Calling a Stored Procedure with output parameters from Query Templates

[Former Member]

This is same problem which Shalaka Khandekar logged earlier. This new thread gives the complete description about our problem. Please go through this problem and suggest us a feasible solution.

We encountered a problem while calling a stored procedure from MII Query Template as follows-

1. Stored Procedure is defined in a package. Procedure takes the below inputs and outputs.

a) Input1 - CLOB

b) Input2 - CLOB

c) Input3 - CLOB

d) Output1 - CLOB

e) Output2 - CLOB

f) Output3 - Varchar2

2. There are two ways to get the output back.

a) Using a Stored Procedure by declaring necessary OUT parameters.

b) Using a Function which returns a single value.

3. Consider we are using method 2-a. To call a Stored Procedure with OUT parameters from the Query Template we need to declare variables of

corresponding types and pass them to the Stored Procedure along with the necessary input parameters.

4. This method is not a solution to get output because we cannot declare variables of some type(CLOB, Varchar2) in Query Template.

5. Even though we are successful (step 4) in declaring the OUT variables in Query Template and passed it successfully to the procedure, but our procedure contains outputs which are of type CLOB. It means we are going to get data which is more than VARCHAR2 length which query template cannot return(Limit is 32767

characters)

6. So the method 2-a is ruled out.

7. Now consider method 2-b. Function returns only one value, but we have 3 different OUT values. Assume that we have appended them using a separator. This value is going to be more than 32767 characters which is again a problem with the query template(refer to point 5). So option 2-b is also ruled out.

Apart from above mentioned methods there is a work around. It is to create a temporary table in the database with above 3 OUT parameters along with a session specific column. We insert the output which we got from the procedure to the temporary table and use it further. As soon the usage of the data is completed we delete the current session specific data. So indirectly we call the table as a Session Table. This solution increases unnecessary load on the database.

Thanks in Advance.

Rajesh

[sidnooradarsh]

Hi Rajesh,

Instead of using a single value return Function use a Table-valued return User defined Function and avoid all the separators and large single valued output.

Something like this:-

CREATE FUNCTION fn_ReturnCustTable (@Region nvarchar(30))

RETURN table

AS

RETURN SELECT CustomerID,CompanyName FROM

Customers

WHERE Region= @Region

Hope this might help

Regards,

Adarsh

[Former Member]

Rajesh,

please check if this following proposal could serve you.

Define the Query with mode FixedQueryWithOutput. In the package define a ref cursor as IN OUT parameter. To get your 3 values back, open the cursor in your procedure like "Select val1, val2, val3 from dual". Then the values should get into your query.

Here is an example how this could be defined.

Package:

type return_cur IS ref CURSOR;

Procedure:

PROCEDURE myProc(myReturnCur IN OUT return_cur) ...
...
OPEN myReturnCur FOR SELECT val1, val2, val3  FROM dual;

Query:

DECLARE
  MYRETURNCUR myPackage.return_cur;
BEGIN

  myPackage.myProc(
    MYRETURNCUR => ?
  );

END;

Good luck.

Michael