Access Oracle Database with Windows Powershell 2.0
Monday, January 18, 2010 at 1:52PM This year I plan to implement database build automation. There are lot of manual tasks for a developer DBA to create and deploy a release package.
I decided to use the Windows Powershell as build tool. it integrates best in the Windows environment to access ...
- File System - read/write parse and concatenate files
- Registry/Environment - access them like a fiele system
- Database - Add ODP.NET Assembly to access the database
- Source control like Team Foundation Server
In my first example i want to show how easy database access is achieved very well controlled due to exception handling via try {} catch {} and finally {} blocks.
This is not a Windows Powershell introduction but an example how you could use it for your own tasks;
The following example connects to the database to extract the tables DDL File.
Use the Powershell console (powershell.exe) to execute the commands
- The ODP.NET Dll ist loaded (specify your oracle home) and a global variable as connect string is defined
[Reflection.Assembly]::LoadFile("C:\Program Files\Oracle\ora102\client_1\BIN\Oracle.DataAccess.dll")
$DBBLD_DEV_DB_CONNECT = "User Id=oasis; Password=oaw123; Data Source=DEV_OAS"
A function definition follows something you now from Unix-Shell
- The SQL is constructed
- A database connection object is defined and the connection is opened
- The SQL is parsed and executed
- now data can be retrieved and just written to STDOUT
- after all records are read the connection is closed
the DDL generation is done on database and retrieved via a pipelined function;
function DbBld_Dump-Table_Ddl
## Dump tables DDL - connects always to OASIS DEV-Database
( [parameter(Mandatory=$true)] [String] $p_table_owner
, [parameter(Mandatory=$true)] [String] $p_table_Name ) {
DbBld_Log-Message "Dump Table ${p_table_owner}.${p_table_Name} ...`n" "`n"
$l_sql="SELECT * FROM TABLE ( Util_Dbbld.Dump_Table_Ddl('${p_table_owner}', '${p_table_Name}') )";
$l_sql
## connect to database
$l_Conn = New-Object Oracle.DataAccess.Client.OracleConnection( $DBBLD_DEV_DB_CONNECT )
$l_Conn.Open();
try {
## prepare and execute sql
$l_command = New-Object Oracle.DataAccess.Client.OracleCommand( $l_sql, $l_Conn )
$l_reader = $l_command.ExecuteReader()
## get the data
while ( $l_reader.Read() ) {
$l_line = $l_reader.GetString(0)
"$l_line"
}
}
catch {
DbBld_Log-Error "Executing [ $l_sql ] `n"
throw
}
finally {
$l_Conn.Close();
}
}
A test using SQL*PLUS calling the pipelined function to describe the CATEGORIES table in the NORTHWIND schema:
SQL> SELECT * FROM TABLE ( Util_Dbbld.Dump_Table_Ddl( 'NORTHWIND', 'CATEGORIES' ) );
COLUMN_VALUE
--------------------------------------------------------------------------------
--
-- DDL:NORTHWIND.CATEGORIES:TABLE.NEW - Table DDL Dump
--
CREATE TABLE CATEGORIES
( CATEGORYID INTEGER NOT NULL
, CATEGORYNAME VARCHAR2(15) NOT NULL
, DESCRIPTION VARCHAR2(2000) NULL
, PICTURE BLOB NULL )
TABLESPACE USERS
;
-- PK:PK_CATEGORIES ...
COLUMN_VALUE
--------------------------------------------------------------------------------
ALTER TABLE CATEGORIES
ADD CONSTRAINT PK_CATEGORIES
PRIMARY KEY ( CATEGORYID )
USING INDEX TABLESPACE USERS
;
16 Zeilen ausgewõhlt.
SQL>
Now describing the table calling the Powershell function DbBld_Dump-Table_Ddl :
PS D:\Tfs\OASIS\MAIN> DbBld_Dump-Table_Ddl NORTHWIND CATEGORIES
DbBld.Msg : Dump Table NORTHWIND.CATEGORIES ...
DbBld.Msg : SELECT * FROM TABLE ( Util_Dbbld.Dump_Table_Ddl( 'NORTHWIND', 'CATEGORIES' ) )
--
-- DDL:NORTHWIND.CATEGORIES:TABLE.NEW - Table DDL Dump
--
CREATE TABLE CATEGORIES
( CATEGORYID INTEGER NOT NULL
, CATEGORYNAME VARCHAR2(15) NOT NULL
, DESCRIPTION VARCHAR2(2000) NULL
, PICTURE BLOB NULL )
TABLESPACE USERS
;
-- PK:PK_CATEGORIES ...
ALTER TABLE CATEGORIES
ADD CONSTRAINT PK_CATEGORIES
PRIMARY KEY ( CATEGORYID )
USING INDEX TABLESPACE USERS
;PS D:\Tfs\OASIS\MAIN>
the advantages using the Powershell function :
- sophisticated error handling if database related work is failing
- powershell functions could be enriched with a lot of additional logic
- easy to integrate in build scripts or even as call-out in SQL-scripts
- retrieved data could be processed with all capabilities of the Powershell
- clean output of data without header, prompt, ...
This is just an easy demonstration how to connect to the Oracle database via Powershell;
One of the next blogs will be the use of the Powershell as a preprocessor;
for example using an include directive in SQL scripts similar that way the the preprocessor of C is doing it;
/Karl Reitschuster
Karl Reitschuster
Some additional hints to the code example :
- Checking connection state : If the opening of a connection fails an error-message with error-stack is written on the screen, but no exception is thrown. You have to check the connection.status object to check if your attempt to connect to the database was successfully.
## set constant for Connection state ...
Set-Variable -Name DB_CONNECTION_STATE_OPEN -Value ([System.Data.ConnectionState]::Open) -Option constant -Scope Script
## in the function ...
## Connect to Database
try {
$L_Conn = New-Object Oracle.DataAccess.Client.OracleConnection( $DBBLD_DEV_DB_CONNECT )
$L_Conn.Open();
if ( $L_Conn.State -ne $DB_CONNECTION_STATE_OPEN ) {
throw "Open Connection failed"
}
}
catch {
Log-Error "Opening Connection"
throw
}
- Dealing with NULLs : You may not get NULL values from the reader which causes an exception. Instead check first with IsDBNULL() if the value to retrieve is NULL.
while ( $l_Reader.Read() ) {
if ( $l_Reader.IsDBNull(0) ) {
$l_Line = ""
} else {
$l_Line = $l_Reader.GetString(0)
}
"$l_line"
}
so far
/Karl Reitschuster
Reader Comments (3)
Just shows that writing by means of knowledge brings so much depth and relevance to ones readers. Thank you for sharing.
Just shows that writing by means of knowledge brings so much depth and relevance to ones readers. Thank you for sharing.
Just shows that writing by means of knowledge brings so much depth and relevance to ones readers. Thank you for sharing.