Thanks for the question, Medico.

Asked: September 17, 2015 - 7:46 am UTC

Last updated: December 04, 2017 - 10:22 am UTC

Version: 11.2

Viewed 100K+ times! This question is

You Asked

Hello Tom

is there anyway to do a query in sql*plus, then have the result output to a file in *.csv or .*.txt format without invoking UTL_FILE, using only sql*plus command.
I'm not allowed to creat any procedure.
PS: this link " http://asktom.oracle.com/tkyte/flat/index.html" dose not work
Thanx

and Chris said...

That's what spool is for! This writes the display of sql*plus to a file you specify. To make it a csv, concatenate all your columns together with commas in between:

CHRIS>desc table_a
 Name               Null?    Type
 ------------------ -------- ---------------
 LAST_NAME                   VARCHAR2(1000)
 FIRST_NAME                  VARCHAR2(1000)
 UNIQUE_ID                   VARCHAR2(4000)

CHRIS>spool out.csv 
CHRIS>select first_name || ',' || last_name from table_a;
McDonald,Connor
Saxon,Chris

CHRIS>spool off

Gives me a file out.csv withe the following contents:

CHRIS>select first_name || ',' || last_name from table_a;
McDonald,Connor                                                                                                                                                 
Saxon,Chris                                                                                                                                                     

CHRIS>spool off

http://docs.oracle.com/database/121/SQPUG/ch_twelve043.htm#SQPUG126

Alternatively, check out the new SQLcl tool:

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

This enables you to specify different output formats (csv, html, ...). It will automatically render the output according to the format you specify. See

https://youtu.be/HApdy-o525A?t=7m10s

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Using Dynamic SQL

Rajeshwaran Jeyabal, September 17, 2015 - 11:22 am UTC

Here is something, which I wrote to an application recently using Dynamic SQL - Check if this help you.

