SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running Oracle stored procedures from SQL server 2005


Running Oracle stored procedures from SQL server 2005

Author
Message
samiam914
samiam914
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 294
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
samiam914
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 294
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[i].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
Attachments
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search