CODEWITHSUNDEEP
sqloracle-databasebatch-file
user1838000
user1838000

Reputation: 275

Windows Batch File calling Oracle PL/SQL block- Format Issue

I have a DB.bat file which holds PL/SQL block as below. The problem here i'm facing is the extract exported to csv has text format. I need to have it in a tabular structure. Please guide me how to achieve the tabular format in csv after spool.

c:\desktop\DB.bat:

call sqlplus ID/pwd@db @c:\desktop\code.sql

c:\desktop\code.sql:

set head off
set feed off
set pagesize 0
spool c:\desktop\DB.csv
--PL/SQL Block ---
spool off
set head on
set feed 6
quit

Upvotes: 1

Views: 215

Answers (2)

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

tabular as in:

Fixed column width?

lpad(col1,*100) || lpad(col2,*100) || lpad(col3,*100) || etc..

where *100 is more than the maximum text size per that column

Delimited?

col1 || chr(9) || col2 || chr(9) || col3 || etc...

Where chr(9) is Tab and can be anything. || '|' || for pipe, || ',' || for comma, etc..

Tabular formats in text files, csv or "comma separated files" come in all kinds of flavor, what are you looking for?

Regards Olafur

ps, tab delimited example

set serveroutput on
begin
  for i in (select tablespace_name || chr(9) || table_name || chr(9) || to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as line from user_tables order by table_name) loop
    dbms_output.put_line(i.line);
  end loop;
end;
set serveroutput off

Upvotes: 1

kfinity
kfinity

Reputation: 9091

It's unclear what you're asking, but... In order to output info from a PL/SQL block to a spool file, you need two things: set serveroutput on in your sql*plus script, and sending your info to dbms_output.put_line in your PL/SQL block. Here's an example:

set head off
set feed off
set pagesize 0
set serveroutput on
spool c:\desktop\DB.csv
begin
  dbms_output.put_line('test1,test2,test3');
end;
/
spool off

If by "PL/SQL block" you just mean a plain SQL query, then Olafur's answer is probably what you're looking for.

Upvotes: 1

Related Questions