Oracle Stored Procedure 1

[patvb]

How do you call an Oracle Stored Procedure that only has an OUT parameter in PowerBuilber (6.5.1)?

I can call an Oracle SP if it has only an input parameter using the DECLARE for the SP and an EXECUTE statement.

Thanks

 

[bubbly]

If u need to get the return values from the Oracle or any other Stored Procedure then u need to declare it in the Local External Functions section of the transaction object.

Then u could call the Stored procedure such as

eg, the transaction object is n_tr of type SQLCA.

Then it would be

Long ll_rc
ll_rc = SQLCA.up_getdata(input, input, output, output)

Here the input and output are the parameters. The input variables act as input parameters
and the output stores the return values from the stored procedure.

Prior to this while declaring the stored procedure in the Local External Functions u need to declare as follows

i.e,

Function long dbo.up_getdata( String ls_1, String ls_2, ref String ls_3, ref String ls_4)

Remember the output parameters are stored in the 3 and 4 parameters and it needs to be preceded by the "ref" keyword.

This tells the object that these are the return values. U could fetch the values by passing variables while call to Stored Procedure.

I hope u should be clear with it.

Else u put forward ur query in a more descriptive manner.

 

[bubbly]

And moreover u should pre-allocate the space for the variables.

say ls_3 and ls_4 are output variables


since both are strings check out the space allocated in the Stored Procedure.

let us guess it should be varchar2(255) for both

then before calling the Stored Procedure u need to

ls_3 = space(255)
ls_4 = space(255)
SQLCA.up_getdate(ls_1, ls_2, ls_3, ls_4)
and then
ls_3 = Trim(ls_3)
ls_4 = Trim(ls_4)

This should be done coz PB does not allocated space for the variables returned from the Stored Procedure.
If the space is not allocated then it would return a single character or nothing