Skip to Content
author's profile photo Former Member
Former Member

Stored Procedure(SP) with ref cursor not working in XI R2

Hi,

We have migrated from 5.x to Xi R2. The environment is Xi R2 on Windows which connects to DWH Server on Unix box.

We are facing 1 issue:

In 5.x the SP could take numerous in ot out or in/out parameters but in XI R2 Architecture it supports

Supported Oracle Stored Procedures

  1. Any PL/SQL procedure that returns a result set through REF cursor.

  2. PL/SQL stored procedures that have at least one IN/OUT REF cursor variable parameter and no OUT or IN/OUT parameters.

Unsupported Oracle Stored Procedures

  1. Any PL/SQL procedure that does not return a result set through REF cursor parameter

  2. Any PL/SQL procedures having at least one OUT parameter

  3. Any PL/SQL function

  4. Any PL/SQL procedure having one IN/OUT parameter of a type different than a REF cursor

  5. Any PL/SQL Table function

We have the following SP which works absolutely fine with 5.x when we try to run the report in XI R2 it gives an error Invalid number of arguments , can anyone help me to convert it to make it compatible with XI R2

  • STORED PROCEDURE***********************************

CREATE OR REPLACE Procedure SSS

(P1 IN VARCHAR2, P2 IN VARCHAR2_3000 .symbol_list%TYPE, P3 IN VARCHAR2,P_DATE DATE, P_FROM_TIME VARCHAR2, P_TO_TIME VARCHAR2, cat_cv IN OUT Pack.catcurtyp1 )

AS

LN_SID1 NUMBER;

C1_SYMBOL VARCHAR2(10);

T1 VARCHAR2(5);

M1 VARCHAR2(80);

M1 NUMBER;

B1 NUMBER;

S1 NUMBER;;

GROSS_QTY NUMBER;

NET_BUY NUMBER;

NET_SELL NUMBER;;

string1 VARCHAR2(3000););

string2 VARCHAR2(3000););

string3 VARCHAR2(3000););

pos NUMBER;

START_TIME DATE;

END_TIME DATE;

LN_SID1 NUMBER;;

user_name VARCHAR2(50););

BEGIN

START_TIME:=TO_DATE(CONCAT(TO_CHAR(P_DATE,'DD-MON-YYYY

'),P_FROM_TIME),'DD-MON-YYYY HH24:MI:SS');

END_TIME:=TO_DATE(CONCAT(TO_CHAR(P_DATE,'DD-MON-YYYY

'),P_TO_TIME),'DD-MON-YYYY HH24:MI:SS');

SELECT SID INTO LN_SID1 FROM V$MYSTAT WHERE ROWNUM < 2;,

DELETE FROM A WHERE user_id=ln_sid1;

COMMIT;

SELECT username INTO user_name FROM v$session

WHERE SID=(SELECT SID FROM v$mystat WHERE ROWNUM<2);

string1:=P_SYMBOL;

WHILE INSTR(string1,',')<>0

LOOP

some conditions

INSERT INTO A

SELECT col_s, col_t, col_u,col_v

FROM B

WHERE some conditions

COMMIT;

END LOOP;

STRING3:=STRING1;

INSERT INTO A

SELECT col_s, col_t, col_u,col_v

FROM B

WHERE some conditions

COMMIT;

OPEN cat_cv FOR

SELECT

some cols

from

( select some cols from

( ....)

union all

select some cols from

( ....)

)

WHERE..)

some condition

GROUP BY some cols

;

END;

/

  • END OF STORED PROCEDURE***********************************);

***********************************PACKAGE*************************************************************

CREATE OR REPLACE PACKAGE SSS

AS

TYPE CURTYP IS record

(

all variables are defined here

);

TYPE CATCURTYP1 IS REF CURSOR RETURN curtyp;

END PACK ;

/

  • END OF PACKAGE***********************************);

Edited by: Shreekantha Velankar on Oct 29, 2009 7:54 AM

Edited by: Shreekantha Velankar on Oct 29, 2009 7:54 AM

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Nov 11, 2009 at 01:37 PM

    Hi Shree,

    Following information might be helpful in resolving the issue.

    Starting with BOE 6.x, as the data access guide clearly states, the support for Oracle stored procedure is limited and this is very well documented in the BOE 6.x data access guide enclosed.

    As the document clearly states and this has been the case since BOE 6.x and nothing has changed in XI R2 or XI R3 .

    Supported Oracle Stored Procedures

    Any PL/SQL procedure that returns a result set through REF cursor.

    PL/SQL stored procedures that have at least one IN/OUT REF cursor variable parameter and no OUT or IN/OUT parameters.

    Unsupported Oracle Stored Procedures

    Any PL/SQL procedure that does not return a result set through REF cursor parameter

    Any PL/SQL procedures having at least one OUT parameter

    Any PL/SQL function

    Any PL/SQL procedure having one IN/OUT parameter of a type different than a REF cursor

    Any PL/SQL Table function

    Document also clearly states that ensure your stored procedures are compatible with BOE

    Also : For Oracle SP we only support ONE in out cursor. If more than one cursor is declared, they will be ignored. And if you have one cursor defined as IN and one as IN OUT and this is not supported.

    Test observation : when you add IN OUT parameter together, Deski does not seem to interpret Oracle IN OUT parameter until you explicitly define a IN parameter in the stored procedure.

    Resolution

    1. Create a cursor inside a Package

    Package creation :

    SQL> show user

    USER is "HR" (We have a user called HR in oracle 10g)

    SQL> CREATE OR REPLACE PACKAGE pack_employees IS

    TYPE catcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

    END pack_employees;

    Package created.

    SQL> commit;

    Commit complete.

    2. Create the stored procedure using the defined cursor.

    CREATE OR REPLACE PROCEDURE get_employees (hiredate IN employees.hire_date%type , cat_

    cv IN OUT pack_employees.catcurtyp) as

    BEGIN

    OPEN cat_cv FOR SELECT * FROM

    employees;

    END;

    SQL> /

    Procedure created.

    SQL> commit;

    Commit complete.

    SQL> select hire_date from employees where employee_id=107;

    HIRE_DATE

    -


    07-FEB-99

    3. Create a connection Test SP using Oracle 10g client in designer.

    4. Create a deski report using Conenction Test SP and select the stored procedure get_employees and click on finish

    5. Now you get the Stored Procedure editor for get_employees wherein you could enter a value which you could edit

    6. Enter date : 07/02/1999 and run the report.

    Now the report runs successfully.

    Regards,

    Sarbhjeet Kaur

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.