Skip to Main Content
  • Questions

  • DBMS_SCHEDULER with Stored Procedure and output parameters

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, A.

Asked: January 21, 2009 - 1:21 pm UTC

Last updated: January 30, 2009 - 9:08 pm UTC

Version: 10.2.0.4.0

Viewed 10K+ times! This question is

You Asked

I am trying to define a job using DBMS_SCHEDULER to execute a stored procedure. This procedure returns an output paramter. I found examples of defining input parameters to be sent but haven't found an example that uses an output parameter with the scheduler. Is there a way to achieve that?

I did a search on this site as well. If there is an example on the site and I didn't find it, please forgive as it is not for lack of trying.

Thanks,

and Tom said...

hah, and where would this OUT parameter go??? what would happen to it? the scheduler isn't expecting it...


You would schedule an anonymous plsql block or create a procedure that takes all input parameters.

for example, schedule

declare
  l_some_output number;
begin
   my_procedure( l_some_output );
   insert into my_own_table_of_my_own_invention ( msg ) values
   ( 'hey, we ran the procedure and it said ' || l_some_output ||
     ' is the answer to life, the universe and everything' );
end;


You need to do something with that output (else why is it being returned. So call your procedure, get your output and do something with it.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

as input to another sp

A B, January 29, 2009 - 1:11 pm UTC

This output parameter will be used as an input parameter to a second stored procedure to be called from within the same block.

Thanks
Tom Kyte
January 30, 2009 - 2:34 pm UTC

huh?

how can you be using a job then. Since a job by definition runs in another session, at some future point in time, asynchronously.

Sounds like you just sort of want to - well you know - call this procedure directly.

what I mean is

A reader, January 30, 2009 - 4:03 pm UTC

job_action => 'begin declare
l_out_from_1 number;
begin
my_procedure( l_out_from_1 );
proc2( l_out_from_1 ) -- used here as input param
end;'
Tom Kyte
January 30, 2009 - 4:25 pm UTC

there you go, you are done. schedule that.

seems obvious? doesn't it? I mean, I told you originally:


declare
  l_some_output number;
begin
   my_procedure( l_some_output );
   insert into my_own_table_of_my_own_invention ( msg ) values
   ( 'hey, we ran the procedure and it said ' || l_some_output ||
     ' is the answer to life, the universe and everything' );
end;



run the procedure, get the output, do something with it. Ok, so you don't want to insert it into a log table, you want to send it to proc2 - there you go, do that.


sorry if I offended you

A reader, January 30, 2009 - 6:34 pm UTC

I was just saying that was how I wanted to use it in a job, not stand alone
Tom Kyte
January 30, 2009 - 9:08 pm UTC

ok, now I'm confused - that is how you would use it in a job? That is your job

begin declare
  l_out_from_1 number;
begin
   my_procedure( l_out_from_1 );
   proc2( l_out_from_1 ) -- used here as input param
end;


we are done then right? You have the answer?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library