DBMS_OUTPUT

[David_T MO]

I'm a long time SQL user but an Oracle newbie and have inherited an
Oracle database. I'm trying to get DBMS_OUTPUT going but am hitting a
brick wall. I can describe the package and execute the 'set
serveroutput on' command, but anytime I attempt to use any procedure
within the package I get an 'unknown command' error (see below).
Doesn't matter what user_id I use. I've rerun the dbmsotpt.sql as SYS
successfully but no joy on using the procedures.

Any suggestions on how to get DBMS_OUTPUT working?

---

limsd>describe dbms_output;
PROCEDURE DISABLE
PROCEDURE ENABLE
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
BUFFER_SIZE NUMBER(38) IN DEFAULT
PROCEDURE GET_LINE
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
LINE VARCHAR2 OUT
STATUS NUMBER(38) OUT
PROCEDURE GET_LINES
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
LINES TABLE OF VARCHAR2(255) OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
A VARCHAR2 IN
PROCEDURE PUT
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
A NUMBER IN
PROCEDURE PUT_LINE
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
A VARCHAR2 IN
PROCEDURE PUT_LINE
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
A NUMBER IN

limsd>SET SERVEROUTPUT ON;
limsd>DBMS_OUTPUT.ENABLE;
SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line
ignored.

[FredBear]

"David_T MO" <david.thomas@cityutilities.net> a écrit dans le message de
news:4f6c512c.0409080734.7a07d909@posting.google.com... I'm a long time SQL user but an Oracle newbie and have inherited an Oracle database. I'm trying to get DBMS_OUTPUT going but am hitting a brick wall. I can describe the package and execute the 'set serveroutput on' command, but anytime I attempt to use any procedure within the package I get an 'unknown command' error (see below). Doesn't matter what user_id I use. I've rerun the dbmsotpt.sql as SYS successfully but no joy on using the procedures. Any suggestions on how to get DBMS_OUTPUT working? --- limsd>describe dbms_output; PROCEDURE DISABLE PROCEDURE ENABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- BUFFER_SIZE NUMBER(38) IN DEFAULT PROCEDURE GET_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LINE VARCHAR2 OUT STATUS NUMBER(38) OUT PROCEDURE GET_LINES Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LINES TABLE OF VARCHAR2(255) OUT NUMLINES NUMBER(38) IN/OUT PROCEDURE NEW_LINE PROCEDURE PUT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A VARCHAR2 IN PROCEDURE PUT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A NUMBER IN PROCEDURE PUT_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A VARCHAR2 IN PROCEDURE PUT_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A NUMBER IN limsd>SET SERVEROUTPUT ON; limsd>DBMS_OUTPUT.ENABLE; SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.

The problem is not with the package.
The problem is how to execute a package or a PL/SQL statement.
Maybe looking at SQL*Plus Manual.
For instance:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1009731

--
Regards
Michel Cadot

[Mark C. Stock]

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message
news:413f2f66$0$7594$636a15ce@news.free.fr...
|
| "David_T MO" <david.thomas@cityutilities.net> a écrit dans le message de
| news:4f6c512c.0409080734.7a07d909@posting.google.com...
....
| > limsd>SET SERVEROUTPUT ON;
| > limsd>DBMS_OUTPUT.ENABLE;
| > SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line
| > ignored.
|
| The problem is not with the package.
| The problem is how to execute a package or a PL/SQL statement.
| Maybe looking at SQL*Plus Manual.
| For instance:
|
|
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1009731
|
| --
| Regards
| Michel Cadot
|
|
|

as the link indicates, use the SQL*Plus EXECUTE command (only works in
SQL*Plus) -- or use an anonymous block. simply wrap your PL/SQL fragment in
BEGIN ... END;

download the PL/SQL and SQL manuals from the referenced web site and google
'pl/sql tutorial' for more info

++ mcs

[Thomas Jensen]

