|
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
laiko
Joined: 11 Feb 2010 Posts: 43 Country: India |
|
Calling a function/procedure in database command |
|
I am running a job of type 'database command'. This is body of SQL commands:
|
|
declare
i integer;
begin
i := My function(1, 'a');
end; |
After running, it logs 'Job completed with exit code 0. This exit code does not satisfy exit code condition. Job failed'.
How should I modify the code above or any setting to make it work?
Can I also use job type 'database command' if I want to execute a procedure and based on the output parameters, it will decide which job to trigger next? How do I know the 'Successful Exit codes' for this type of Job. Unlike in running a 'program or batch file', this 'database command' job type does not allow me to specify successful exit codes.
|
|
Wed Mar 10, 2010 4:00 pm |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
In order to help you with troubleshooting no-zero job exit code, I need to see debug.log and jdbc.log files after this job run. Please enable tracing options in the scheduler (Tools/Options menu; Log tab, Trace enabled) re-run the job and post content of debug.log and jdbc.log files specific to the run of that database job (see timing of log records)
In regard to the stored procedure output parameter…in a database type job you cannot use an output parameter from a procedure executed on db server to control what to run next on the scheduler side. But here is what you can do to implement this requirement. . Instead of a database job, use JavaScript job with DatabaseConnect, DatabaseExecute and DatabaseRetrieve commands. In the DatabaseExecute call anonymous PL/SQL block of code in which you call the stored procedure. In the same block save the output value into a session variable. Then call DatabaseRetrieve to retrieve this value. You can then use "if" or "case" logic to figure out what to do next.
Here is a template (an idea), not a ready to use script
|
|
Database.connect( "PRODUCTION" );
// exec proc. and save the output value in a session-level variable
Database.execute( "DECLARE myVAR INT; BEGIN MySchema.MyStoredProcedure(myVAT); DBMS_SESSION.SET_CONTEXT(...place the variable here into some context...); END;" );
//retrieve the value fro msession-level variable
var myVal = Database.retrieve( "SELECT DBMS_SESSION.GET_CONTEXT(...) FROM DUAL");
Database.disconnect( "PRODUCTION" );
if (myVal == 1)
Scheduler.runJob(125);
else
Scheduler.runJob(126; |
|
|
Wed Mar 10, 2010 8:43 pm |
|
|
|
|
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
|
|
|