export data to .cvs or .txt

[theresatan]

Dear Sir:

I need to export data from oracle 9i to SQL server 2000. Oracle server is on hosting site and connction is SSH Tunnel.

I set up a linked server in SQL server with oralce ODBC connection.
For small table, it works fine.
For large table, it did not work. When I run select count (*) from tablenme, I got "OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time."

But I can run select * from tablename using SQL Plus.

Is there any utility can do that?

If not, I am going to export the data to a .cvs or .txt file, then load them to SQL server.

When I use spool filename, the first line is
"SQL> select * from tablename; "
My questions are:
1. How to get rid of it without manually effor( my file is 100 MB and difficult to open)?

2. How to keep the heading on first line only?

3. how to spool to a cvs or excel file.

Thanks in Advance!

Theresa

 

[LKBrwnDBA]

Check here:

http://www.tek-tips.com/viewthread.cfm?qid=883991

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Theresa,

Here is my favourite script to create a Comma-Separated-Values (.csv) file from any table that the current Oracle login owns (except for tables with LONG columns). I call the script, "GenASCII.sql":

Section 1 -- GenASCII.sql code:

set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"''' 
  from user_tab_columns
 where table_name = upper('&x');
prompt from &x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Section 2 -- Sample invocation for a short, demo file:

SQL> @genascii
Enter the table to ASCII: s_dept
Enter the flat file to write: yada.txt

Following output is generated script that writes text output from table "s_dept"

set echo off
set feedback off
set heading off
set pagesize 0
spool yada.txt
Select
'"'||ID|| '"'
||',"'||NAME|| '"'
||',"'||REGION_ID|| '"'
from s_dept
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "yada.txt"

"10","Finance","1"
"31","Sales","1"
"32","Sales","2"
"33","Sales","3"
"34","Sales","4"
"35","Sales","5"
"41","Operations","1"
"42","Operations","2"
"43","Operations","3"
"44","Operations","4"
"45","Operations","5"
"50","Administration","1"

Output file = "yada.txt"

SQL>

Let us know if this helps resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[theresatan]

Actually, they are not table , they are views, what should modified?

Thanks!

Theresa

 

[theresatan]

Also,
1. How to eliminate the select statement in output file( my file is quite big and difficult to open)
2. How to keep the heading in the first row?

Thanks, Mufasa!

Theresa

 

[dbomrrsm]

look at sql server dts - set up the oracle connection and the sql server connection the create a transform task between the two - you can do a select * or define any query you like to bring in the oracle data.

good luck.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SantaMufasa]

Theresa,

Since GenASCII.sql uses the USER_TAB_COLUMNS view, the script also handles VIEWS without any modifications.

As far as HEADINGS are concerned, note that GenASCII.sql generates only one column: a concatenated string of all of your columns, separated by commas (with double quotes). Therefore, in this case, column headings are not applicable to GenASCII output. Since the GenASCII.sql script "acquires" the column names for each table, you could use a variation on my code to produce what you want insofar as column headings are concerned.

I'll bet you are simply doing a copy-and-paste of my code from Tek-Tips into your SQL*Plus prompt, right? If you save the code to a file (such as "GenASCII.sql"), then when you invoke your GenASCII script with a "SQL> @Genascii" command, you will not see any of the SQL code in your resulting flat file. (The code that suppresses the output, "SET ECHO OFF", works only when you are executing code from a script.) Try it this way and confirm that it works properly for you.

Now, DBomrrsm brings up a point that worries me a little...Which direction are you wanting to go with your .csv file? Are you going from Oracle to SQL Server or from SQL Server to Oracle. My code works properly only if you are going from Oracle to a .csv file...since GenASCII.sql contains several SQL*Plus commands that only Oracle understands, you will get many errors if you try to run GenASCII.sql from a non-Oracle db engine.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbomrrsm]

Santa

The original post said from oracle to sql server - i only jumped in because of my experience with sql server - i bow down to your vast experience with oracle and you have helped me many times.

There is a DTS (Data Transformation Services) in sql server that is great for getting data from various and numerous sources. A lovely gui tool that is quite noddy when you get used to it but it does a very important job very well.

It is literally as easy as i suggested above - gui to create one connection gui to create the next - draw a line between the two in the direction you want to go with the data, double click the line and add your select sql and then select from a few, very well prompted, options i.e. create a new table or insert into another !!

Simple as that and i dont know why oracle cant replicate this because most out there in the oracle world can do anything inside a DB but getting stuff in and out is a nightmare.

My two penneth.

And thanks again Santa for all the help you have provided me and countless others with in the past and hopefully for a long time in the future.



[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SantaMufasa]

DBomrrsm, good points, all. Oracle has, in fact, some good tools to accomplish results very similar to the description that you mention, but my presumption is that unless someone discloses that they have such tools available, I create a solution that depends upon the most common denomination of Oracle tools.

I am glad that I have posted information (in times past) that you have found useful. I appreciate your thoughtful willingness to share, as well...such is what makes Tek-Tips a great place to "live", right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbomrrsm]

