| dbms_output doesn't work in shell script [message #98047] |
Fri, 02 January 2004 11:00  |
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  |
Frank Naude
Messages: 4581
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
