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”) :
RownumbSymbMsg
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy