« 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 (16)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: Erik Pitoniak
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Response: Dr. Rashmi Patel
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Response: Erik Pitoniak
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Response: promedia.ba
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Response: sims 4 keygen
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Response: sims 4 keygen
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Access Oracle Database with Windows Powershell 2.0

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>