Oracle Stored Procedure with out parameter

[Former Member]

Good morning,

Is it possible to use an Oracle stored procedure with out parameters in MII ?

If yes, what is the manipulation to see the values of parameters Out?

Thank you

[Former Member · Accepted solution]

Michael,

This is the MII query template :

DECLARE 
STRCOMPTERENDU NVARCHAR2(200);
BEGIN 
STRCOMPTERENDU := NULL;
XMII.SP_VALIDATEPROCESSORDERSLIST2 ( STRCOMPTERENDU => [Param.1]  );
COMMIT; 
END;

and the stocked procedure code

CREATE OR REPLACE PROCEDURE XMII.SP_ValidateProcessOrdersList2(strCompteRendu OUT nVarchar2) IS
tmpVar NUMBER;
debugmode INT;
strClauseSql varchar(2048);
strListPOactif varchar(1024);
 dtmTimeStamp DATE; 
/******************************************************************************
   NAME:       SP_ValidateProcessOrdersList
   PURPOSE:    
   REVISIONS:
   Ver        Date        Author           Description
   -
  -
  -
  -
   1.0        18/06/2008          1. Created this procedure.
   NOTES:
   Automatically available Auto Replace Keywords:
      Object Name:     SP_ValidateProcessOrdersList
      Sysdate:         18/06/2008
      Date and Time:   18/06/2008, 18:45:32, and 18/06/2008 18:45:32
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
   tmpVar := 0;
   debugmode := 0; 
   -- lecture date systeme pour time stamp
   select sysdate  into dtmTimeStamp from dual;
   if debugmode = 1 then
    DBMS_OUTPUT.put_line('SP_ValidateProcessOrdersList');
   end if;
   -- insertion du bloc dans le log
   insert into LOG_ORDER
    (DATE_ORDER,BLOCK_ORDER,ID_LOG_ORDER)
   values
   (dtmTimeStamp,'SP_ValidateProcessOrdersList',ID_LOG_ORDER.nextval);
   Commit;
    if debugmode = 1 then
    DBMS_OUTPUT.put_line('insertion LOG OK');
   end if;
strCompteRendu := '0123456-896;0123456-897';
commit;  
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
     ROLLBACK;
     -- insertion du bloc dans le log
   insert into LOG_ORDER
    (DATE_ORDER,BLOCK_ORDER,ID_LOG_ORDER)
   values
   (dtmTimeStamp,' ',ID_LOG_ORDER.nextval);
   COMMIT;
       -- Consider logging the error and then re-raise
       RAISE;
END SP_ValidateProcessOrdersList2;
/

Thanks for your help

Alexandre

[Former Member · Accepted solution]

Hi Alexandre,

the error "invalid column index" occurs because MII expects a cursor when using "FixedQueryWithOutput".

Try to use the construct for returning a single value:

Query

XMII.SP_VALIDATEPROCESSORDERSLIST2 ( myReturnCur => ? );

Procedure

type return_cur IS ref CURSOR;

CREATE OR REPLACE PROCEDURE XMII.SP_ValidateProcessOrdersList2(myReturnCur OUT return_cur)

...

strCompteRendu := '0123456-896;0123456-897';

...

OPEN myReturnCur FOR SELECT strCompteRendu FROM dual;

Using this MII gets one row with one column (strCompteRendu) as return value.

Michael