rajesh@ORA11G> $type delim_data.sql
declare
        l_cursor int;
        l_sql long := replace('&1','"','''');
        l_col_cnt int;
        l_desc_tab dbms_sql.desc_tab;
        l_value varchar2(1000);
        l_status int;
        l_result varchar2(32767);
        l_delimiter varchar2(1) := ',';
begin
        l_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
        dbms_sql.describe_columns(l_cursor,l_col_cnt,l_desc_tab);
        for i in 1..l_col_cnt
        loop
                dbms_sql.define_column(l_cursor,i,l_value,1000);
        end loop;
        l_status := dbms_sql.execute(l_cursor);
        while ( dbms_sql.fetch_rows(l_cursor)>0)
        loop
                l_result := null;
                for i in 1..l_col_cnt
                loop
                        dbms_sql.column_value(l_cursor,i,l_value);
                        l_result := l_result ||l_value ||l_delimiter;
                end loop;
                        l_result := trim(l_delimiter from l_result);
                        dbms_output.put_line(l_result);
        end loop;
        dbms_sql.close_cursor(l_cursor);

exception
        when others then
                if dbms_sql.is_open(l_cursor) then
                        dbms_sql.close_cursor(l_cursor);
                end if;
                raise;
end;
/
rajesh@ORA11G>
rajesh@ORA11G> set verify off
rajesh@ORA11G>
rajesh@ORA11G> @delim_data 'select * from dept'
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

PL/SQL procedure successfully completed.

rajesh@ORA11G>
rajesh@ORA11G> @delim_data 'select * from emp where job="CLERK"'
7369,SMITH,CLERK,7902,17-DEC-1980 12:00:00 AM,800,,20
7876,ADAMS,CLERK,7788,23-MAY-1987 12:00:00 AM,1100,,20
7900,JAMES,CLERK,7698,03-DEC-1981 12:00:00 AM,950,,30
7934,MILLER,CLERK,7782,23-JAN-1982 12:00:00 AM,1300,,10

PL/SQL procedure successfully completed.

rajesh@ORA11G>
rajesh@ORA11G> 
rajesh@ORA11G> @delim_data 'select empno,ename,to_char(hiredate,"yyyymmddhhmiss") as hire_date from emp where job="CLERK"'
7369,SMITH,19801217120000
7876,ADAMS,19870523120000
7900,JAMES,19811203120000
7934,MILLER,19820123120000

PL/SQL procedure successfully completed.

rajesh@ORA11G>

usefule

Srikar, February 19, 2016 - 1:27 pm UTC

Hi,

I have the function which parse and create the csv file just like above code. But the challenge is I have more than 6 to 7 queries each of more than couple of 100 of lines, what is the best approach to pass these queries to the function.

I tried with bind variables but no luck.
Moreover, I would like to automate this in a script file.

Thank you in advance.

Regards,
Srikar

February 20, 2016 - 4:15 am UTC

This script takes any query and outputs the data as rows not columns.

set serverout on size 999999
declare
p_query varchar2(32767) := 'select * from scott.emp';

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    n number := 0;
  procedure p(msg varchar2) is
    l varchar2(4000) := msg;
  begin
    while length(l) > 0 loop
      dbms_output.put_line(substr(l,1,80));
      l := substr(l,81);
    end loop;
  end;
begin
    execute immediate
    'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            p( rpad( l_descTbl(i).col_name, 30 )
              || ': ' || 
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
        n := n + 1;
    end loop;
    if n = 0 then
      dbms_output.put_line( chr(10)||'No data found '||chr(10) );
    end if;
end;
/

You could easily adapt this to write out CSV data.

Cheers,
Connor

A reader, October 10, 2017 - 3:15 pm UTC

you can only use below:

set colsep |

October 11, 2017 - 9:43 am UTC

You can only use colsep in what scenario?

Single Quotes Query wont work

Abhay, November 30, 2017 - 1:57 pm UTC

Hi ,

If I pass any query with single quote it wont work. Is there any work around.

Example :--
p_query varchar2(32767) := 'select to_char(sysdate,'MM/DD/YYYY') from dual';

Regards.

November 30, 2017 - 2:04 pm UTC

You need to double up on single quotes inside strings:

p_query varchar2(32767) := 'select to_char(sysdate,''MM/DD/YYYY'') from dual';

Or prefix your string with q wrap it in start/end delimiters:

p_query varchar2(32767) := q'|select to_char(sysdate,'MM/DD/YYYY') from dual|';

You can find further examples at:

https://livesql.oracle.com/apex/livesql/file/content_CIREYU9EA54EOKQ7LAMZKRF6P.html

Column Size greater than 30

Anand, December 04, 2017 - 9:30 am UTC

Hi,

The column size has been defined here :--

  begin
    while length(l) > 0 loop
      dbms_output.put_line(substr(l,1,80));
      l := substr(l,81);
    end loop;
  end

Now even if I increase it to 200 the data doesnt get displayed in single line .

It appears as :--

desc                          : Rahdoahhdo ONE ocohahahd Sulajaldlj  Atkadkkadio
Management

Ideally it should be coming as

 desc                          : Rahdoahhdo ONE ocohahahd Sulajaldlj  Atkadkkadio Management

Anyway to get the width increased.

Regards.

December 04, 2017 - 10:22 am UTC

Where? In SQL*Plus? And what exactly did you increase?

If you are in SQL*Plus, your linesize may be too small:

SQL> sho lines
linesize 120
SQL> set lines 200

Column Size greater than 30

Anand, December 04, 2017 - 10:38 am UTC

Somehow unable to edit or reply to previous review hence writing a new one .

I increased the below parameter :--

  begin
    while length(l) > 0 loop
      dbms_output.put_line(substr(l,1,200));
      l := substr(l,201);
    end loop;
  end;

Also, added set lines 200 at the top of query posted by "Connor "

I am trying to call SQLPLUS from Unix script .

# Connect to Oracle DB
export LD_LIBRARY_PATH=/root/oracle/instantclient_12_2
/root/oracle/instantclient_12_2/sqlplus test/test123@11.12.13.14/test.localhost.com <<EOF
# Execute Oracle Query
@oracle.sql
exit;

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Using Dynamic SQL

usefule

Single Quotes Query wont work

Column Size greater than 30

Column Size greater than 30

More to Explore

PL/SQL demos

PL/SQL docs

SQLPLUS query output to *.csv or *.txt format

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Using Dynamic SQL

usefule

Single Quotes Query wont work

Column Size greater than 30

Column Size greater than 30

More to Explore

PL/SQL demos

PL/SQL docs

SQLPLUS query output to .csv or .txt format