Re: Yield to Other application from PL/SQL

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