Return value from procedure into bind variable

Toad Data Point
1

Using Toad Data Point 4.1.0.226 (32 bit) Working on Automation flow, have the variable Load_Success defined as Boolean earlier in the flow. Using the Execute Script activity, trying to capture the out Boolean parameter of a package.procedure call into my variable but keep getting Oracle errors. Here is the last attempt:

DECLARE
OUT_SUCCESS BOOLEAN;
BEGIN
CONTACT_FEED.LOAD_VCAST_ACCTS_TO_CONTACT(OUT_SUCCESS);
:Load_Success := OUT_SUCCESS;
END;

When I run, I get:

ORA-06550: line 5, column 34:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

Any help is appreciated.

2

Load_Success may not be data type Boolean but instead data type Int. Try changing the data type of Out_Success to Int so they are both integers. Return codes can often have several values for different types of errors with one value for success so the data type would not be boolean. Depends on how your function or stored procedure is set up.

3

Appreciate the response. Luckily I wrote the procedure called in my example, so I can control the parameters. Just to make sure, I changed things so that I first attempted to pass out a number, then later a string into a Automation variable. Data type didn’t matter, nothing seemed to get assigned. I then tried declaring a variable before the call, used it as the out parameter, and then assigned that variable to the DP bind variable after the call, but still could not capture the result.

I finally gave in to the less elegant approach of storing the result of the procedure call in the database and then adding a select statement after the procedure call to capture that info into the “Save result set in variable” variable. I just do not see any way to get DP to recognize the value of a procedure out parameter.

4

Looks like Toad does not play well procedure return values. What if you turned it into a select query in an Execute Script step followed by a set variable value step?

Select Contact_Feed.Load_Vcast_Accts_To_Contact(Out_Success) TestResult From Dual

name the result set variable say ResultSetVar

Then in the set variable value step set the variable value for Load_Success to #ResultSetVar.TestResult#

Just an idea.