Hi All,

I have a ORACLE stored procedures which is returning one output and running fine.My WF8 code is...
--------------------------------------------

-DEFAULT &ID=10;
ENGINE SQLORA SET DEFAULT_CONNECTION conname
ENGINE SQLORA
EX conname.AVINASHCURSOR &ID;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS MPHOLD
END
TABLE FILE MPHOLD
PRINT *
END
-------------------------------------------
-------------------------------------------

But the procedure has been change. Now It has two out parameter. 1 is 'SYS_REFCURSOR' and 2nd is 'VAARCHAR'.......


Please give me the correct WF8 code which can accept two out parameter.

This message has been edited. Last edited by: <Kathryn Henning>,

Just provide all parameters in your EX line right after the procedure name:

ENGINE SQLORA
EX conname.AVINASHCURSOR param1 param2 param3 param4 etc;
...

Please note that parameters are separated by spaces instead of commas.

These params are input variable or both -njsden.
and applying this, it is not working.

my procedure is-
--------------------------------------------------
--------------------------------------------------
create or replace
PROCEDURE AvinashCursor(
v_num IN NUMBER,
v_name IN VARCHAR,
VAR1 IN OUT NUMBER,
c_dbuser IN OUT SYS_REFCURSOR
)
AS

BEGIN
VAR1 := 1;
OPEN c_dbuser FOR
SELECT * FROM Avinash_table where user_id=v_num AND CREATED_BY=v_name;
END AvinashCursor;
--------------------------------------------------
--------------------------------------------------

and am trying to call by this code-
-------------------------------------------------
-DEFAULT &ID=1;
-DEFAULT &NAME='avinash';
-DEFAULT &C_NAME=123;
ENGINE SQLORA SET DEFAULT_CONNECTION conname
ENGINE SQLORA
EX conname.AVINASHCURSOR &ID,'&NAME',&C_NAME;
------------------------------------------------

error comming-
------------------------------------------------
------------------------------------------------

(FOC1671) SET SERVER TPHRAT_TST COMMAND FOR ORACLE INTERFACE OUT OF SEQUENCE
(FOC1671) SET SERVER TPHRAT_TST COMMAND FOR ORACLE INTERFACE OUT OF SEQUENCE
(FOC1400) SQLCODE IS 6550 (HEX: 00001996)
: ORA-06550: line 1, column 44:
: PLS-00363: expression '123' cannot be used as an assignment target
: ORA-06550: line 1, column 7:
: PL/SQL: Statement ignored
L (FOC1405) SQL PREPARE ERROR.

------------------------------------------------
------------------------------------------------

This message has been edited. Last edited by: Avinash,

Hi Avinash,

As per IBI, Stored Procedures must adhere to the following rules:

A cursor must be defined with:
1) The TYPE statement in a PACKAGE or PROCEDURE.

2) An associated record layout of the answer set to be returned.

3) The cursor must be opened in the procedure.

4) No fetching is allowed in the stored procedure. The Adapter for Oracle fetches the answer set.

CREATE OR REPLACE PACKAGE pack1 AS
TYPE nfrectype IS RECORD (
pename emp.ename%TYPE,
pjob emp.job%TYPE,
pMgr emp.mgr%TYPE);
TYPE nfcurtype IS REF CURSOR RETURN nfrectype ;
PROCEDURE proc1(v_empno IN NUMBER,c_saltable IN OUT nfcurtype);
END pack1 ;
/
sho error

CREATE OR REPLACE PACKAGE BODY pack1 AS
PROCEDURE proc1 (v_empno IN NUMBER,c_saltable IN OUT nfcurtype)
IS
BEGIN
OPEN c_saltable FOR SELECT ename, job, mgr
FROM scott.emp
WHERE EMPNO = v_empno;
END proc1 ; -- end of procedure
END pack1; -- end of package body
/
sho error
  

Thanks,
Rifaz