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


 
Thread Tools Search this Thread
Top Forums Programming How to Format database output (DBMS_OUTPUT.PUT_LINE) in unix?
# 1  
Old 03-22-2011
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:
Code:
#!  /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  serveroutput on size 1000000
 set  echo off
 set  feedback off
 set  heading off
 set  pages 0
 set  pagesize 1000
 set  linesize 300
 set  verify off
 
 declare
   v_nm  implmnt_dim.implmnt_nm%type ;
   v_cc  implmnt_dim.implmnt_id%type ;
    c_ref_nm sys_refcursor ;
 begin
   OPEN  c_ref_nm
    for
      select implmnt_nm, implmnt_id from implmnt_dim ;
      dbms_output.put_line(rpad(substr('Country Name',1,20),20)||'Country Code')  ;
      dbms_output.put_line('|') ;
      dbms_output.put_line(rpad(substr('------------',1,20),20)||'------------')  ;
      dbms_output.put_line('|') ;
    loop
      fetch c_ref_nm into v_nm, v_cc ;
      exit when c_ref_nm%notfound ;
      dbms_output.put_line('|') ;
      dbms_output.put_line(rpad(substr(v_nm,1,20),20)||v_cc)  ;
   end  loop ;
    end;
 /
 exit;
 ENDOFSQL`
 
 echo  $Return_op|tr '|' '\n'
 
 echo  'End of data extraction..'
 ~
 "test1.ksh" 46 lines, 1015  characters

Now the Output is like:
Code:
ksh:0$  ksh test1.ksh
 Country  Name Country Code
  ------------ ------------
 
  Mexico  130
  Chicago 85
  Argentina 135
  Paraguay 136
  Chile  132
  Colombia 133
  Venezuela 134

But I want the output like what I'm getting in database when I execute the PL/SQL block only:
Code:
Country Name        Country Code

---------------------        ------------
Mexico                  130
Chicago                 85
Argentina               135
Paraguay               136
Chile                     132
Colombia               133
Venezuela             134

Please help...!

Thanks,
Saptarshi
# 2  
Old 03-28-2011
Ok, I done it with Spool.Smilie Getting the desired output. Smilie
Code:
varDate='date +...'
export spool_file=file_dir/spool_file_${varDate}.txt
db connection started
spool $spool_file
-- your db code here
/
spool off
exit;

Thanks,
Saptarshi
Login or Register to Ask a Question

Previous Thread | Next Thread

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

UNIX command output format in email is not same as on the system

Hi I have script to collect file system usage statistics from few remote unix hosts and email . On the UNIX system the column spacing is fine but the email output is not aligned properly. Any tips to fix this ? (1 Reply)
Discussion started by: new2prog
1 Replies

3. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

4. Shell Programming and Scripting

UNIX shell script to format output report

i am new to unix shell scripting, could someone please help me. i was asked to develop a unix script and requirement of the script is as follows: 1. In source directory, if any new files are being dropped by external system then an email should be sent out with a message saying "files are... (3 Replies)
Discussion started by: crefi1545
3 Replies

5. Shell Programming and Scripting

UNIX command output in csv format

I'm just wondering is there any way to capture the output of a unix command in a csv format. df -h gives the result of filesystem,free space,Used space, use %,mounted on. Is there a way to capture the command output and format it as comma sparated or fixed length file. (3 Replies)
Discussion started by: anita81
3 Replies

6. UNIX for Dummies Questions & Answers

UNIX command output format

how can I get the df -h command output into excel format or csv file. df -k | tr -s " " | sed 's/ /, /g' | sed '1 s/, / /g' | column -t df -h | column -t I have tried as above but the format is not right. I'm not able to load the format into a excel or a table. ... (2 Replies)
Discussion started by: anita81
2 Replies

7. 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

8. Shell Programming and Scripting

Converting windows format file to unix format using script

Hi, I am having couple of files which i used to copy from windows to Linux, so now in case of text files (CTRL^M) appears at end of line. I know i can convert this windows format file to unix format file by running dos2unix. My requirement here is that i want to do it automatically using a... (5 Replies)
Discussion started by: sarbjit
5 Replies

9. Shell Programming and Scripting

How to format database output

I have an output from a database query on the unix standard output as under: abc dsagh dsflsfds dsfsdfsdfsd sdf sdfsd sdfsd sdfsdf sdfsdfs sdfsdf sdfsdf sdfsdf sdfsdf sdfsdf sdfsdff dsfsdf sdfdf sdf (8 Replies)
Discussion started by: abcabc1103
8 Replies

10. Shell Programming and Scripting

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... (6 Replies)
Discussion started by: stuck1
6 Replies
Login or Register to Ask a Question

Featured Tech Videos