Hello,
I want to execute an Oracle stored procedure that takes in 1 parameter and returns 4 values.
Is there an easier way to access the values the procedure returns than this :
run a simple plsql code in 24x7 that creates a temporary table with 4 columms,
execute the stored procedure,
store the values returned in the temp table
create new select that selects the values from the temp table
drop temp table

Thanks.

You can create an Oracle function that calls up your stored procedure, concatenates result into 1 value and return it as a result. The in 24x7 you can use simple SELECT my_function() FROM dual; query to get the concatenated result.
The function code could look like
CREATE FUNCTION my_function RETURN VARCHAR2
IS
var1 VARCHAR2(100);
var2 NUMBER;
var3 DATE;
var4 CHAR(3);
BEGIN
my_procedure(var1, var2, var3, var4);
RETURN var1 || '^' || var2 || '^' || var3 || '^' || var4;
END;

In 24x7 you can use GetToken statement to easily parse the returned value and get 4 different pieces. Both Oracle and 24x7 can do automatic data type conversions.

Hope this heps

: Hello,
: I want to execute an Oracle stored procedure that takes in 1 parameter and
: returns 4 values.
: Is there an easier way to access the values the procedure returns than this :
: run a simple plsql code in 24x7 that creates a temporary table with 4
: columms,
: execute the stored procedure,
: store the values returned in the temp table
: create new select that selects the values from the temp table
: drop temp table

: Thanks.

Thanks for that.
I actually need the four values it returns. I think what I was getting at was is there an easier way to get the values from a stored procedure as I will want to manipulate them and email them in 24x7.
I have got a plsql script that will store them in a table but I do not think you can execute a plsql file from 24x7 without runng the sqlplus program - is this correct or can you run a sql file from 24x7 (like you can from sqlplus, because if I try it as a DatabaseExecute command I get errors).
eg
DatabaseExecute("@c:\jobcode\mysqlprog.sql", rows_affected)

returns an Oracle error but would work file if I ran it from sqlplus prompt.

Load your file into a a varible and execute it
Example:
Dim( SQL, string )
FileReadAll("c:\\jobcode\\mysqlprog.sql", SQL)
DatabaseExecute(SQL)

But if you go this way you will have no access to the returned value as it it will be executed as PL/SQL block.
DatabaseRetrieve instead can be used to fetch the returned value, but it can run only SELECT statements.

: Thanks for that.
: I actually need the four values it returns. I think what I was getting at was
: is there an easier way to get the values from a stored procedure as I will
: want to manipulate them and email them in 24x7.
: I have got a plsql script that will store them in a table but I do not think
: you can execute a plsql file from 24x7 without runng the sqlplus program -
: is this correct or can you run a sql file from 24x7 (like you can from
: sqlplus, because if I try it as a DatabaseExecute command I get errors).
: eg
: DatabaseExecute("@c:\jobcode\mysqlprog.sql", rows_affected)

: returns an Oracle error but would work file if I ran it from sqlplus prompt.