Home » Other » Client Tools » SQL plus help (merged)
SQL plus help (merged) [message #413779] Fri, 17 July 2009 11:33 Go to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Confused

I have to query 500 tables in my database and spool the tables into 500 separate csv files.

I can do it by one by one. But I am looking for a block which can perform the task without manually entries. Another challenge is that I need each table in one csv file. This means I have to create 500 csv files containing data for 500 tables ...one for each table....

Can anybody suggest or give a sample template would be really helpful..

Awaiting your kind reply frndz...

Anand
Re: SQL plus help [message #413781 is a reply to message #413779] Fri, 17 July 2009 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a SQL query to generate the script that will generate the csv files.

Regards
Michel
Re: SQL plus help [message #413782 is a reply to message #413781] Fri, 17 July 2009 11:39 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
HI Michel,

Thanks for your reply...

I can do it by basic SQL commands.

Set colsep ','
set heading on
set feedback off
set flush off
set termout off
set echo off


Spool c:\\anu\temp7.csv
select * from CDFTS.table1;
Spool off

but I am looking to have a block which take up table names from all_tables and save the dump in individual csv files..
I hope I am clear.
Please let me know if u need any furhter info.
Re: SQL plus help [message #413783 is a reply to message #413779] Fri, 17 July 2009 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
login to DB as schema owner & issue following SQL

select distinct data_type from user_tab_columns order by 1;


Post results back here.

Do any of the columns contain a comma character as part of a string?
Re: SQL plus help [message #413791 is a reply to message #413783] Fri, 17 July 2009 11:53 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Thanks Swan,

I have queried tha all_tab_columns since i dont have access on user_tab_columns.

Please find the result..
DATA_TYPE
----------------------------------------------------------------------------------------------------
CHAR
DATE
NUMBER
TIMESTAMP(6)
TIMESTAMP(7)
UNDEFINED
VARCHAR2
Re: SQL plus help [message #413792 is a reply to message #413782] Fri, 17 July 2009 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select 'I am table '||table_name from all_tables;

Regards
Michel
Re: SQL plus help [message #413793 is a reply to message #413779] Fri, 17 July 2009 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>select * from CDFTS.table1;
Above defaults to implicit conversion of column datatype to string
& could result in not correct data within output *CSV file.

Re: SQL plus help [message #413794 is a reply to message #413792] Fri, 17 July 2009 11:59 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Yes Michel,

I would need to save the dump files of each table in a seperate csv file. Since I have more tables, I am looking for a query to take up the table names from all_tables.....

If passing the table names makes the code complex. I will pass the table names all at once ( if i can) but Please let me know the code to save the table content in seperate csv files.

Please dont hesitate if I am not clear
Re: SQL plus help [message #413796 is a reply to message #413793] Fri, 17 July 2009 12:03 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Hi Swan,

Thanks for notifiying that.

I wil be sending these csv files for auditing purpose. They wil not be validating at data level. However, they are extremely interested to have the table content in seperate CSV files.

Regards,
Anand
Re: SQL plus help [message #413800 is a reply to message #413794] Fri, 17 July 2009 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted how to do it for 1 table.
I posted how to generate something for each and all tables.
Combine the 2.

Regards
Michel
Re: SQL plus help [message #413811 is a reply to message #413800] Fri, 17 July 2009 13:16 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Hi Michel,
Iam not sure if i understand your note properly.

if u have identified a way to achieve this, please share the psudo code for this. I am still struggling to identify the solution...

my challange has come when I am trying to spool the file with in the pl/sql block.

Regards,
Ananda Panigrahi...
Re: SQL plus help [message #413812 is a reply to message #413811] Fri, 17 July 2009 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select '
Set colsep '',''
set heading on
set feedback off
set flush off
set termout off
set echo off
Spool c:\\anu\'||table_name||'csv
select * from CDFTS.'||table1||';'||'
Spool off '
from all_tables
/

Re: SQL plus help [message #413813 is a reply to message #413779] Fri, 17 July 2009 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>my challange has come when I am trying to spool the file with in the pl/sql block.

What PL/SQL block?

Previously all you posted were SQL*Plus directives & SELECT statement

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: SQL plus help [message #413821 is a reply to message #413812] Fri, 17 July 2009 13:57 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Thanks Michel,

It's an elegant answer. With this sql I have almost acheived my objective.

select '
Set colsep '',''
set heading on
set feedback off
set flush off
set termout off
set echo off
Spool c:\anu\'||table_name||'.csv
select * from CDFTS.'||table_name||';'||'
Spool off '
from all_tables
/

Above query given by you has absolutely served my purpose.

This query is generating the sql commands which needs to be executed. I would need to copy the output and then needs to run in the SQL prompt.

Is it possible to include the generation and execution in single step
Re: SQL plus help [message #413823 is a reply to message #413821] Fri, 17 July 2009 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, spool the result in a file and call it just after.
spool x.sql
query
spool off
@x

Regards
Michel
Re: SQL plus help [message #413828 is a reply to message #413823] Fri, 17 July 2009 14:33 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Excellent Michel,

I am extremely delighted with the answer.

I can see seperate csv files for each Table. Here for empty tables The csv file was created empty. For these kind of tables I want to display the column names without data.

I tried by changing different set operators but not sure which one meets my requirement.

Kindly share if u have any suggestion.

Thank you...


Query I have used.


set heading off
set feedback off
set flush off
set termout off
set echo off
spool c:\anu\query.sql

select '
Set colsep '',''
set heading on
set feedback off
set flush off
set termout off
set echo off
Spool c:\anu\'||table_name||'.csv
select * from CDFTS.'||table_name||';'||'
Spool off '
from all_tables where owner='CDFTS'
/
spool off
@c:\anu\query.sql

[Updated on: Fri, 17 July 2009 14:35]

Report message to a moderator

SQL Plus display column names [message #413832 is a reply to message #413779] Fri, 17 July 2009 15:08 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
hi frndz,

I am working on SQL plus.

I have an empty table BCDFS03_PRED. I want to see the column names for this table without data which I am trying to spool to file.

Could anybody please help me which set operator I have use. If not please explain the other way if any???

SQL> select * from CDFTS.BCDFS03_PRED;

no rows selected
Re: SQL Plus display column names [message #413834 is a reply to message #413832] Fri, 17 July 2009 15:10 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
You can use either
desc {tablename}


or look at the view USER_TAB_COLUMNS.
Re: SQL Plus display column names [message #413835 is a reply to message #413834] Fri, 17 July 2009 15:15 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Hi Joy,

Iam doing this exercise for bulk of tables.

I want to have all column names in a row as how it appears if data exists.....though there is no data.....
At the end if it displays " no rows returned" then it would be perfect.

Thanks,
Anand
Re: SQL Plus display column names [message #413837 is a reply to message #413835] Fri, 17 July 2009 15:22 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Use a combination of USER_TAB_COLUMNS and either a "pivot" or stragg.

You can search for both of them here at OraFAQ.
Re: SQL plus help [message #413838 is a reply to message #413779] Fri, 17 July 2009 15:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please be aware that the "column names" in the resultant *csv file will NOT necessarily match the column_name in the DB; in some cases SQL*Plus truncates the name.
Re: SQL plus help [message #413840 is a reply to message #413838] Fri, 17 July 2009 15:38 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Thans Swan,

I understand that extracted CSV file will fail to represent the Database data at below instances.
1. if the table datacontent has a comma in it. it will be drifted to next cell. thus the whole row fails to represent the right columns.
2. Some times SQL plus may truncate the data on display.
3. csv file convers datatypes to string format.

Swan ,Please add up if any more instances present in such way.

Thanks
Re: SQL plus help [message #413902 is a reply to message #413840] Sat, 18 July 2009 10:31 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> set pagesize 0
SQL> set feedback off
SQL> set flush off
SQL> set termout off
SQL> set echo off
SQL> set linesize 10000
SQL> set trimout on
SQL> set trimspool on
SQL> spool c:\query.sql
SQL> select '
  2  Set colsep '',''
  3  Spool c:\'||table_name||'.csv
  4  select wm_concat(column_name)
  5  from (select column_name from all_tab_columns
  6        where owner='''||owner||''' and table_name='''||table_name||'''
  7        order by column_id);'||'
  8  select * from '||owner||'.'||table_name||';'||'
  9  Spool off '
 10  from all_tables
 11  where owner='MICHEL' and table_name in ('DEPT','T')
 12  order by table_name
 13  /
Set colsep ','
Spool c:\DEPT.csv
select wm_concat(column_name)
from (select column_name from all_tab_columns
      where owner='MICHEL' and table_name='DEPT'
      order by column_id);
select * from MICHEL.DEPT;
Spool off

Set colsep ','
Spool c:\T.csv
select wm_concat(column_name)
from (select column_name from all_tab_columns
      where owner='MICHEL' and table_name='T'
      order by column_id);
select * from MICHEL.T;
Spool off
SQL> spool off
SQL> @c:\query.sql
SQL> host type c:\DEPT.csv
DEPTNO,DNAME,LOC
        10,ACCOUNTING    ,NEW YORK
        20,RESEARCH      ,DALLAS
        30,SALES         ,CHICAGO
        40,OPERATIONS    ,BOSTON

SQL> host type c:\T.csv
COL1,COL2

Regards
Michel
Previous Topic: Installation of Oracle
Next Topic: sql developer 1.5.4
Goto Forum:
  


Current Time: Fri Mar 29 08:07:27 CDT 2024