How to retrieve a string from an Oracle Proc ?

Hello,
I have an Oracle Stored procedure that returns a string which I want to execute and email the result.
Here is my JAL :

// Execute SQL that emails how many records have been
// loaded into the Transact holdall table and how many were sent

Dim result, string
Dim success_flag, number
// call procedure that calculates no of recs loaded
DatabaseConnect( "Inpro_Dell" )
DatabaseExecute( "execute pkg_transact.report_on_transact_load(result)", success_flag )
DatabaseDisconnect

MailSend( "Exchange Settings", "schedule12", "Dominic Klein", "No of Transact Records Loaded", result )
END:

But it doesn't like my DatabaseExecute command because of result.
I want to return the string into my variable called result.
How do I call a database procedure or function and retrieve the results into a variable or variables ??

DatabaseExecute doesn't return any result-sets or OUT variables, it simply executes whatever is given as a SQL command(s).

However, you can use it for dynamic things like the following:

Dim( result_value, string )
DatabaseConnect( "Inpro_Dell" )
DatabaseExecute( "create worktable(result varchar2(4000));", rows_affected )
DatabaseExecute( "declare v_result varchar2(4000); begin pkg_transact.report_on_transact_load(v_result); insert into worktable(result) values (v_result); end;", rows_affected )
DatabaseRetrieve( "select result from worktable", rows_affected )
DatabaseExecute( "drop table worktable;", rows_affected)

DatabaseGet(1, 1, result_value)
DatabaseDisconnect

You may want to add some error handling to this processing such as to check if the table already exit, truncate it, otherwise create it, also to check if the retrieve returned any rows before accessing them

If you want to return results from SQL functions this is very simple, see example below.

DatabaseRetrieve("select my_function(parameter1, parameters2) from dual", rows_affected)
DatabaseGet(1, 1, my_variable)

If your function has dynamic parameters that you want to pass from the job script, build the complete command using ConcatEx statement in 24x7 then execute your SQL as in the following example:

ConcatEx("select my_function(", numeric_value, ",'", string_value, "') from dual", my_select)
DatabaseRetrieve(my_select, rows_affected)

: Hello,
: I have an Oracle Stored procedure that returns a string which I want to
: execute and email the result.
: Here is my JAL : // Execute SQL that emails how many records have been
: // loaded into the Transact holdall table and how many were sent

: Dim result, string
: Dim success_flag, number
: // call procedure that calculates no of recs loaded
: DatabaseConnect( "Inpro_Dell" )
: DatabaseExecute( "execute
: pkg_transact.report_on_transact_load(result)", success_flag )
: DatabaseDisconnect

: MailSend( "Exchange Settings", "schedule12",
: "Dominic Klein", "No of Transact Records Loaded",
: result )
: END: But it doesn't like my DatabaseExecute command because of result.
: I want to return the string into my variable called result.
: How do I call a database procedure or function and retrieve the results into
: a variable or variables ??

Yes that first bit looks fine at a glance, ie where you create a temporary table - have you missed off the TABLE keyword in create ? Also why do you need the rows_Affected when creating a table or is it just used for error checking ?

Regards.

Yes, there should be a valid CREATE TABLE command

: Yes that first bit looks fine at a glance, ie where you create a temporary
: table - have you missed off the TABLE keyword in create ? Also why do you
: need the rows_Affected when creating a table or is it just used for error
: checking ?

: Regards.