April 15, 2008 at 5:14 am
Hi, I have a stored procedure located on SQL2005 that call by "openquery" stored procedure from Oracle. After executing this I`ve got an error message from SQL side such:An error occurred while submitting the query text to OLE DB provider "OraOLEDB.Oracle" for linked server "ODBDCP". Whats wrong? Please help. I did everything like in http://www.objectsharp.com/cs/blogs/matt/archive/2005/06/13/2221.aspx
/****** Object: StoredProcedure [dbo].[CallOracleProcTest] Script Date: 04/15/2008 13:23:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE Proc [dbo].[CallOracleProcTest]
As
declare @createdTran tinyint
Set @createdTran = 0
-- Note : This must be set in order for Transactions to be Started and Rollbacked properly
SET XACT_ABORT ON
-- Starting a Transaction, Note: this is optional.
-- This technique, just describes how to test to see
-- if a transaction has already been started, by the caller
-- If not, then start the transaction.
If @@trancount = 0
Begin
Begin Transaction
Set @createdTran = 1
End
-- Use the below temp table, to capture the results returned back from the call to the Oracle Proc.
Create table #test (indicator int identity(1,1), Oracle_Result varchar(500))
Declare @parma1ForOracle varchar(100), @parma2ForOracle varchar(100),
@parma3ForOracle varchar(100), @parma4ForOracle varchar(100)
set @parma1ForOracle = '1'
set @parma2ForOracle = '100.2'
set @parma3ForOracle = 'This is info for Oracle test'
set @parma4ForOracle = 'Dec 12 2004'
/* NOTE: Below would build the Dynamic Sql Statement. Parameters passed to Oracle are built into the Dynamic Sql*/
/* Results from Call are placed into a Temp table. NOTE: That the position of the passed back info, would have
to be know by this proc*/
/* Note that resultset 25 , signifies that <= 25 rows can be returned back from the Oracle Stored proc*/
Declare @OracleCall varchar(8000)
set @OracleCall = 'Insert into #test(Oracle_Result) SELECT * FROM OPENQUERY(ODBDCP , ''{CALL ODBDCP.MBRRAPI.CallFromSqlServerPackage.OracleProc('
set @OracleCall = @OracleCall + ''''''+ @parma1ForOracle + ''''''
set @OracleCall = @OracleCall + ',' + '''''' + @parma2ForOracle + ''''''
set @OracleCall = @OracleCall + ',' + '''''' + @parma3ForOracle + ''''''
set @OracleCall = @OracleCall + ',' + '''''' + @parma4ForOracle + ''''''
set @OracleCall = @OracleCall + ',{resultset 25, ReturnVal})}'')'
-- Below is where actually call the Oracle stored procedure.
exec (@OracleCall)
if @@error <> 0 goto err_handler
-- Now get the info passed back from the oracle stored procedure.
-- Note: Must know the row number of the data passed back from the oracle stored procedure,
-- and its type.
Declare @parma1FromOracle varchar(100), @parma2FromOracle integer,
@parma3FromOracle real, @parma4FromOracle datetime
Select @parma1FromOracle = Oracle_Result From #test Where indicator = 1
if @@error <> 0 goto err_handler
Select @parma2FromOracle = Convert(int,Oracle_Result) From #test Where indicator = 2
if @@error <> 0 goto err_handler
Select @parma3FromOracle = Convert(real,Oracle_Result) From #test Where indicator = 3
if @@error <> 0 goto err_handler
Select @parma4FromOracle = Convert(datetime,Oracle_Result) From #test Where indicator = 4
if @@error <> 0 goto err_handler
print 'Result of @parma1FromOracle = ' + ltrim(rtrim(@parma1FromOracle))
print 'Result of @parma2FromOracle = ' + ltrim(rtrim(str(@parma2FromOracle)))
print 'Result of @parma3FromOracle = ' + ltrim(rtrim(Convert(varchar(30),@parma3FromOracle)))
print 'Result of @parma4FromOracle = ' + ltrim(rtrim(Convert(varchar(30),@parma4FromOracle)))
if @createdTran = 1 and @@trancount > 0
Begin
Commit Transaction
End
SET XACT_ABORT OFF
return 0
err_handler:
print 'Error in proc'
if @createdTran = 1 and @@trancount > 0
Begin
Rollback Transaction
End
SET XACT_ABORT OFF
Return - 1
April 15, 2008 at 2:10 pm
double check if you have a linked server named ODBDCP.
April 16, 2008 at 12:00 am
There is no problem with linked server! But the error with submitting query is still present. Any ideas?
May 21, 2008 at 1:15 pm
While I am trying to figure this out, do you have any oracle stored procedures that returns a REF cursor and you tried executing it in sqlserver. If you were successful can you please post the solution, including the oracle package.
Thnx
Sam
October 7, 2008 at 2:15 am
Perhaps the `CALL ODBDCP.MBRRAPI.` should omit the linked server name ODBDCP?
Following this you may receive a further error message such as:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "{CALL .... }".
The OLE DB provider "OraOLEDB.Oracle" for linked server "ODBDCP" indicates that either the object has no columns or the current user does not have permissions on that object.
This is where I am currently at.
Regards,
Chris
October 8, 2008 at 3:18 pm
I have figured out how to execute from a SQL server Stroed procedure an Oracle stored procedure that returns a REF cursor. You have to write CLR procedure and create assemblies, it works great.
I hopt to get all the info together and put the complete solution here or publish as an article, as it is little bit involved. In case some one has a urgent need before I can publish the solution, please post reponse here, I will get an email and I will respond.
October 20, 2008 at 11:07 pm
Hi all
I am trying to execute the Oracle package and Sql query within same dataset in SSRS 2005.
Oracle Package sets Environment variables for the sql query to exucute the query
Oracle package and Sql query as below
call eec_sys.eec_pck_logins.login('SDB','ASC','PP')
select id, m_pck_std_custom.id_desc(id, 1) from M_id
How can i execute the Oracle Package and sql query at once in same dataset of SSRS
Thanks in advnace
February 13, 2009 at 7:58 am
I am trying to use an existing Oracle Package (oracle 9i) to use with SSRS (sql server 2005). The report I am creating in SSRS will use a set of parameters to pass to the Oracle Package. I am able to connect to Oracle, but I am unable to pass the parameter correctly to the oracle package. I have researched, and have come upon your post. I believe my problem is that I do not understand how to add the ref cursor to the existing package.
Below is the existing Oracle package:
AS
PROCEDURE MAIN(P_PROCESS_ID S_MLBOM.PROCESS_ID%TYPE,
P_CCN CCN.CCN%TYPE,
P_ITEM BOM.ITEM%TYPE,
P_REVISION BOM.REVISION%TYPE,
P_BCR_TYPE BOM.BCR_TYPE%TYPE,
P_BCR_ALT BOM.BCR_ALT%TYPE,
P_ASSEM_QTY VARCHAR2,
P_INCL_COST VARCHAR2,
P_INCL_ALT VARCHAR2,
P_EFFDATE DATE,
P_SUMMARY VARCHAR2,
P_SORT VARCHAR2);
PROCEDURE BOM_PROC(BM_COMP_ITEM BOM.COMP_ITEM%TYPE,BM_COMP_REV BOM.COMP_REV%TYPE,
BM_ALT_COMP BOM.ALT_COMP%TYPE,BM_COMP_QTY BOM.COMP_QTY%TYPE,
BM_EFFBEG BOM.EFFBEG%TYPE,BM_EFFEND BOM.EFFEND%TYPE);
END U_MLBOM;
----------------------
AS
S_CURRENT NUMBER;
S_TAIL NUMBER;
S_NEXT_LEVEL NUMBER;
S_PARENT_MANUAL CHAR;
S_COMP_SEQ NUMBER;
S_KEY_1 S_MLBOM.SML_BOM_KEY%TYPE;
S_START NUMBER;
S_END NUMBER;
TEMP_COST COST_ITM.ITEM_COST21%TYPE;
TEMP_VA_COST CST_IDET.VA_COST_AMT21%TYPE;
TEMP_MAKE ITEM_CCN.MAKE%TYPE;
TEMP_MANUAL COST_ITM.COST_MANUAL%TYPE;
PART1 S_MLBOM.SML_BOM_KEY%TYPE;
PART2 S_MLBOM.SML_BOM_KEY%TYPE;
PART3 S_MLBOM.SML_BOM_KEY%TYPE;
S_EFFDATE DATE;
S_ASSEM_QTY NUMBER;
S_CUR S_MLT%ROWTYPE;
MY_PROCESS_ID S_MLT.PROCESS_ID%TYPE;
PROCEDURE MAIN(P_PROCESS_ID S_MLBOM.PROCESS_ID%TYPE,
P_CCN CCN.CCN%TYPE,
P_ITEM BOM.ITEM%TYPE,
P_REVISION BOM.REVISION%TYPE,
P_BCR_TYPE BOM.BCR_TYPE%TYPE,
P_BCR_ALT BOM.BCR_ALT%TYPE,
P_ASSEM_QTY VARCHAR2,
P_INCL_COST VARCHAR2,
P_INCL_ALT VARCHAR2,
P_EFFDATE DATE,
P_SUMMARY VARCHAR2,
P_SORT VARCHAR2)
IS
CURSOR BOM_CUR(B_CCN CCN.CCN%TYPE,B_BCR_TYPE BOM.BCR_TYPE%TYPE,B_ITEM BOM.ITEM%TYPE,
B_REVISION BOM.REVISION%TYPE,B_BCR_ALT BOM.BCR_ALT%TYPE,B_EFFDATE DATE,B_INCL_ALT VARCHAR2)
IS
SELECT COMP_ITEM,COMP_REV,ALT_COMP,COMP_QTY,EFFBEG,EFFEND FROM BOM
WHERE
(CCN = B_CCN) AND
(BCR_TYPE = B_BCR_TYPE) AND
(ITEM = B_ITEM) AND
(REVISION = B_REVISION) AND
(BCR_ALT = B_BCR_ALT) AND
(B_EFFDATE IS NULL OR ((EFFBEG IS NULL OR EFFBEG = B_EFFDATE))) AND
(B_INCL_ALT = 'Y' OR ALT_COMP = ' ')
ORDER BY BOM_USEQ;
CURSOR BOM_CUR2(B_CCN CCN.CCN%TYPE,B_BCR_TYPE BOM.BCR_TYPE%TYPE,B_ITEM BOM.ITEM%TYPE,
B_REVISION BOM.REVISION%TYPE,B_BCR_ALT BOM.BCR_ALT%TYPE,B_EFFDATE DATE,B_INCL_ALT VARCHAR2)
IS
SELECT COMP_ITEM,COMP_REV,ALT_COMP,COMP_QTY,EFFBEG,EFFEND FROM BOM
WHERE
(CCN = B_CCN) AND
(BCR_TYPE = B_BCR_TYPE) AND
(ITEM = B_ITEM) AND
(REVISION = B_REVISION) AND
(BCR_ALT = B_BCR_ALT) AND
(B_EFFDATE IS NULL OR ((EFFBEG IS NULL OR EFFBEG = B_EFFDATE))) AND
(B_INCL_ALT = 'Y' OR ALT_COMP = ' ')
ORDER BY COMP_ITEM,COMP_REV;
CURSOR BOM_CUR3(B_CCN CCN.CCN%TYPE,B_BCR_TYPE BOM.BCR_TYPE%TYPE,B_ITEM BOM.ITEM%TYPE,
B_REVISION BOM.REVISION%TYPE,B_BCR_ALT BOM.BCR_ALT%TYPE,B_EFFDATE DATE,B_INCL_ALT VARCHAR2)
IS
SELECT COMP_ITEM,COMP_REV,ALT_COMP,COMP_QTY,EFFBEG,EFFEND FROM BOM
WHERE
(CCN = B_CCN) AND
(BCR_TYPE = B_BCR_TYPE) AND
(ITEM = B_ITEM) AND
(REVISION = B_REVISION) AND
(BCR_ALT = B_BCR_ALT) AND
(B_EFFDATE IS NULL OR ((EFFBEG IS NULL OR EFFBEG = B_EFFDATE))) AND
(B_INCL_ALT = 'Y' OR ALT_COMP = ' ')
ORDER BY BALLOON;
BEGIN
MY_PROCESS_ID := P_PROCESS_ID;
DELETE S_MLT WHERE PROCESS_ID = MY_PROCESS_ID;
COMMIT;
IF P_EFFDATE = TO_DATE('01011800','MMDDYYYY') THEN
S_EFFDATE := NULL;
ELSE
S_EFFDATE := P_EFFDATE;
END IF;
S_ASSEM_QTY := TO_NUMBER(P_ASSEM_QTY);
INSERT INTO S_MLT(PROCESS_ID,S_MLT_SEQ,KEY,ITEM,REVISION,ALT,BCR_ALT,LVL_,QTY,ASSEM,MANUAL,COST,MAKE,EFFBEG,EFFEND,NEXT)
VALUES(P_PROCESS_ID,'0000001',' ',P_ITEM,P_REVISION,P_BCR_ALT,' ',0,S_ASSEM_QTY,' ',' ',0,' ',NULL,NULL,' ');
S_CURRENT := 0;
S_TAIL := 1;
WHILE S_TAIL > S_CURRENT LOOP
S_CURRENT := S_CURRENT + 1;
SELECT * INTO S_CUR FROM S_MLT WHERE (PROCESS_ID = MY_PROCESS_ID) AND
(S_MLT_SEQ = LPAD(TO_CHAR(S_CURRENT),7,0));
IF S_CUR.LVL_ > 32 THEN EXIT; END IF;
IF S_CUR.ALT != ' ' AND S_CUR.LVL_ != 0 THEN GOTO NEXT; END IF;
S_NEXT_LEVEL := S_CUR.LVL_ + 1;
S_PARENT_MANUAL := S_CUR.MANUAL;
S_COMP_SEQ := 0;
S_KEY_1 := S_CUR.KEY;
S_START := S_CUR.LVL_ * 3 + 1;
S_END := S_START + 2;
TEMP_COST := 0;
TEMP_VA_COST := 0;
TEMP_MAKE := ' ';
TEMP_MANUAL := ' ';
IF P_INCL_COST != 'Y' OR S_PARENT_MANUAL = 'Y' THEN NULL;
ELSE
BEGIN
SELECT ITEM_COST21,COST_MANUAL INTO TEMP_COST,TEMP_MANUAL FROM COST_ITM
WHERE CCN = P_CCN AND BCR_TYPE = P_BCR_TYPE AND
ITEM = S_CUR.ITEM AND REVISION = S_CUR.REVISION AND
BCR_ALT = S_CUR.ALT;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN
SELECT VA_COST_AMT21 INTO TEMP_VA_COST FROM CST_IDET
WHERE CCN = P_CCN AND BCR_TYPE = P_BCR_TYPE AND
ITEM = S_CUR.ITEM AND REVISION = S_CUR.REVISION AND
BCR_ALT = S_CUR.ALT;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN
SELECT MAKE INTO TEMP_MAKE FROM ITEM_CCN
WHERE CCN = P_CCN AND ITEM = S_CUR.ITEM AND REVISION = S_CUR.REVISION;
IF TEMP_MANUAL = 'Y' OR TEMP_MAKE != 'Y' THEN
S_PARENT_MANUAL := 'Y';
UPDATE S_MLT SET COST = TEMP_COST * S_CUR.QTY WHERE
(PROCESS_ID = MY_PROCESS_ID) AND
S_MLT_SEQ = S_CUR.S_MLT_SEQ;
ELSE
UPDATE S_MLT SET COST = TEMP_VA_COST * S_CUR.QTY WHERE (PROCESS_ID = MY_PROCESS_ID) AND
S_MLT_SEQ = S_CUR.S_MLT_SEQ;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END IF;
IF P_SORT = 'S' THEN
FOR BM IN BOM_CUR(P_CCN,P_BCR_TYPE,S_CUR.ITEM,S_CUR.REVISION,
S_CUR.ALT,S_EFFDATE,P_INCL_ALT) LOOP
BOM_PROC(BM.COMP_ITEM,BM.COMP_REV,BM.ALT_COMP,BM.COMP_QTY,BM.EFFBEG,BM.EFFEND);
END LOOP;
END IF;
IF P_SORT = 'C' THEN
FOR BM IN BOM_CUR2(P_CCN,P_BCR_TYPE,S_CUR.ITEM,S_CUR.REVISION,
S_CUR.ALT,S_EFFDATE,P_INCL_ALT) LOOP
BOM_PROC(BM.COMP_ITEM,BM.COMP_REV,BM.ALT_COMP,BM.COMP_QTY,BM.EFFBEG,BM.EFFEND);
END LOOP;
END IF;
IF P_SORT = 'B' THEN
FOR BM IN BOM_CUR3(P_CCN,P_BCR_TYPE,S_CUR.ITEM,S_CUR.REVISION,
S_CUR.ALT,S_EFFDATE,P_INCL_ALT) LOOP
BOM_PROC(BM.COMP_ITEM,BM.COMP_REV,BM.ALT_COMP,BM.COMP_QTY,BM.EFFBEG,BM.EFFEND);
END LOOP;
END IF;
NULL;
COMMIT;
END LOOP;
DELETE S_MLBOM WHERE PROCESS_ID = P_PROCESS_ID;
COMMIT;
INSERT INTO S_MLBOM (PROCESS_ID,SML_BOM_KEY,ITEM,REVISION,ALT,BCR_ALT,LVL_,QTY,ASSEM,
PARENT_COST_MANUAL,COST,ITEM_CCN_MAKE,EFFBEG,EFFEND)
SELECT MY_PROCESS_ID,KEY,ITEM,REVISION,ALT,BCR_ALT,LVL_,QTY,ASSEM,MANUAL,COST,MAKE,EFFBEG,EFFEND
FROM S_MLT WHERE PROCESS_ID = MY_PROCESS_ID;
COMMIT;
IF P_SUMMARY = 'Y' THEN
INSERT INTO S_SUMQAV (PROCESS_ID,ITEM,REVISION,TOTAL_REQD_QTY)
SELECT PROCESS_ID,ITEM,REVISION,SUM(QTY)
FROM S_MLT
WHERE PROCESS_ID = MY_PROCESS_ID
GROUP BY PROCESS_ID,ITEM,REVISION;
END IF;
COMMIT;
DELETE S_MLT WHERE PROCESS_ID = MY_PROCESS_ID;
COMMIT;
END MAIN;
PROCEDURE BOM_PROC(BM_COMP_ITEM BOM.COMP_ITEM%TYPE,BM_COMP_REV BOM.COMP_REV%TYPE,
BM_ALT_COMP BOM.ALT_COMP%TYPE,BM_COMP_QTY BOM.COMP_QTY%TYPE,
BM_EFFBEG BOM.EFFBEG%TYPE,BM_EFFEND BOM.EFFEND%TYPE)
IS
S_TEMP_QTY S_MLT.QTY%TYPE;
BEGIN
S_COMP_SEQ := S_COMP_SEQ + 1;
S_TAIL := S_TAIL + 1;
IF S_START = 1 THEN
PART1 := NULL;
ELSE
PART1 := SUBSTR(S_KEY_1,1,S_START - 1);
END IF;
PART2 := LPAD(S_COMP_SEQ,3,'0');
PART3 := SUBSTR(S_KEY_1,S_END + 1);
S_KEY_1 := PART1 || PART2 || PART3;
IF S_ASSEM_QTY != 0 THEN
S_TEMP_QTY := S_CUR.QTY * BM_COMP_QTY;
ELSE
S_TEMP_QTY := BM_COMP_QTY;
END IF;
INSERT INTO S_MLT(PROCESS_ID,S_MLT_SEQ,KEY,ITEM,REVISION,ALT,BCR_ALT,LVL_,QTY,ASSEM,MANUAL,COST,
MAKE,EFFBEG,EFFEND,NEXT)
VALUES(MY_PROCESS_ID,LPAD(TO_CHAR(S_TAIL),7,0),S_KEY_1,BM_COMP_ITEM,BM_COMP_REV,BM_ALT_COMP,
' ',S_NEXT_LEVEL,S_TEMP_QTY,' ',S_PARENT_MANUAL,0,' ',BM_EFFBEG,BM_EFFEND,' ');
END BOM_PROC;
END U_MLBOM;
------------------------
I know this is for SQL Server discussions, but this does involve SQL server and I have checked on Oracle and have not found a resolution.
Thanks for any input that may be of help.
February 17, 2009 at 2:00 pm
I would like to check with you on how you resoved this issue.
February 18, 2009 at 5:46 pm
Amanda,
Here is an example (there may be better ones out on the web, do a search on "Ref Cursor"), I am going to show the essentials. copy the code into a note pad for better view ( '--' denotes line comment, I have some commented code in here).
I create a package first:
CREATE or REPLACE PACKAGE m04_get_class_list_pkg AS
TYPE tpk_return_refcur IS REF CURSOR;
PROCEDURE m04_get_class_list (pi_yr_sess IN VARCHAR2
,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
-- ,pi_subj13 IN VARCHAR2, pi_subj14 IN VARCHAR2, pi_subj15 IN VARCHAR2
,po_return_refcur IN OUT tpk_return_refcur
-- ,po_return_cd OUT VARCHAR2
);
END m04_get_class_list_pkg;
/
Then I create package body:
CREATE or REPLACE PACKAGE BODY m04_get_class_list_pkg AS
PROCEDURE m04_get_class_list (pi_yr_sess IN VARCHAR2
,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
-- ,pi_subj13 IN VARCHAR2, pi_subj14 IN VARCHAR2, pi_subj15 IN VARCHAR2
,po_return_refcur IN OUT tpk_return_refcur
-- ,po_return_cd OUT VARCHAR2
) IS
.
.
Do your processing
.
.
OPEN po_return_refcur FOR
select 'RESULTS'
from dual
union all
select ' '
from dual
union all
select 'ERROR - ERROR - Did not Run Successfully. CONTACT DOIT'
from dual;
END m04_get_class_list;
END m04_get_class_list_pkg;
/
Above refcursor is returning 3 rows, above should be sufficient for what you are doing.
To test this in oracle environment to make sure it worked, I created a function and then called that function.
I created a function:
create or replace function m04_get_class_list_fn (pi_yr_sess IN VARCHAR2
,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
-- ,pi_subj13 IN VARCHAR2, pi_subj14 IN VARCHAR2, pi_subj15 IN VARCHAR2
)
--return of refcur via a function, causing problem in crystal, its ok in sql*plus
RETURN m04_get_class_list_pkg.tpk_return_refcur IS
return_cd varchar2(25);
-- TYPE return_refcur IS REF CURSOR;
po_return_refcur m04_get_class_list_pkg.tpk_return_refcur;
BEGIN
m04_get_class_list_pkg.m04_get_class_list (pi_yr_sess
,pi_subj1 , pi_subj2 , pi_subj3 , pi_subj4
,pi_subj5 , pi_subj6 , pi_subj7 , pi_subj8
,pi_subj9 , pi_subj10 , pi_subj11 , pi_subj12
,po_return_refcur);
END m04_get_class_list_fn;
Called the function using the block code in sql plus using below code:
Note, I am passing data for first two parameters via a function, rest I set them to null. Ref cursor is returned all the way from the package body to function to this block; you can loop thru the ref cursor. I was using the above stored procedure in crystal reports, first I tested in oracle environment, before I used it in crystal reports.
DECLARE
TYPE return_refcur IS REF CURSOR;
po_return_refcur return_refcur;
vc_col1 VARCHAR2(150);
vc_col2 VARCHAR2(150);
return_cd varchar2(25);
BEGIN
po_return_refcur := m04_get_class_list_fn ('2002/FA','ENGL','','','','','','','','','','','');
LOOP
FETCH po_return_refcur
INTO vc_col1;
EXIT WHEN po_return_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE
('vc_col1:'||vc_col1);
END LOOP;
CLOSE po_return_refcur;
END;
Note : I have not executed the above code in a very long time, when I did it worked, I don't believe I have changed anything since then.
Also have a quick look at this link about multiple cursors and SSRS. http://www.developer.com/db/article.php/3524781
So far I have not called oracle stored procedures using SSRS, looks like SSRS makes it easy.
I have experimented calling Oracle stored procedures that use ref cursors in sql server stored procedures ( one way of doing this is you have to use .NET CLR and assemblies), this is for your information only, not related to your question.
I hope it helps. In case you have not used oracle, good to talk to some one who works in oracle environment at your work place.
Sam
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply