Is it possible to display comments to user while executing a procedure in a package. My package has 3 procedures. I am calling each one after other. I want to display comments on console like procedure xyz is executing, procedure executed successfully. I added comments inside procedure like DBMS_OUTPUT.PUT_LINE('PROCEDURE EXECUTED SUCCESSFULLY') but didn't worked for me.
FYI i am using oracle 11g in windows 7 system.
Asked
Active
Viewed 4,231 times
2
mallikarjun
- 1,862
- 5
- 23
- 47
-
2what do you call "console" ? Sql plus ? Something else ? What's the environment where user should see the comments ? – Raphaël Althaus Sep 04 '12 at 08:05
-
Sorry for that DIsplay in SQLPLUS(when i execute package) – mallikarjun Sep 04 '12 at 08:53
-
See for some suggestions this post: http://stackoverflow.com/questions/1523949/have-pl-sql-outputs-in-real-time/2643269 – Rene Sep 04 '12 at 11:24
1 Answers
10
You can't use DBMS_OUTPUT to display information on a procedure while it is running. This is because DBMS_OUTPUT.put_line doesn't display data on screen, rather, the data is put in a queue that is later read by the calling client (This queue is also invisible outside of its transaction). If you use SQL*Plus the queue is read and displayed automatically at the end of the procedure if you have SET SERVEROUTPUT ON.
Other means exist to follow the progress of a procedure while it is running:
- You could write to a file instead.
UTL_FILE.put_linewill write directly if the parameterautoflushis set to true. - You could set session variables with
DBMS_APPLICATION_INFO. These variables can be read with another session by queryingv$session. - You could use
AUTONOMOUS_TRANSACTIONSto log progress information in a dedicated table. This table can be queried by another session simultaneously.
As you can see you would need another process to read the information while it is written. In some applications, this would be achieved by running the main batch job in a new separate process, for example by calling DBMS_JOB or DBMS_SCHEDULER while the calling transaction loops on the progress table or file until the job is complete.
SQL*Plus is not an interactive client, you will need some more sophisticated environment to achieve this functionality.
Community
- 1
- 1
Vincent Malgrat
- 66,725
- 9
- 119
- 171
-
How to use DBMS_APPLICATION_INFO comments. could you give me an example. – mallikarjun Sep 04 '12 at 09:08
-
See this [answer on another related question](http://stackoverflow.com/a/1523970/119634) – Vincent Malgrat Sep 04 '12 at 09:14