Visit Our UNIX and Linux User Community


sqlplus and dbms_output.put_line in shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sqlplus and dbms_output.put_line in shell script
# 1  
Old 12-19-2007
sqlplus and dbms_output.put_line in shell script

Hi,

I have created a pl/sql block utilises dbms_output.put_line. This script works fine when I call from sqlplus ie sqlplus @./scriptname but when I embed it within my shell script I get no output to screen so I end up with an empty spool file. I know it's executing successfully when called from the shell as I have feedback on and i get the "PL/SQL procedure successfully completed." message.My question is it possible to use dbms_output.put_line within a shell script and to spool the output to file?

Thanks,
S1
# 2  
Old 12-19-2007
Please post your script
# 3  
Old 12-20-2007
Part of script thats causing me problems:

Code:
sqlplus -s /nolog <<EOF
set trimspool on
set line 500
set escape on
connect /as sysdba

spool hot_backup.cmd

declare
  copy_cmnd constant varchar2(30) := 'cp';
  copy_dest constant varchar2(30) := '/u/homes/oradba/backup';

  dbname  varchar2(30);
  logmode varchar2(30);
begin
  select name, log_mode
  into   dbname, logmode
  from   sys.v_\\\$database;

  if logmode <> 'ARCHIVELOG' then
     raise_application_error(-20000,
                     'ERROR: Database must be in ARCHIVELOG mode!!!');
     return;
  end if;

  dbms_output.put_line('spool backup.'||dbname||'.'||
                       to_char(sysdate, 'ddMonyy')||'.log');

  -- Loop through tablespaces
  for c1 in (select tablespace_name ts
             from   sys.dba_tablespaces where tablespace_name='ALRX')
  loop
    dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
    -- Loop through tablespaces' data files
    for c2 in (select file_name fil
               from   sys.dba_data_files
               where  tablespace_name = c1.ts)
    loop
      dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
    end loop;

    dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
  end loop;

  -- Backup controlfile and switch logfiles
  dbms_output.put_line('alter database backup controlfile to trace;');
  dbms_output.put_line('alter database backup controlfile to '||''''||
                       copy_dest||'control.'||dbname||'.'||
                       to_char(sysdate,'DDMonYYHH24MI')||''''||';');
  dbms_output.put_line('alter system switch logfile;');
  dbms_output.put_line('spool off');
end;
/

spool off

set head on
set feed on
set serveroutput off



exit;
EOF

# 4  
Old 12-20-2007
set serveroutput on

set serverout on is missing in ur script
# 5  
Old 12-20-2007
UTL_FILE is better for this task, ex:
Code:
sqlplus -s / <<-EOF
set echo off 
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
DECLARE
fichero utl_file.file_type;
BEGIN
fichero:=utl_file.fopen('/tmp','hot_backup.cmd','W');
utl_file.put_line(fichero,'LINE 1');
utl_file.put_line(fichero,'LINE 2');
utl_file.fclose(fichero);
END;
/
EOF

# 6  
Old 12-20-2007
Thanks for your replys:

Serveroutput on only gives me the following "PL/SQL procedure successfully completed." and nothing else.

Klashxx: your example works fine, but we dont use utl_file_dir here and this is going to be a generic script that backs up approx 10 instances and I don't want to have to change my init.ora files as this will generate more paper work than its worth. Is there any way to get dbms_output to print to the screen when run from with the shell?
# 7  
Old 12-20-2007
small code

That's very strange

Try the following code, it worked for me:
Code:
sqlplus -s scott/tiger <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
spool aaa.txt
BEGIN
DBMS_OUTPUT.PUT_LINE('xyz');
END;
/
spool off
EOF


Previous Thread | Next Thread
Test Your Knowledge in Computers #559
Difficulty: Easy
A runtime error is an error that occurs while a program is being executed.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

DBMS_OUTPUT.PUT_LINE doesn't print values in shell script

Hello, I'm trying to print the value of my cursor in the dbms_output.put_line in shell script, but it only shows "PL/SQL procedure successfully completed." nothing else. I have set serveroutput on, Below is my script : Any advise would be really helpful. sqlplus -s $ORACLE_LOGON <<EOF >>... (2 Replies)
Discussion started by: mail.chiranjit
2 Replies

2. Shell Programming and Scripting

Sqlplus in shell script

Hi All, Please let me know what i am missing in the following code (part of my script) Schemas=(AWQM WFCONTROLLER PROVCO PRISM) for s in "${Schemas}" do sch="${s}_$tol" if || ;then echo "This is AD or TD region" sqlplus -s $sch/$tpwd@$ttns <<EOF... (7 Replies)
Discussion started by: pvmanikandan
7 Replies

3. Shell Programming and Scripting

[Solved] How to display only output of DBMS_OUTPUT.PUT_LINE , rest should be neglected

Hi All, I Have written a script through that i am calling sql file Sqlfile.sql set time on set timing on set echo on set head off set scan on set feedback on set serveroutput on set linesize 1000 DECLARE v_acc_no NUMBER(10); v_product_no NUMBER(10); BEGIN... (3 Replies)
Discussion started by: sujit_kashyap
3 Replies

4. Shell Programming and Scripting

sqlplus in shell script

Hi When I use sqlplus in shell script, I get sqlplus: command not found. ORACLE_HOME is not set. How to set ORACLE_HOME in unix? Thanks (3 Replies)
Discussion started by: vinoth_kumar
3 Replies

5. Programming

How to Format database output (DBMS_OUTPUT.PUT_LINE) in unix?

Dear All, As I'm new here, please forgive me if any rule violation occurred. I have a script like this: #! /bin/ksh # Author : Saptarshi # Date : 18-Mar-2011 # Version : 1.0 Return_op=`sqlplus -s <<ENDOFSQL db_user/db_pass@db_nm WHENEVER SQLERROR EXIT 1 set ... (1 Reply)
Discussion started by: saps19
1 Replies

6. Shell Programming and Scripting

Want to learn/use SQLPLUS in shell script

Hi All, How i will use sqlplus in shell script? Can any one provide sample code which explain following: 1. Connect to oracle DB 2. Exceute select * from tablename 3. Release connection to the DB 4. Append output in file everytime when query executes. Thanks in advance (1 Reply)
Discussion started by: poweroflinux
1 Replies

7. Shell Programming and Scripting

SQLPLUS within shell script

Hi I want to connect to the Oracle database using a username/password and get back the query result(a numeric value) in a variable, which I can then compare using a conditional. Can anybody help me with this. Thanks Gaurav (4 Replies)
Discussion started by: gaurav_1711
4 Replies

8. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

9. Shell Programming and Scripting

help me in sending parameters from sqlplus script to unix shell script

Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files.. Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this? Please tell me... (2 Replies)
Discussion started by: Hara
2 Replies

10. UNIX for Dummies Questions & Answers

Shell Script And SQLPLUS

i'm having real problems retrieving the returncode of my sqlplus-call. I found a lot of informations on the net, but havn't been able to get it running so far, so now i ask for some help ;) I do start the sqlplus out of my shell script with the parameters stored in the proc_clips.sql, which is... (6 Replies)
Discussion started by: maco_home
6 Replies

Featured Tech Videos