# encoding: ascii
# api: powershell
# title: Test Ora Proc Wrapper 1
# description: Powershell wrapper to an Oracle stored procedure returning one Ref cursor. Here it determines the version of the Oracle Server. You can substitute similar procedures.
# version: 0.1
# type: function
# license: CC0
# x-poshcode-id: 1062
# x-archived: 2009-04-29T02:34:26
#
#
# this is a testcase for http://code.google.com/p/poshcodegen/
# a project for generating PowerShell function wrappers around stored procedure call, and more.
# Here we show how to return a resultsets (aka ref cursors) from an Orcale stored procedures using ADO.NET with PowerShell
# by the way it tell the verion of the orcale Products you are running
# Bernd Kriszio http://pauerschell.blogspot.com/
# Set your connection string (somthing like the following line) outside this function
# $con_ora = "Data Source=your-tns;User Id=scott;Password=tiger;Integrated Security=no"
[System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
function noparm_recordset ()
{
#Set up .NET code for calling stored proc
$connection = New-Object System.Data.OracleClient.OracleConnection($con_ora)
$command = New-Object System.Data.OracleClient.OracleCommand('noparm_recordset', $connection)
$command.CommandType = [System.Data.CommandType]::StoredProcedure
#Set up Stored Proc parameters
#$command.Parameters.Add("ReturnValue", [Data.SqlDBType]::int) | out-null
#$command.Parameters["ReturnValue"].Direction = [System.Data.ParameterDirection]::ReturnValue
#$command.Parameters.Add("RC_1", [System.Data.OracleClient.OracleType]::Cursor) | out-null
$command.Parameters.Add("RC_1", [Data.OracleClient.OracleType]::Cursor) | out-null
$command.Parameters["RC_1"].Direction = [System.Data.ParameterDirection]::Output
#Run
$connection.Open() | out-null
$adapter = New-Object System.Data.OracleClient.OracleDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
#$command.ExecuteNonQuery()
$connection.Close() | out-null
$CommandOutput = New-Object PSObject
$CommandOutput | Add-Member -Name Tables -Value $dataSet.Tables -MemberType NoteProperty
#Set output parameters
#$CommandOutput | Add-Member -Name ReturnValue -Value $command.Parameters["ReturnValue"].Value -MemberType NoteProperty
#Close the connection and return the output object
return $CommandOutput
}
(noparm_recordset).tables[0]
<#
-- code of the testprocedure
create or replace procedure noparm_recordset (
rc_1 in out SYS_REFCURSOR
) as
begin
Open rc_1 for
SELECT * FROM product_component_version ;
end;
/
-- Test call using SQL*Plus
var r refcursor
exec noparm_recordset(:r)
print r
#>