SQL plus help (merged) [message #413779] |
Fri, 17 July 2009 11:33  |
anandapani
Messages: 14 Registered: July 2009
|
Junior Member |
|
|
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 #413782 is a reply to message #413781] |
Fri, 17 July 2009 11:39   |
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 #413791 is a reply to message #413783] |
Fri, 17 July 2009 11:53   |
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 #413794 is a reply to message #413792] |
Fri, 17 July 2009 11:59   |
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   |
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 #413811 is a reply to message #413800] |
Fri, 17 July 2009 13:16   |
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 #413821 is a reply to message #413812] |
Fri, 17 July 2009 13:57   |
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 #413828 is a reply to message #413823] |
Fri, 17 July 2009 14:33   |
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   |
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 #413835 is a reply to message #413834] |
Fri, 17 July 2009 15:15   |
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 help [message #413840 is a reply to message #413838] |
Fri, 17 July 2009 15:38   |
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  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|