Running Oracle stored procedures from SQL server 2005

  • samiam914

    SSCommitted

    Points: 1886

    Hello,

    I am new to T-SQL, written only couple of small ones. Does SQL server 2005 provide capability to run Oracle stored procedures. I already have a linked server established for Oracle.

    I have several oracle stored procedures that :

    a) Accept multiple input parameters and return multiple out parameters.

    b) Accept multiple input parameters and return a REF CURSOR as out parameter.

    If you have any sample code, can you please post it here along with any suggestions. I researched, but there seems to be no solution, especially for REF CURSOR. Much appreciate it.

    Thnx

    Sam

  • samiam914

    SSCommitted

    Points: 1886

    I found a way for ref cursors, here is the solution:

    What you will need

    1) Little bit of Oracle DBA assistance may be needed

    2) VS studio would help in creating C# class and deploying

    3) Ability to create Oracle packages

    4) Ability to create SQL Server packages

    5) SQL Server DBA assistance for configuring and approval

    6) Follow steps step1-step5.

    //--step1----------------------------- create Oracle pkg-------------------------------------------

    CREATE or REPLACE PACKAGE m04_get_pgm_info_pkg AS

    TYPE tpk_return_refcur IS REF CURSOR;

    PROCEDURE m04_get_pgm_info (

    pi_subj1 IN VARCHAR2, pi_subj2 IN VARCHAR2, pi_subj3 IN VARCHAR2, pi_subj4 IN VARCHAR2

    ,pi_subj5 IN VARCHAR2, pi_subj6 IN VARCHAR2, pi_subj7 IN VARCHAR2, pi_subj8 IN VARCHAR2

    ,pi_subj9 IN VARCHAR2, pi_subj10 IN VARCHAR2, pi_subj11 IN VARCHAR2, pi_subj12 IN VARCHAR2

    ,po_return_refcur IN OUT tpk_return_refcur

    );

    END m04_get_pgm_info_pkg;

    //--step2----------------------------- create Oracle pkg body--------------------------------------

    //--note this procedure returns 3 rows of a single columned string

    CREATE or REPLACE PACKAGE BODY m04_get_pgm_info_pkg AS

    PROCEDURE m04_get_pgm_info (

    pi_subj1 IN VARCHAR2, pi_subj2 IN VARCHAR2, pi_subj3 IN VARCHAR2, pi_subj4 IN VARCHAR2

    ,pi_subj5 IN VARCHAR2, pi_subj6 IN VARCHAR2, pi_subj7 IN VARCHAR2, pi_subj8 IN VARCHAR2

    ,pi_subj9 IN VARCHAR2, pi_subj10 IN VARCHAR2, pi_subj11 IN VARCHAR2, pi_subj12 IN VARCHAR2

    ,po_return_refcur IN OUT tpk_return_refcur

    ) IS

    .

    .

    .

    .

    OPEN po_return_refcur FOR

    select 'RESULTS'

    from dual

    union all

    select '-----------------------------------------------------------------------------------'

    from dual

    union all

    select 'Ran Successfully; number of rows/records created=' || count(*)

    from myschema.m04_pat_prog;

    END m04_get_pgm_info;

    END m04_get_pgm_info_pkg;

    /

    //--step3-------------------------- create C# Class-------------------------------------------------

    //create a SqlServerProject2.dll using below class via Visual Stuido, by deploying it.

    //change TNSNAME,USERID,PASSWORD to appropriate values in the connection string

    //note: the class name is “StoredProcedures2”, it has a “s”, where as the method does not.

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    using System.Data.OracleClient;

    public partial class StoredProcedures2

    {

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void StoredProcedure2(String subj1

    , String subj2

    , String subj3

    , String subj4

    , String subj5

    , String subj6

    , String subj7

    , String subj8

    , String subj9

    , String subj10

    , String subj11

    , String subj12

    //, SqlString subj12

    )

    {

    // create connection

    OracleConnection conn = new OracleConnection("Data Source=TNSNAME;User Id=USERID;Password=PASSWORD;");

    // create the command for the stored procedure

    OracleCommand cmd = new OracleCommand();

    cmd.Connection = conn;

    cmd.CommandText = "m04_get_pgm_info_pkg.m04_get_pgm_info";

    cmd.CommandType = CommandType.StoredProcedure;

    // add the parameters for the stored procedure including the REF CURSOR

    // to retrieve the result set

    cmd.Parameters.Add("pi_subj1", OracleType.VarChar, 7).Value = subj1;

    //cmd.Parameters[0].Direction = ParameterDirection.Input;

    cmd.Parameters.Add("pi_subj2", OracleType.VarChar, 7).Value = subj2;

    cmd.Parameters.Add("pi_subj3", OracleType.VarChar, 7).Value = subj3;

    cmd.Parameters.Add("pi_subj4", OracleType.VarChar, 7).Value = subj4;

    cmd.Parameters.Add("pi_subj5", OracleType.VarChar, 7).Value = subj5;

    cmd.Parameters.Add("pi_subj6", OracleType.VarChar, 7).Value = subj6;

    cmd.Parameters.Add("pi_subj7", OracleType.VarChar, 7).Value = subj7;

    cmd.Parameters.Add("pi_subj8", OracleType.VarChar, 7).Value = subj8;

    cmd.Parameters.Add("pi_subj9", OracleType.VarChar, 7).Value = subj9;

    cmd.Parameters.Add("pi_subj10", OracleType.VarChar, 7).Value = subj10;

    cmd.Parameters.Add("pi_subj11", OracleType.VarChar, 7).Value = subj11;

    cmd.Parameters.Add("pi_subj12", OracleType.VarChar, 7).Value = subj12;

    cmd.Parameters.Add("po_return_refcur", OracleType.Cursor).Direction =

    ParameterDirection.Output;

    // open the connection and create the DataReader

    conn.Open();

    OracleDataReader dr = cmd.ExecuteReader();

    // Create the record and specify the metadata for the columns.

    SqlDataRecord record = new SqlDataRecord(

    new SqlMetaData("Rownumb", SqlDbType.Int),

    new SqlMetaData("Symb", SqlDbType.NVarChar, 10),

    new SqlMetaData("Msg", SqlDbType.NVarChar, 250));

    // Mark the begining of the result-set.

    SqlContext.Pipe.SendResultsStart(record);

    int x = 0;

    // output the results and close the connection.

    while (dr.Read())

    {

    x++;

    for (int i = 0; i < dr.FieldCount; i++)

    {

    // Set values for each column in the row.

    record.SetInt32(0, x);

    record.SetString(1, ">->");

    record.SetString(2, dr.ToString());

    }

    // Send the row back to the client.

    SqlContext.Pipe.SendResultsRow(record);

    }

    conn.Close();

    // Mark the end of the result-set.

    SqlContext.Pipe.SendResultsEnd();

    }

    };

    //--step4---------- create SQL Server stored procedure and configure ------------------------------

    SELECT name, database_id, is_trustworthy_on FROM sys.databases -- shows trustworty on/off

    alter database yourSQLDB set trustworthy on

    alter authorization on database ::yourSQLDB to sa

    --enable clr integration using Surface Area Configuration tool or do the following then

    --make sure you reconfigure as follows

    --EXEC sp_configure 'clr enabled' , '1'

    --go

    --reconfigure;

    –-drop only if recreating or may be you can use ALTER Assembly instead

    --drop Assembly StoredProcedures2Assembly;

    create Assembly StoredProcedures2Assembly

    From '\\mydirectory\SqlServerProject2\SqlServerProject2\obj\Debug\SqlServerProject2.dll'

    with permission_set=unsafe

    drop proc StoredProcedure2Proc

    create proc StoredProcedure2Proc

    @subj1 nvarchar(7)='',@subj2 nvarchar(7)='',@subj3 nvarchar(7)='',@subj4 nvarchar(7)='',@subj5 nvarchar(7)=''

    ,@subj6 nvarchar(7)='',@subj7 nvarchar(7)='',@subj8 nvarchar(7)='',@subj9 nvarchar(7)='',@subj10 nvarchar(7)=''

    ,@subj11 nvarchar(7)='',@subj12 nvarchar(7)=''

    AS EXTERNAL NAME

    StoredProcedures2Assembly.StoredProcedures2.StoredProcedure2

    //--step5--execute SQL Server stored procedure (which gets data from oracle stored

    //--procedure that returns a result set via ref cursor

    exec dbo.StoredProcedure2Proc

    @subj1='EDU',@subj2='EDAD',@subj3='MAE',@subj4='ENE',@subj5='SSE'

    ,@subj6='SCE',@subj7='FLE',@subj8='MPE',@subj9=' ',@subj10=' '

    ,@subj11=' ',@subj12=' '

    Results from the above procedure when run in SQL Server management Studio looks like this (note-there are 3 columns here a) Rownumb b) Symb c) Msg. Due to cut & paste into this forum, lost positionality. pl/sql stored procedure returned what is shown in column “Msg”) :

    Rownumb Symb Msg

    1 >-> RESULTS

    2 >-> -----------------------------------------------------------------------------------

    3 >-> Ran Successfully; number of rows/records created=13898

    Disclaimer: No guarantees or warranties, free to use the above method at your own risk.

    A word document is attached to this post, which contains same as above.

    Sam

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply