Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure Output Advice? (Oracle 9.2.0.1.0)
icon9.gif  Stored Procedure Output Advice? [message #299687] Tue, 12 February 2008 23:37 Go to next message
friendly300
Messages: 1
Registered: February 2008
Location: Sydney
Junior Member
Hi everyone,

This should be a simple answer for those who have the knowledge, but I have been battling for many days with it. I have a stored procedure "pr_Log" which captures inputs and uses dbms_output to output what is essentially a log file of run times. Within the middle of this procedure, I call another procedure "pr_mercerCntrlScript" that runs dependant on what parameters haver been used. To complicate matters I am using VBA as a front end to kick off the procedures and I can not (that I know of anyway), get to "set serveroutput on" from within the stored procedure. Similarly, within VBA, I can only call (again from my knowledge) procedures / functions and not PL/SQL scripts.

There my questions are (and I would be grateful for any help as I have read numerous web articles, but none seem to address this issue - including askTom!):

1. How can I get output from my stored procedures without having the ability to set serveroutput on?
2. If I can't get output without setting the serveroutput, is there a way of doing that in a stored procedure?
3. Is there a way of redirecting dbms_output (from "put_line") into a variable / cursor so that I can get that returned into the VBA code? (I've tried ref cursors, but they don't seem to pick up the dbms_output)
4. Is there a way of starting a procedure without setting serveroutput on before it and still get output (similar to Q1)?

Cut down code is as follows:
CREATE OR REPLACE
PROCEDURE pr_Log (  ps_database         IN      VARCHAR2
                  , ps_type             IN      VARCHAR2
                  , ps_category         IN      VARCHAR2
                  , pd_start_rep_period IN      DATE
                  , pd_end_rep_period   IN      DATE ) is
    BEGIN
            
        ls_output_name       :=     C_Header_Rec.v_output_name;
        ls_database          :=     ps_database;
        ls_type              :=     ps_type;
        ls_category          :=     ps_category;
        ld_start_rep_period  :=     pd_start_rep_period;
        ld_end_rep_period    :=     pd_end_rep_period;
--        ls_output_string     :=     ps_output_string;
        ln_proc_time         :=     dbms_utility.get_time();
        ln_start_time        :=     to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS');
        ln_duration          :=     ' ';

        dbms_output.enable(1000000);
        

     BEGIN
        OPEN c_Header; 
           FETCH c_Header INTO c_Header_Rec;
                
           dbms_output.put_line(rpad(ln_start_time        ,043)||
                                rpad(c_Header_Rec.v_client ,051) 
                                );
                                       
           dbms_output.put_line(rpad(ls_scriptname       ,036)||
                                rpad(ls_application      ,058) 
                                );
                                       
       dbms_output.put_line(rpad('Version: '||ls_revision ,039)||
                            rpad(ls_program_name          ,055) 
                            );
                                               
           dbms_output.put_line(' ');
                
           CLOSE c_Header;
     END;    

  dbms_output.put_line(( 'Start the Initiating Log - '    )||
                       ( ls_scriptname||'.sql'            )||
                       ( ' on '||ln_start_time            )||
                       (' with the following parameters; ')
                       );
        
  dbms_output.put_line(('   '                )||
                       (ls_database          )||' ~ '||
                       (ls_type              )||' ~ '||
                       (ls_category          )||' ~ '||
                       (ld_start_rep_period  )||' ~ '||
                       (ld_end_rep_period    )
                       );
        
  dbms_output.put_line(' ');     
            
           pr_mercerCntrlScript( ls_database
                               , ls_type
                               , ls_category
                               , ld_start_rep_period
                               , ld_end_rep_period
                               , li_totRecInsert
                               , li_totRecUpdate);

  ln_duration := ROUND((dbms_utility.get_time() -
                        ln_proc_time)/100, 3);
            
  dbms_output.put_line(('Ending the '           )||
                       (both_fn_WhoAmI          )||
                       (' at '                  )||
                       to_char(sysdate, 'Dy DD-Mon-YYYY
                                                HH24:MI:SS'))
                      );

   dbms_output.put_line(' ');

   dbms_output.put_line('************************************************************');
   dbms_output.put_line('*   Summary of the Entire Process:-');
   dbms_output.put_line('*      Started on                : ' || ln_start_time);
   dbms_output.put_line('*      Ended on                  : ' || to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS'));
   dbms_output.put_line('*      Total Processing Time     : ' || ln_duration||' seconds');
   dbms_output.put_line('*      Elapsed time (HH:MM:SS)   : ' || BOTH_fn_convSecsFormat(ln_duration));
   dbms_output.put_line('*      Total Records Inserted    : ' || li_totRecInsert);
   dbms_output.put_line('*      Total Records Updated     : ' || li_totRecUpdate);
   dbms_output.put_line('************************************************************');
       
   dbms_output.put_line(' ');

END pr_Log;

END MercSupPkg;
/


The cut down version of the control script is:
CREATE OR REPLACE 
PROCEDURE pr_mercerCntrlScript (  ps_database         IN VARCHAR2
                                , ps_type             IN VARCHAR2
                                , ps_category         IN VARCHAR2
                                , pd_start_rep_period IN DATE
                                , pd_end_rep_period   IN DATE
                                , oi_totRecInsert     OUT INTEGER
                                , oi_totRecUpdate   OUT INTEGER) IS
BEGIN
    
    ls_database          :=     ps_database;
    ls_type              :=     ps_type;
    ls_category          :=     ps_category;
    ld_start_rep_period  :=     pd_start_rep_period;
    ld_end_rep_period    :=     pd_end_rep_period;
    ln_proc_time         :=     dbms_utility.get_time();
    ln_start_time        :=     to_char(sysdate, 'Dy DD-Mon-YYYY
                                                    HH24:MI:SS');

    dbms_output.put_line(('   Commencing a '         )||
                         (MBUCKLEY.both_fn_WhoAmI    )||
                         (' at '                     )||
                         (ln_start_time              )
                         );
                           
    dbms_output.put_line(('   these parameters passed in: ' )||
                         (ps_database                  )||' ~ '||
                         (ps_type                      )||' ~ '||
                         (ps_category                  )||' ~ '||
                         (pd_start_rep_period          )||' ~ '||
                         (pd_end_rep_period            )||' ~ '||
                         (nvl(oi_totRecInsert,0)       )||' ~ '||
                         (nvl(oi_totRecUpdate,0)       )
                         );
     
    dbms_output.put_line(' ');
    
    IF ls_database =  'AURUGL' THEN  --Commence the appropriate 
                                      UGL Procedure, depending 
                                      on ls_type 
      
        CASE  ls_type                         
            
            WHEN 'New'        
                 THEN pr_uglMercerNewMember(  ls_type
                                            , ls_category
                                            , ld_start_rep_period
                                            , ld_end_rep_period
                                            , ls_filename
                                            , li_recCount);

                          li_totRecInsert  :=  li_recCount;

***Other code is essentially the same as the first CASE Statement

    oi_totRecInsert  :=  li_totRecInsert;
    oi_totRecUpdate  :=  li_totRecUpdate;

    
    dbms_output.put_line(('   Ending the '                   )||
                         (MBUCKLEY.both_fn_WhoAmI            )||
                         (' at '                             )||
                         (to_char(sysdate, 'Dy DD-Mon-YYYY
                                                 HH24:MI:SS'))
                         );

END pr_mercerCntrlScript;
/
Re: Stored Procedure Output Advice? [message #299706 is a reply to message #299687] Wed, 13 February 2008 00:12 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
you can use DBMS_OUTPUT.ENABLE(100000) within your procedure.as for your 3rd question - sorry i have never programmed VB.may be others can help you on this issue.


regards,
Re: Stored Procedure Output Advice? [message #299725 is a reply to message #299687] Wed, 13 February 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. dbms_output package as get_line/get_lines procedures to get the content of dbms_output buffer.
2. You can
3. see 1.
4. call dbms_output.enable to allocate the buffer

Regards
Michel
Re: Stored Procedure Output Advice? [message #299746 is a reply to message #299725] Wed, 13 February 2008 01:45 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SET SERVEROUTPUT ON is a SQL*Plus feature, not a DBMS_OUTPUT feature.

DBMS_OUTPUT works only WITHIN PL/SQL, if you want to get data out of it back to your client, then you have to write the interface yourself.

Here is an example that simulates SET SERVEROUTPUT ON - you could use it in any client that supported SQL SELECT statements that return bulk rows.

First create a type and function. Once-off effort.
The function converts the DBMS_OUTPUT array from a PL/SQL table into a nested table so that we can access it with SQL table functions.
CREATE TYPE clob_tab AS TABLE OF CLOB;
/

CREATE OR REPLACE FUNCTION dump_dbms_output RETURN clob_tab PIPELINED IS
        output DBMS_OUTPUT.CHARARR;
        lines  INTEGER := 2147483647;
BEGIN
        DBMS_OUTPUT.GET_LINES(output, lines);

        FOR i IN output.FIRST .. output.LAST LOOP
                PIPE ROW(output(i));
        END LOOP;
END;
/


Now we run it. Note how I switch off SERVEROUTPUT so that I can retrieve the output manually.
SET SERVEROUTPUT OFF

BEGIN
        DBMS_OUTPUT.ENABLE;
        DBMS_OUTPUT.PUT_LINE('Hi');
        DBMS_OUTPUT.PUT_LINE('There');
END;
/

select *
from table(dump_dbms_output())
/


Ross Leishman
Previous Topic: Dynamic sql
Next Topic: need the values of a variable inside a for loop
Goto Forum:
  


Current Time: Sat Dec 03 20:38:43 CST 2016

Total time taken to generate the page: 0.07376 seconds