If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Noob Messaging Question

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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 ...
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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 ...
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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 ...
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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 ...
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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 ...
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On