« Extract Database Object Header Comment with Powershell | Main | Upgrading from Oracle Database 10g to 11g: What to expect from ... »
Monday
Jan182010

Access Oracle Database with Windows Powershell 2.0

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

 

References (1)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: custom essays
    The access of the oracle platform as you write her with the use of the windows PowerShell this is very good job form you that you write about that here with the details. Those who are having the work with that can get help.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>