My point exactly Santa - Oracle people rely on you - SQL Server people rely on a large corporation that has decided to help users with something thats not too easy to do !

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SantaMufasa]

...

That makes me feel a wide range of emotions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[theresatan]

DBomrrsm:

I used SQL import and export Wizard and it works, but the data volumn is large and it is still running.The performence is slower than I expect.

Thanks, DBomrrsm!

Mufasa:

I followed your instruction (copy and paste ...), it work for table only, also, the sql statement is still in the output file.
when I replace the table with view, it return no rows. I even tried 'select * from user_tab_columns where table_name = myViewname' directly in SQL plus, and returned no rows. I believe work for you but I don't know why it did not work for me.

I am interested in the oracle tool that has the similar function as SQL Server DTS. Can I have the tool name?

Thanks again all your helps!

Theresa

 

[SantaMufasa]

I followed your instruction (copy and paste ...), it work for table only,

My suggestion was to not copy and paste, at least into the SQL* prompt...if you do any copying and pasting, it should be from my earlier post, into a file perhaps called GenASCII.sql. Then you execute the script from a SQL> prompt with the "@genascii" command. By doing things this way, and by virtue of the "set echo off" command, you should not see the SQL statement in the output file.

Next, I just re-tested the GenASCII.sql script while accessing a VIEW (instead of a TABLE), and it worked perfectly.

Lastly, if you execute the command:

select *  from user_tab_columns  where table_name = 'myViewname';

...it will always return no rows since your viewname must be in all uppercase. Example:

select *  from user_tab_columns  where table_name = '[b]MYVIEWNAME[/b]';

If you continue to have trouble, Theresa, please post a copy-and-paste of your actual SQL*Plus screen, containing all of your interactions and error messages. (It is very difficult to troubleshoot problems while trying to guess what may be your screen.)




Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[theresatan]

Mufasa:

Sorry for replying late because I have something due today and I just sent it out.

1. Regarding SQL statement in output file:
---------
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 32767
spool C:\DL\PARTS1.txt
select * from PARTS;
spool off
--------
If I paste above to SQL plus, the output file has the sql statement in it. But if I save above in a file and then run this file, the output file has no sql statement in it.

2. Regarding the script not working for view
Actually, the script work for the local instance's view but not for the remote one, but it is possible that the DBA on that site restrict my access.

In my remote Oracle 10g instance(connect by SSH tunel)
I have no table acces, but only view(ex:R_Activity_Type) access. Following is the screen snapshot:
-----------------------------------------------------
SQL> select * from R_ACTIVITY_TYPE where ACTIVITY_TYPE_KEY in ( 1,2,3,4,5);

ACTIVITY_TYPE_KEY ACTIVITY_TYPE_DESCRI A
----------------- -------------------- -
1 Internal Training N
2 Ext Training - Bill Y
3 Internal Nonbillable N
4 Travel Time - Bill Y
5 Sick Time N

SQL> select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"'''
2 from user_tab_columns
3 where table_name = 'R_ACTIVITY_TYPE';

no rows selected

SQL>
---------------------------------------

3. Can you recommand some utilities to move data from oracle to SQL? It took me 4 hours to move 200,000 rows over SSH.

Thank you very much and have a nice weekend!

Theresa

 

[dbomrrsm]

use dts in sql server

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SantaMufasa]

Theresa,

Regarding your Issue #1, please let me be perfectly clear about copying and pasting my code:
DO NOT PASTE MY CODE DIRECTLY INTO THE SQL PROMPT !!!!
DO NOT PASTE MY CODE DIRECTLY INTO THE SQL PROMPT !!!!
DO NOT PASTE MY CODE DIRECTLY INTO THE SQL PROMPT !!!!
DO NOT PASTE MY CODE DIRECTLY INTO THE SQL PROMPT !!!!
DO NOT PASTE MY CODE DIRECTLY INTO THE SQL PROMPT !!!!
DO NOT PASTE MY CODE DIRECTLY INTO THE SQL PROMPT !!!!

Here is what you should do:

COPY and PASTE my code into a text file, preferrably named "GenASCII.sql", so was can discuss your situation from common ground. But, don't use the code, above...use the code in the following section since it should solve your Issue #2.

Regarding your Issue #2, the problem you are having with seeing no columns is that you do not own the view. My earlier code works just on views that you own. Following is code that I modified just for you, that works on any table or view to which you have permissions to access:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
accept w prompt "Enter the Oracle username that owns the source table: "
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt 
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"''' 
  from all_tab_columns
 where owner = upper('&w')
   and table_name = upper('&x');
prompt from &w..&x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Now again, save the above code to a file, then run the file as a script from your SQL> prompt (e.g."SQL> @genascii").

Regarding your Issue #3, once you get this script running satisfactorily, you can ftp the .csv file to your target server (much faster than SSH) to populate your target database.

Let us know of your success or if you have follow-on questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.