Home » SQL & PL/SQL » Client Tools » spooling data (10.2.0.4, Windows 2003)
spooling data [message #541674] Wed, 01 February 2012 13:29 Go to next message
sant_new
Messages: 164
Registered: June 2008
Senior Member
Hi friends,

I would like to use the Spool command to export data for other purposes within the application. We would like to use tab demiliter to seperate the fields but the cliient wants to know if the text datatype fields can be wrapped in double quotes along with the tab demiliter..
SQL>create table test (id number(2), first_name varchar2(15), last_name varchar2(15),var_no number(4),
type varchar2(1),type_no number(12));

Table created.

SQL> insert into test values(1,'mary','ross',132,'S',12);

1 row created.

SQL> insert into test values(3,'Sue','Bill',432,'S',12);

1 row created.

I tried the below spool command to use tab demilited for all the fields but not sure how to wrap double quotes for only the text fields and also would to have the column names in the 1st row but don't seem to get the full column name in the csv file.

set echo off
set feedback off
set linesize 1000
set pagesize 4000
set trim on 
set headsep off
set colsep '' (used tab between the quote)

spool test.csv
select id,first_name,last_name,var_no,type,type_no from test;
spool off


Any help would be appreciated.. Thanks a lot

[Updated on: Wed, 01 February 2012 13:33] by Moderator

Report message to a moderator

Re: spooling data [message #541675 is a reply to message #541674] Wed, 01 February 2012 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not sure I understand what you want.
Maybe something like that:
SQL> host type x.sql
set colsep '    '
set recsep off
set head on
set headsep off
set trimspool on
set feedback off
set linesize 1000
col type format a4
spool x
select id, '"'||first_name||'"' first_name, '"'||last_name||'"' last_name, var_no,
       '"'||type||'"' type, type_no
from test
/
spool off

SQL> @x
        ID      FIRST_NAME              LAST_NAME                   VAR_NO      TYPE       TYPE_NO
----------      -----------------       -----------------       ----------      ----    ----------
         1      "mary"                  "ross"                         132      "S"             12
         3      "Sue"                   "Bill"                         432      "S"             12
SQL> host type x.lst
        ID      FIRST_NAME              LAST_NAME                   VAR_NO      TYPE       TYPE_NO
----------      -----------------       -----------------       ----------      ----    ----------
         1      "mary"                  "ross"                         132      "S"             12
         3      "Sue"                   "Bill"                         432      "S"             12

Regards
Michel
Re: spooling data [message #541676 is a reply to message #541675] Wed, 01 February 2012 14:42 Go to previous messageGo to next message
sant_new
Messages: 164
Registered: June 2008
Senior Member
Thanks a lot Michel. That was exactly what I was looking for. But when I try the same script for different tables with more number of records and more fields in the table, the column names in the 1st row is not displayed in full..
Is it possible to display the column names in full only in the 1st row?And is it possible to remove the dotted lines in the 2nd row?

Also, will there be any limitation in extracting big tables (about 2 million and 100+ columns) to CSV file?

Thanks a lot for all your help.

[Updated on: Wed, 01 February 2012 14:54]

Report message to a moderator

Re: spooling data [message #541678 is a reply to message #541676] Wed, 01 February 2012 14:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7988
Registered: November 2002
Location: California, USA
Senior Member
sant_new wrote on Wed, 01 February 2012 12:42


And is it possible to remove the dotted lines in the 2nd row?


set underline off
Re: spooling data [message #541679 is a reply to message #541676] Wed, 01 February 2012 14:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7988
Registered: November 2002
Location: California, USA
Senior Member
sant_new wrote on Wed, 01 February 2012 12:42


Is it possible to display the column names in full only in the 1st row?


For each column_name with a name longer than the width of the data, use:

column column_name format a9

where 9 is the length of the column name.

Re: spooling data [message #541680 is a reply to message #541679] Wed, 01 February 2012 15:15 Go to previous messageGo to next message
sant_new
Messages: 164
Registered: June 2008
Senior Member
Thanks so much
Re: spooling data [message #541687 is a reply to message #541680] Wed, 01 February 2012 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 22800
Registered: January 2009
Senior Member
while immediate problem has been solved, a different solution is possible.
SQL> select dbms_assert.enquote_literal(owner) owner, dbms_assert.enquote_name(table_name) table_name
  2  from all_tables
  3  where owner = USER
  4* order by 1;
 
OWNER                          TABLE_NAME
------------------------------ ------------------------------
'UWCLASS'                      "AIRPLANES"
'UWCLASS'                      "MV_FGA"
'UWCLASS'                      "SERV_INST"
'UWCLASS'                      "POSTAL_CODE"
'UWCLASS'                      "DBA_OBJECTS2"
'UWCLASS'                      "CALTAB"
'UWCLASS'                      "T"
'UWCLASS'                      "DEPARTMENT"
'UWCLASS'                      "NO_COMPRESS"
'UWCLASS'                      "OLTP_COMPRESS"
'UWCLASS'                      "OLTP_COMPRESS_DIRECT"
'UWCLASS'                      "MLOG$_FGA_DEMO"
'UWCLASS'                      "FGA_DEMO"
'UWCLASS'                      "PLSQL_PROFILER_RUNS"
'UWCLASS'                      "PLSQL_PROFILER_UNITS"
'UWCLASS'                      "PLSQL_PROFILER_DATA"
'UWCLASS'                      "RUPD$_FGA_DEMO"
'UWCLASS'                      "SERVERS"
Re: spooling data [message #541816 is a reply to message #541687] Thu, 02 February 2012 08:53 Go to previous messageGo to next message
sant_new
Messages: 164
Registered: June 2008
Senior Member
Thanks a lot BlackSwan.
Re: spooling data [message #541818 is a reply to message #541816] Thu, 02 February 2012 09:32 Go to previous messageGo to next message
sant_new
Messages: 164
Registered: June 2008
Senior Member
I'm trying to extract data from a table with 50+ fields, when I run the SQL I get:

SP2-0027: Input is too long (> 2499 characters)

I tried with SET SERVEROUTPUT ON SIZE UNLIMITED, still get the same error. Is there a limitation to extract large volume of data using spooling?

Thanks again
Re: spooling data [message #541822 is a reply to message #541818] Thu, 02 February 2012 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This a SQL*Plus error not a SQL one.
Copy and paste your SQL*Plus session.

Regards
Michel
Re: spooling data [message #541825 is a reply to message #541818] Thu, 02 February 2012 10:01 Go to previous message
Barbara Boehmer
Messages: 7988
Registered: November 2002
Location: California, USA
Senior Member
You need to post a copy and paste of a run that produces this error message.
Previous Topic: Suppress Orcale script message dynamically
Next Topic: Oracle Practise at Solution Beacon
Goto Forum:
  


Current Time: Mon Sep 22 18:10:28 CDT 2014

Total time taken to generate the page: 0.09225 seconds