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

[Former Member]

Hi,

Thanks a lot for your help

Regards

Alexandre

[Former Member]

Hi,

There are no column in my query only the output parameter.

Alexandre

[agentry_src]

Alexandre,

Can you post your script in the MII query template and the stored procedure script?

Thanks,

Mike

[Former Member]

Hi mickael,

Thanks for your response.

I still have a problem.

When i want to test the stored procedure in MII, i have an error.

This error is java.sql.SQLException: Invalid column index.

Do you have this problem ?

Regards

Alexandre

[agentry_src]

Does the column have an assigned name or alias? It will need one most likely.

Mike

[agentry_src]

Hi alexandre,

[Stored Procedure|]

Take a look at this posting. It should have the info you requested. Test it to make sure the output is available.

Although I don't think this would have a problem working, you may also want to try it with a FixedQuery with Output. I vaguely remember someone telling me that this is the correct scenario for using that mode. Not sure, though.

Good luck,

Mike

[Former Member]

Hi Alexandre and Michael,

we are often using stored procedures with output. Here is an example.

MII-Query

Use FixedQueryWithOutput

Query Details (example):

DECLARE
  myReturnCur PKG_MII.return_cur;
BEGIN 

  PKG_MII.getRow(
    myReturnCur => ?
  );

END;

StoredProcedure

type return_cur IS ref CURSOR;

PROCEDURE getRow(myReturnCur IN OUT return_cur) AS
  myField := VARCHAR2(100);
  
  BEGIN

    IF NOT myReturnCur%ISOPEN
    THEN

    -- select information into myField
    
    END IF;
   
    OPEN myReturnCur FOR SELECT myField FROM dual;
    
END getRow;

This will return one row with the myField column. If you want to return more than one row, you can write something like

OPEN myReturnCur FOR SELECT <fields> FROM <table>

Michael

[agentry_src]

Hi Michael,

I had hoped there was someone out there who was doing this particular scenario. Thanks for posting it in enough detail.

Best regards,

Mike