Home » SQL & PL/SQL » SQL & PL/SQL » Create TSV file (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production, sun4u - OS version:5.9)
Create TSV file [message #571938] Tue, 04 December 2012 11:22 Go to next message
rahul1982
Messages: 31
Registered: November 2011
Location: Pune
Member
Hi,

As per project requirement, we need to create tab seeprated file for a table. This table contain around 1000000 records.

I have written below sql

 set pagesize 10000
set feedback OFF
set serveroutput on
set heading OFF
spool D:\Officework\Database_maintaince\MATRIX.txt
SELECT CorporationID || CHR(9) || Corp_File_Key || CHR(9) || State_Code ||
       CHR(9) || File_Data_Date || CHR(9) || Input_Name || CHR(9) ||
       Raw_Name || CHR(9) || Address_1 || CHR(9) || Address_2 || CHR(9) || City ||
       CHR(9) || State || CHR(9) || Zip || CHR(9) || County || CHR(9) ||
       Country || CHR(9) || Mailing_Add1 || CHR(9) || Mailing_Add2 ||
       CHR(9) || Mailing_City || CHR(9) || Mailing_State || CHR(9) ||
       Mailing_Zip || CHR(9) || Phone || CHR(9) || Ext || CHR(9) || Fax ||
       CHR(9) || EMail || CHR(9) || DBA_Name || CHR(9) || Registry_Number ||
       CHR(9) || Tax_ID || CHR(9) || Prin_Bus_Add1 || CHR(9) ||
       Prin_Bus_Add2 || CHR(9) || Prin_Bus_City || CHR(9) || Prin_Bus_State ||
       CHR(9) || Prin_Bus_Zip || CHR(9) || Prin_Bus_County || CHR(9) ||
       Prin_Bus_Country || CHR(9) || Incorporation_State || CHR(9) ||
       Filing_Date || CHR(9) || Filing_Type || CHR(9) || Corp_Status ||
       CHR(9) || Corp_Status_Date || CHR(9) || Last_Report_Date || CHR(9) ||
       Expire_Date || CHR(9) || Bankrupt_Date || CHR(9) || SIC || CHR(9) ||
       Corp_Type || CHR(9) || Non_Profit_Type || CHR(9) || Term || CHR(9) ||
       Home_State || CHR(9) || Capital || CHR(9) || Purpose || CHR(9) ||
       Corp_Description || CHR(9) || Jurisdiction || CHR(9) || Statute ||
       CHR(9) || RawNameIndex || CHR(9) || ComplianceStatus
  FROM MATRIX;
Spool off;


This query is taking more than 6 hours.

please suggest if there any other way how can we create tab separated file. Or we can export the entire table in tab separated file format.

Thankx,
Rahul.

Re: Create TSV file [message #571939 is a reply to message #571938] Tue, 04 December 2012 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://tkyte.blogspot.fr/2009/10/httpasktomoraclecomtkyteflat.html

Regards
Michel
Re: Create TSV file [message #571940 is a reply to message #571939] Tue, 04 December 2012 11:42 Go to previous messageGo to next message
Bill B
Messages: 1086
Registered: December 2004
Senior Member
try. The commands must be in a script file. Without echoing of the select and trimming the output it will run much faster.
set echo off
set pagesize 0
set linesize 10000
set trimspool on
set feedback OFF
set termout off
spool D:\Officework\Database_maintaince\MATRIX.txt
SELECT CorporationID || CHR(9) || Corp_File_Key || CHR(9) || State_Code ||
       CHR(9) || File_Data_Date || CHR(9) || Input_Name || CHR(9) ||
       Raw_Name || CHR(9) || Address_1 || CHR(9) || Address_2 || CHR(9) || City ||
       CHR(9) || State || CHR(9) || Zip || CHR(9) || County || CHR(9) ||
       Country || CHR(9) || Mailing_Add1 || CHR(9) || Mailing_Add2 ||
       CHR(9) || Mailing_City || CHR(9) || Mailing_State || CHR(9) ||
       Mailing_Zip || CHR(9) || Phone || CHR(9) || Ext || CHR(9) || Fax ||
       CHR(9) || EMail || CHR(9) || DBA_Name || CHR(9) || Registry_Number ||
       CHR(9) || Tax_ID || CHR(9) || Prin_Bus_Add1 || CHR(9) ||
       Prin_Bus_Add2 || CHR(9) || Prin_Bus_City || CHR(9) || Prin_Bus_State ||
       CHR(9) || Prin_Bus_Zip || CHR(9) || Prin_Bus_County || CHR(9) ||
       Prin_Bus_Country || CHR(9) || Incorporation_State || CHR(9) ||
       Filing_Date || CHR(9) || Filing_Type || CHR(9) || Corp_Status ||
       CHR(9) || Corp_Status_Date || CHR(9) || Last_Report_Date || CHR(9) ||
       Expire_Date || CHR(9) || Bankrupt_Date || CHR(9) || SIC || CHR(9) ||
       Corp_Type || CHR(9) || Non_Profit_Type || CHR(9) || Term || CHR(9) ||
       Home_State || CHR(9) || Capital || CHR(9) || Purpose || CHR(9) ||
       Corp_Description || CHR(9) || Jurisdiction || CHR(9) || Statute ||
       CHR(9) || RawNameIndex || CHR(9) || ComplianceStatus
  FROM MATRIX;
Spool off;

[Updated on: Tue, 04 December 2012 11:46]

Report message to a moderator

Re: Create TSV file [message #571941 is a reply to message #571940] Tue, 04 December 2012 11:58 Go to previous message
rahul1982
Messages: 31
Registered: November 2011
Location: Pune
Member
Michel - Thank you for providing link. I will explore more on pro*c & pl sql code.

Bill - Thank you so much, I have tested the SQL script and now its executed in 10 mins.

[Updated on: Tue, 04 December 2012 11:58]

Report message to a moderator

Previous Topic: execution plan
Next Topic: SQL Syntax Error
Goto Forum:
  


Current Time: Sun Aug 31 05:38:21 CDT 2014

Total time taken to generate the page: 0.08503 seconds