Re: Yield to Other application from PL/SQL

Thanks Dmitri !
 I'm running Personnel Oracle & PB5 on client & also talking to remote database running Oracle Server & I'm making log file on the client machine & don't want to get away with it.

So I don't really want to use dbms_pipe. However I would definitely try using dbms_job

i would appreciate if u send me send me code extract of PB (how u are calling SP which submits the job.)

& also the SP which is submitted.

I will try somethinglike this :

PB Code :
declare lp_ms_repl_submit procedure for ms_repl_submit(:adir, :afile, :aloglevel) ;
execute lp_ms_repl_submit;

fetch     lp_ms_repl_submit  into :li_job;
close     lp_ms_repl;

Oracle SP which submits the job :
create or replace procedure ms_repl_submit parameter_lsit as

    declare etc

    dbms_job.submit(jobno, here i have to submit main procedure ms_repl with Parameters)
end
/

create or replace main proc Par1, par2 par3 etc

    exec proc1
    exec proc2
   etc
   other stuff;

end /

question     1     is it correct or there is a better way ?
                 2    how do i pass parameter from the submitting procedure ?
                 3   Can I check  that the SP is over without using dbms_pipe

Thanks again

-Atul
please note that email address is akkane_at_yahoo.com

zd_at_ahaSPAMMENOT.ru wrote:

> Hey Atul,
>
> Your problem could be solved thru the use of DBMS_JOB Oracle package!
>
> I've implemented a progress bar window with a "Stop" button in PB 5.0.03
> using DBMS_JOB and DBMS_PIPE packages. This window shows stored
> procedure progress and allows user to cancel the procedure.
>
> The problem is when you call a stored procedure (SP), PowerBuilder waits
> until your SP terminates and returns anything (synchronous behavior).
> You can overcome this if you call DBMS_JOB.Submit procedure to execute
> your SP.
> Submit(...) puts your SP in the job queue and returns control to your
> application. If JOB_QUEUE_PROCESSES, JOB_QUEUE_INTERVAL,
> JOB_QUEUE_KEEP_CONNECTIONS parameters are properly set up in INITxxx.ORA
> file, Oracle checks for new entries in the job queue every
> (JOB_QUEUE_INTERVAL) seconds. When it finds out your SP waits there, it
> will create a new database session independent of your initial session
> in which you issued Submit proc. Finally Oracle will execute your SP in
> this newly created session and then close it.
>
> In my progress bar I've made use of DBMS_PIPE package to communicate
> with my SP. If the user clicks Stop button, it sends an appropriate
> message to the SP. This SP also sends messages to the application when
> certain amounts of work are done, so the user could see SP progress in
> percents.
>
> If I get it right your procedure writes something to a log file. So
> Oracle server runs on the same machine as your PB application? Please
> provide more details. Anyway, you could give up with the file and switch
> to DBMS_PIPE messages (you can always assemble log file in your PB
> application).
>
> Email me for more info about using DBMS_JOB/DBMS_PIPE with PowerBuilder
> or look up Oracle documentation.
>
> Regards,
>
> --
> Dmitri Zasypkin
> Moscow, Russia
>
> (remove SPAMMENOT to reply)
>
> Atul Kane wrote:
> >
> > Hi there !
> > I'm calling a stored procedure from within powerbuilder.
> > the stored procedure is very complex & does lot of things.
> > It creates a log file of what it does.
> > I'm displaying the contents of this file on the powerbuilder window, so
> > the user knows what is going on.
> >
> > Now my problem is how to code in PL/SQL to give up resources for a split
> > second (yield)
> > so that my Powerbuilder application can read the log file & display it
> > as it being written by procedure.
> >
> > I tried coding in the timer event of PB window but it doesn't work.
> > (timer event is very low priority of Windows OS) The file is displayed
> > only after the procedure
> > is completely executed.
> >
> > i'm using windows95, Oracle 7.3 , PB 5
> >
> > Any help would be appreciated
> >
> > -Atul
Received on Wed May 13 1998 - 00:00:00 CDT