Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Posts
    11

    Noob Messaging Question

    how can i print debug statements back to the calling client or to the screen via PL/SQL?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    check out error handling or
    dbms_output.put_line
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2005
    Posts
    11
    Quote Originally Posted by The_Duck
    dbms_output.put_line
    i put in..

    Code:
    IF LENGTH(IN_PROGRAM) > 0 THEN
    	 	dbms_output.PUT_LINE('IN_PROGRAM');
     	    --qry_str := qry_str || ' AND OT.ADMINISTRATION = IN_PROGRAM';
    	 END IF;
    but i don't see anything printing the IN_PROGRAM value. am i missing something? where would this value get printed?.. in my logs?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    set serveroutput on

    also, if your variable is already a string, then don't put your variable in quotes.

    PHP Code:
      1  declare
      
    2  in_program varchar2(20);
      
    3  begin
      4
      5  in_program 
    := 'correct test';
      
    6
      7  dbms_output
    .put_line('in_program');
      
    8  dbms_output.put_line(in_program);
      
    9end;
    SQL > /
    in_program
    correct test

    PL
    /SQL procedure successfully completed
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    You can also make it come back as an error with RAISE_APPLICATION_ERROR.

    Code:
    raise_application_error(-20000, 'IN_PROGRAM = '||in_program);
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  6. #6
    Join Date
    Feb 2005
    Posts
    11
    sorry i should have displayed all of my code...

    Code:
    PROCEDURE TEST(IN_PROGRAM IN VARCHAR2, PR_CURSOR OUT PACKAGE_REPORTS_CURSOR)
    AS
        qry_str VARCHAR2(2000);
    BEGIN
    	 
    	 qry_str := 'SELECT OT.SALES_ORDER_NUM, ST.TRACKING_NUM, SS.SHIPMENT_STATUS_DETAIL, ST.EXCEPTION_CODE,
    	 		 			ST.REFERENCE_NUM, ST.EXPECTED_SHIP_DATE, ST.ACTUAL_SHIP_DATE,
    						ST.EXPECTED_DELIV_DATE, ST.REQ_DELIV_DATE, ST.ACTUAL_DELIV_DATE
    				 FROM   SHIPMENT_TRACKING ST, ORDER_TABLE OT, SHIPMENT_STATUS_LOOKUP SS
    				 WHERE	OT.SALES_ORDER_NUM = ST.SALES_ORDER_NUM
    				 AND	SS.SHIPMENT_STATUS_CODE = ST.SHIP_TYPE';
    				 
     	 IF LENGTH(IN_PROGRAM) > 0 THEN
    	 	dbms_output.PUT_LINE(IN_PROGRAM);
     	    --qry_str := qry_str || ' AND OT.ADMINISTRATION = IN_PROGRAM';
    	 END IF;
    	 OPEN PR_CURSOR FOR qry_str;
    END TEST;
    but i'm still not seeing where the output is sent to

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    are you setting serveroutput on in sqlplus before running the procedure??

    set serveroutput on
    exec test ('x');
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Feb 2005
    Posts
    11
    Quote Originally Posted by The_Duck
    are you setting serveroutput on in sqlplus before running the procedure??

    set serveroutput on
    exec test ('x');
    sorry i'm sucha noob.. but where in the copy and pasted code above would i put this statement?

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    you don't put it in the statement.
    it is a setting in sqlplus.

    here is a shortcut:
    edit $ORACLE_HOME/sqlplus/admin/glogin.sql
    or for windows:
    C:\oracle\ora92\sqlplus\admin\glogin.sql

    add this line:
    set serveroutput on


    open up sqlplus and run your procedure by issueing:
    execute TEST('X');
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Feb 2005
    Posts
    11
    Quote Originally Posted by The_Duck
    you don't put it in the statement.
    it is a setting in sqlplus.

    here is a shortcut:
    edit $ORACLE_HOME/sqlplus/admin/glogin.sql
    or for windows:
    C:\oracle\ora92\sqlplus\admin\glogin.sql

    add this line:
    set serveroutput on


    open up sqlplus and run your procedure by issueing:
    execute TEST('X');
    only problem is, i want to always see output in my logs, not just when debugging w/ SQL*Plus. is there a way to always have server output on, even when not explicityly using sql plus?

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    please supply a more concrete example of what you are referring to.
    how are you calling the procedure if not through sqlplus?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    Perhaps we should discuss how dbms_output works. According to documentation, the package uses a memory buffer area (was limited to 1Mb in 8i). Each put_line statement simply adds a one more line of string to the buffer.

    When "exec test ('x');" completes, control returns to the client. The client program now calls some other procedure in the dbms_output package, and reads the contents of the buffer, line by line. This is what SqlPlus does behind your back, when you do a Set ServerOutput ON. The package has also a method to clear the buffer, so it is empty when the next "exec" is callled.

    If you have written your own client, then you can do something similar. For example, my Dot Net client writes each line it obtains from dbms_output into the database, and also displays it on stdout. You can also write the same in Java.

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •