Database Reference
In-Depth Information
12. Close the connection when inished:
PS > $conn.Close()
How it works...
The
Oracle.Management.Omo
assembly is located under the
ORACLE_BASE\ORACLE_
HOME\odt
folder and installed into the GAC (that is,
%WINDIR%\Microsoft.NET\
assembly\GAC_32\Oracle.Management.Omo
). After loading the assembly, we explore
the types in the assembly with
$types = $asm.GetTypes()
. Spotting a
Connection
object in the results, we see what properties are available on it with
($types | ? {$_.
Name -eq "Connection"}).GetProperties()
. With the properties known, we create
an
Omo.Connection
object and pass it connection details. We then inspect methods
available to us on the connection object using
Get-Member
(
gm
) using
$conn | gm | ?
{$_.MemberType -eq "Method"}
. We notice both
Open
and
Initialize
methods on
the connection so we invoke both in that order. If we didn't call
Initialize
we'd get an error
later when we tried to drill down into further use of the connection. Most of the objects in this
assembly work this way; you can get back basic details without having to initialize but to drill
into child objects you have to initialize the parent.
Next we get back a rich collection of table objects with
$tables = $conn.
GetTables($false, $true)
, passing
$false
for the refresh parameter and
$true
to
fetch dependencies. From there we iterate through the tables and print out table names. Next
we reference the
DEPARTMENTS
table with
$deptTable = $tables["DEPARTMENTS"]
and call
Initialize()
on it so we can access column data. We pipe the columns to
Format-Table
(
ft
) and use an expression to concatenate sub object properties into
the results:
@{Label="Type"; Expression={"{0} ({1})" -f $_.DataType.
OracleType, $_.DataType.Size}}
.
Next we generate a CREATE table script with
$deptTable.GetCreateSQLs($true)[0]
;
$true
indicates the schema name should be appended and we access item 0 since the
method returns an
ArrayList
. Finally, we invoke the
GetData
method on the table (passing
$true
to append the schema name in the query) to get a
DataSet
created with the table
data and then we close the connection.
There's more...
This is only a small fraction of
Oracle.Management.Omo
usage. We can query, alter, and
otherwise interact with nearly all Oracle types. If you can do it through Visual Studio using
Server Explorer and related integration points, you can probably do it through PowerShell.
While we could do much of this ourselves using
Oracle.DataAccess
and interacting with
Oracle system objects, that would involve a lot of raw plumbing already done for us inside this
ODT assembly. To explore this assembly more easily, create a project in Visual Studio that
references the
Oracle.Management.Omo assembly
, or use a .NET disassembler tool
such as JetBrain's dotPeek to explore all the objects and their members.
Search WWH ::
Custom Search