SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
retrieving values from database stored procedu

 
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite View previous topic
View next topic
retrieving values from database stored procedu
Author Message
Dominic Klein



Joined: 23 Nov 2001
Posts: 132

Post retrieving values from database stored procedure Reply with quote

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.

Mon Feb 10, 2003 7:55 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: retrieving values from database stored procedu Reply with quote

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.

Mon Feb 10, 2003 9:34 am View user's profile Send private message
Dominic Klein



Joined: 23 Nov 2001
Posts: 132

Post Re: retrieving values from database stored procedu Reply with quote

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.

Mon Feb 10, 2003 9:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: retrieving values from database stored procedu Reply with quote

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.

Mon Feb 10, 2003 12:30 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.