PASSING VALUE TO WINDOW FROM ORACLE PROCEDURE

[Former Member]

Hi,

I am calling a pretty time consuming oracle procedure from a window. I would like to show the status of the procedure to he the user. Is there any way to pass a value from the Oracle Procedure to the PB Window ?

Regards

JAIMOHAN

[Former Member · Accepted solution]

Hi  J;

  => No, once you call an SP from PB its synchronous and your PB application will wait until the SP ends to get notified.

However .... off the top-of-my-head:

1) You would need to make your PB application section that calls the SP asynchronous using multi-threading.

2) The SP would have to be modified to post progress information to another DB table.

3) The main PB application's main thread could then be modified to display the progress table information to the user on a timer.

4) The PB application sub-thread that calls the SP would have to use "post back" to the main application thread when it was notified by the SP that it was completed.

Regards ... Chris

[Former Member · Accepted solution]

Hi Chris,

Thank you for the reply. Shall get back once I complete the timing object logic.

Regards

[former_member190719]

You might want to look at using Oracle's DBMS_JOB package to fire off the procedure asynchronously and the DBMS_PIPE package to return status information the the PowerBuilder application.

[Former Member]

Dear Bruce,

Thank you for the reply.

I have not used Oracle extensively. So I am not that much familiar about the DBMS_JOB package. I went through the DBMS_PIPE literature, but could not comprehend much. I shall be grateful if you can throw a little more light on it. Is there any example codes available on this?

Regards

[former_member190719]

DBMS_SCHEDULER is actually a better (newer) option than DBMS_JOB.  What you're doing is delegating the running of the stored procedure to a process on the database server rather than your client.  Your client just starts the job.

Since at that point the job is running under a different session, you need some sort of inter-session communications channel to transmit the status information back to the client.  That's where DBMS_PIPE comes in.  Here's one particular tutorial on using it:

ORACLE-BASE - DBMS_PIPE : For Inter-Session Communication