May 2, 2008 at 5:34 pm
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
July 12, 2010 at 4:29 pm
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