Home » Infrastructure » Unix » dbms_output doesn't work in shell script
dbms_output doesn't work in shell script [message #98047] Fri, 02 January 2004 11:00 Go to next message
Mathew Stein
Messages: 1
Registered: January 2004
Junior Member
I am trying to use dbms_output inside a block of anonymous PL/SQL in a shell script but there is no output. I need DBMS_OUTPUT to print to stdout.

Example:
${ORACLE_HOME}/bin/sqlplus /nolog << EOF
connect / as sysdba
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
DBMS_OUTPUT.PUT_LINE ('Hello');
END;
/
EOF

Anybody know why and how to fix it? Please note that I do not want to spool to a file.
Re: dbms_output doesn't work in shell script [message #98049 is a reply to message #98047] Sun, 04 January 2004 22:05 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

I've executed your code and received the following output:

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 5 10:08:50 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> Connected.
SQL> SQL>   2    3    4  <b>Hello</b>

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production


Best regards.

Frank
Re: dbms_output doesn't work in shell script [message #98243 is a reply to message #98047] Mon, 24 May 2004 20:33 Go to previous messageGo to next message
Simon Tankersley
Messages: 2
Registered: May 2004
Junior Member
Hi,

I also had the same problem with some of my sql. I executed your sql and it worked. Comparing the differences I discoverd that mine has tabs. That is mine was

${ORACLE_HOME}/bin/sqlplus /nolog << EOF
connect / as sysdba
tab SET SERVEROUTPUT ON SIZE 1000000
tab BEGIN
tab DBMS_OUTPUT.PUT_LINE ('Hello');
tab END;
tab /
EOF

Not sure if this helps you but if your sql has tabs then it may not run.

Cheers

Simon
Re: dbms_output doesn't work in shell script [message #98458 is a reply to message #98243] Thu, 04 November 2004 08:42 Go to previous message
Simon Tankersley
Messages: 2
Registered: May 2004
Junior Member
I now know more about the situation. The above will execute providing that there is no tab or any white space in front of the /. If you do want to put a tab in front of the / to layout your code then you should do the following

${ORACLE_HOME}/bin/sqlplus /nolog <<- EOF
connect / as sysdba
tab SET SERVEROUTPUT ON SIZE 1000000
tab BEGIN
tab DBMS_OUTPUT.PUT_LINE ('Hello');
tab END;
tab /
EOF

The only difference here is the '-' in front of the 'EOF'. This directs the shell to remove all leading tabs before passing the script to sqlplus. Please note however that some editors may replace your tabs with spaces and the '-' only removes tabs not spaces.

cheers,

Simon
Previous Topic: List only directories
Next Topic: Oracle 8i and Sco Unixware 7.1.1
Goto Forum:
  


Current Time: Fri Mar 29 08:47:56 CDT 2024