1

I'm getting a SYSREFCUROSOR as output parameter from a stored procedure, How to call that SP in a oracle scheduler job and pass that parameter?

abhinay
  • 11
  • 4
  • What do you plan to do with the ref cursor that is generated? What will use/consume it? – Alex Poole Jun 24 '15 at 12:28
  • Maybe write a function which returns your RefCursor and call this function inside your scheduler job. – Wernfried Domscheit Jun 24 '15 at 13:31
  • just we will call that stored procedure in job. We don't consume it inside the job – abhinay Jun 24 '15 at 13:52
  • You can't pass around a cursor between different sessions. Please show some code or pseudo-code, as it it not clear what you are attempting. – OldProgrammer Jun 24 '15 at 14:19
  • This is my procedure : create or replace PROCEDURE CSIP_GETRECORDS_SP (i_fromdate IN DATE ,i_todate IN DATE ,p_result_cur OUT SYS_REFCURSOR ) The cursor will get a set of records between the provided range and that sp is called by the scheduler job and cursor output is used to sent an email. @OldProgrammer – abhinay Jun 25 '15 at 06:52
  • Please add code by editing the question, not as a comment. The cursor output will be used by *what* to send an email? – Alex Poole Jun 25 '15 at 20:24
  • the cursor output should be used by scheduler_job to send an email @AlexPoole – abhinay Jul 02 '15 at 15:29
  • You want the *scheduler* to use use your ref cursor when it sends [a notification email](http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#CIAFGIHH), as part of the ADD_JOB_EMAIL_NOTIFICATION body argument? That isn't at all clear from the question. And I don't think it's possible. Why not have the procedure send an email itself? – Alex Poole Jul 02 '15 at 15:48

1 Answers1

0

If you are discarding the ref cursor then create a wrapper procedure that does that; something like:

create procedure wrapper_proc as
  l_refcursor sys_refcursor;
begin
  orig_proc(l_refcursor);
  close l_refcursor;
end;
/

And then call the wrapper procedure in your scheduled job instead.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318