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

[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