Database Reference
In-Depth Information
Above the
Connect
function, comment-based help is used to support
Get-Help
calls, which is
always a good idea for shared modules. In this function and others, the
[CmdletBinding()]
attribute is used to enable use of
Write-Verbose
and
Write-Debug
,
-WhatIf
, and other
common parameters support such as
-ErrorVariable
. The
Connect
function was changed
to store the connection object in a script level variable (unless
-PassThru
is present) so it is
available for other functions needing a connection later, without needing to pass it each time.
Functions like
Disconnect
use the connection passed in if set; otherwise, they use the script
level connection object.
The
Invoke
function sets the
SupportsShouldProcess
property of the
CmdletBinding
attribute to
$true
to enable
-WhatIf
support so we can test a non-query SQL method to see
how many rows would be affected if committed. The
ShouldProcess
method is called on
the built-in
$psCmdlet
object and is given the connection's data source as the target name
for output. When this method returns
$false
, it means
-WhatIf
was not supplied and the
transaction will get committed. Otherwise, the transaction is rolled back and the number of
affected rows is output.
After the functions,
Export-ModuleMember
lists the items we want publicly exposed.
Since functions such as
Load
and
Get-Connection
were not included, they can only be
used inside the module as helpers.
Export-ModuleMember
can be passed multiple items,
called multiple times, and used for other items like aliases and variables.
There's more...
This recipe just scratches the surface of what can be done with PowerShell modules,
advanced functions, and Oracle. We could port and refactor more prior Oracle functions to
cmdlet functions, deine aliases, support pipeline input, accept script blocks as parameters,
tackle other Oracle operations, and much more.
Automating SQL*Plus (Advanced)
In this recipe we'll look at using SQL*Plus (included with ODP.NET) from PowerShell to execute
larger blocks of SQL, such as combined DDL scripts to update multiple procedures and views.
ODP.NET is great for basic select, insert, update, and delete statements, executing procedures
and the like but it breaks down for executing larger, complex batches of scripts such as
making many object changes for an application deployment. Issues with linefeeds, BEGIN/
END blocks, and multiple objects in one ile can occur with ODP.NET but are well-handled in
SQL*Plus.
Getting ready
Ensure you have a TNS name entry set up for Oracle Express on localhost. See the
Connecting
and disconnecting (Simple)
recipe for more details.
Search WWH ::
Custom Search