Calling Oracle stored procedure from SQL2005

  • 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

  • double check if you have a linked server named ODBDCP.

    Alex S
  • There is no problem with linked server! But the error with submitting query is still present. Any ideas?

  • 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

  • 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

  • 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.

  • 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

    askforsushil@gmail.com

  • 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.

  • I would like to check with you on how you resoved this issue.

  • 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