David_T MO wrote: I'm a long time SQL user but an Oracle newbie and have inherited an Oracle database. I'm trying to get DBMS_OUTPUT going but am hitting a brick wall. I can describe the package and execute the 'set serveroutput on' command, but anytime I attempt to use any procedure within the package I get an 'unknown command' error (see below). Doesn't matter what user_id I use. I've rerun the dbmsotpt.sql as SYS successfully but no joy on using the procedures. Any suggestions on how to get DBMS_OUTPUT working? --- limsd>describe dbms_output; PROCEDURE DISABLE PROCEDURE ENABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- BUFFER_SIZE NUMBER(38) IN DEFAULT PROCEDURE GET_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LINE VARCHAR2 OUT STATUS NUMBER(38) OUT PROCEDURE GET_LINES Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LINES TABLE OF VARCHAR2(255) OUT NUMLINES NUMBER(38) IN/OUT PROCEDURE NEW_LINE PROCEDURE PUT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A VARCHAR2 IN PROCEDURE PUT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A NUMBER IN PROCEDURE PUT_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A VARCHAR2 IN PROCEDURE PUT_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A NUMBER IN limsd>SET SERVEROUTPUT ON; limsd>DBMS_OUTPUT.ENABLE; SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.


Hi,

using sql*plus you have use one of the following syntaxes:

SQL> execute dbms_output.put_line('Test executing statement');

or

SQL> begin
2 dbms_output.put_line('Test using PL/SQL');
3 end;
4 /


Best regards
Thomas Jensen

[Daniel Morgan]

David_T MO wrote: I'm a long time SQL user but an Oracle newbie and have inherited an Oracle database. I'm trying to get DBMS_OUTPUT going but am hitting a brick wall. I can describe the package and execute the 'set serveroutput on' command, but anytime I attempt to use any procedure within the package I get an 'unknown command' error (see below). Doesn't matter what user_id I use. I've rerun the dbmsotpt.sql as SYS successfully but no joy on using the procedures. Any suggestions on how to get DBMS_OUTPUT working? --- limsd>describe dbms_output; PROCEDURE DISABLE PROCEDURE ENABLE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- BUFFER_SIZE NUMBER(38) IN DEFAULT PROCEDURE GET_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LINE VARCHAR2 OUT STATUS NUMBER(38) OUT PROCEDURE GET_LINES Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LINES TABLE OF VARCHAR2(255) OUT NUMLINES NUMBER(38) IN/OUT PROCEDURE NEW_LINE PROCEDURE PUT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A VARCHAR2 IN PROCEDURE PUT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A NUMBER IN PROCEDURE PUT_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A VARCHAR2 IN PROCEDURE PUT_LINE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- A NUMBER IN limsd>SET SERVEROUTPUT ON; limsd>DBMS_OUTPUT.ENABLE; SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.

SQL> set serveroutput on
SQL> exec dbms_output.enable;

For a full demo go to http://www.psoug.org
Click on Morgan's Library
Click on DBMS_OUTPUT

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[David_T MO]

Thanks guys, that did the trick.

I'll do some more digging for tutorials, etc. from the sites given,
but I have another quick question. Why, when I execute this test SQL
script (below), am I getting the message 'Input truncated to 1
characters'?

---

DECLARE

BEGIN

if abs( ( sysdate - 1 ) - sysdate ) > 6 then

dbms_output.put_line('Bad Date');

else

dbms_output.put_line('Good Date');

end if;


END;
/

---

limsd>@u_test_date.sql;
Input truncated to 1 characters
Good Date

PL/SQL procedure successfully completed.

---

[FredBear]

"David_T MO" <david.thomas@cityutilities.net> a écrit dans le message de
news:4f6c512c.0409090630.1f1f57f0@posting.google.com... Thanks guys, that did the trick. I'll do some more digging for tutorials, etc. from the sites given, but I have another quick question. Why, when I execute this test SQL script (below), am I getting the message 'Input truncated to 1 characters'? --- DECLARE BEGIN if abs( ( sysdate - 1 ) - sysdate ) > 6 then dbms_output.put_line('Bad Date'); else dbms_output.put_line('Good Date'); end if; END; / --- limsd>@u_test_date.sql; Input truncated to 1 characters Good Date PL/SQL procedure successfully completed. ---

Add a new line at the end of the script.
The message comes from that the last line is not ended (does not end with chr(10)).

--
Regards
Michel Cadot

[David_T MO]

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:<41408f08$0$15562$636a15ce@news.free.fr>... "David_T MO" <david.thomas@cityutilities.net> a écrit dans le message de news:4f6c512c.0409090630.1f1f57f0@posting.google.com... Thanks guys, that did the trick. I'll do some more digging for tutorials, etc. from the sites given, but I have another quick question. Why, when I execute this test SQL script (below), am I getting the message 'Input truncated to 1 characters'? Add a new line at the end of the script. The message comes from that the last line is not ended (does not end with chr(10)).

Thanks!