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

问题:

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:desktopDB.bat:

call sqlplus ID/pwd@db @c:desktopcode.sql

c:desktopcode.sql:

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

回答1:

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


回答2:

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
  • 发表于 2019-03-19 09:55
  • 阅读 ( 86 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除