Home » SQL & PL/SQL » SQL & PL/SQL » how to import table data from sql plus (oracle 10g)
how to import table data from sql plus [message #608146] Mon, 17 February 2014 00:49 Go to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

i am using oracle 10g , i have a table emp
now i want to export that table data in txt file using sql plus
please tell me how to do that .
Thanks

[Updated on: Mon, 17 February 2014 00:56]

Report message to a moderator

Re: how to import table data from sql plus [message #608151 is a reply to message #608146] Mon, 17 February 2014 01:00 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Spool.
Re: how to import table data from sql plus [message #608154 is a reply to message #608151] Mon, 17 February 2014 01:16 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

using spool i will have to export data using select command , but i do not want to use select command
second thing (not sure) i can't import data using spool file .
so i want to know any other efficient way so that i cant export and import data easily
thanks
Re: how to import table data from sql plus [message #608157 is a reply to message #608154] Mon, 17 February 2014 01:20 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In your first message you said that you want to create a TXT file using SQL*Plus (1). Now you said that you don't want to use SELECT command (2).

I don't know any other way to do 1 without 2.
Re: how to import table data from sql plus [message #608163 is a reply to message #608157] Mon, 17 February 2014 01:47 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
hi,

I think there is not having any other solution.Only the solution given by Littlefoot.Otherwise you have to use
ETL Tools like Informatica/Ab-Initio/Data Stage etc.
Re: how to import table data from sql plus [message #608165 is a reply to message #608163] Mon, 17 February 2014 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And how do you think the tools will extract the data? Maybe using SELECT.

Re: how to import table data from sql plus [message #608166 is a reply to message #608146] Mon, 17 February 2014 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@jgjeetu

In addition to think and search before posting (see your previous topics), please take care of what post.

Title
how to import table data from sql plus

Question
i want to export that table data in txt file


Re: how to import table data from sql plus [message #608169 is a reply to message #608166] Mon, 17 February 2014 02:51 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

my requirement is to 'export' , that was by mistake Smile
Re: how to import table data from sql plus [message #608171 is a reply to message #608169] Mon, 17 February 2014 02:55 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what's the problem with LF's solution?
Re: how to import table data from sql plus [message #608174 is a reply to message #608171] Mon, 17 February 2014 03:19 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

the problem is i can't import data using spool file. (not sure)
Re: how to import table data from sql plus [message #608175 is a reply to message #608174] Mon, 17 February 2014 03:22 Go to previous messageGo to next message
ThomasG
Messages: 3101
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then what format(s) can you import?
Re: how to import table data from sql plus [message #608176 is a reply to message #608165] Mon, 17 February 2014 03:35 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Michel,

I m using Ab-Initio at present and giving only table name and unloading data from table.I am unloading
700 million data within 5-6 minutes using Parallelism.
Re: how to import table data from sql plus [message #608180 is a reply to message #608176] Mon, 17 February 2014 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what do you want to be able to import the data into?
Re: how to import table data from sql plus [message #608183 is a reply to message #608176] Mon, 17 February 2014 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
msol25 wrote on Mon, 17 February 2014 10:35
Dear Michel,

I m using Ab-Initio at present and giving only table name and unloading data from table.I am unloading
700 million data within 5-6 minutes using Parallelism.


And it does not use SELECT?
Note that the question is "i want to export that table data in txt file using sql plus"
Re: how to import table data from sql plus [message #608186 is a reply to message #608183] Mon, 17 February 2014 04:14 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

ok forget about earlier things, my requirement is
i just want to know the method though which i can export table data into supported document file and after that in case of need i could import data from that document again.
thats it . thanks
Re: how to import table data from sql plus [message #608189 is a reply to message #608186] Mon, 17 February 2014 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Spool in a CSV format.
Search for this, it is a FAQ.


Re: how to import table data from sql plus [message #608191 is a reply to message #608189] Mon, 17 February 2014 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about data pump export/import (or even original exp/imp utilities)?
Re: how to import table data from sql plus [message #608193 is a reply to message #608186] Mon, 17 February 2014 04:32 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

thanks Smile
Re: how to import table data from sql plus [message #608194 is a reply to message #608191] Mon, 17 February 2014 05:00 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

thanks michel , finally i exported data using following commands:-
SQL> SPOOL ON
SQL> SET COLSEP ','
SQL> SET ECHO OFF NEWPAGE 0 SPACE 0 PAGESIZE 0 FEED OFF HEAD OFF TRIMSPOOL ON
SQL> SPOOL C:\EMP.TXT
SQL> SELECT * FROM EMP



spool file get from attachment
now tell me how to import data from this txt file.
thanks
  • Attachment: EMP.TXT
    (Size: 1.14KB, Downloaded 25 times)
Re: how to import table data from sql plus [message #608196 is a reply to message #608194] Mon, 17 February 2014 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use SQL*Loader (details in the documentation).

Re: how to import table data from sql plus [message #608197 is a reply to message #608196] Mon, 17 February 2014 05:22 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

from sql*loader section i got above queries, but facing problem
please tell me the steps/codes to import data.
thanks
Re: how to import table data from sql plus [message #608199 is a reply to message #608197] Mon, 17 February 2014 05:26 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a control file which is read by SQLLDR executable; if you create it correctly, data will be loaded into the destination table. Every step is described in documentation. Take some time (hours, if not days) to read it. Then come back if you still have some questions.
Re: how to import table data from sql plus [message #608235 is a reply to message #608199] Mon, 17 February 2014 23:06 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

how to create contol file , and where to write following code:-
sqlldr username@server/password control=loader.ctl

please tell
Re: how to import table data from sql plus [message #608236 is a reply to message #608235] Mon, 17 February 2014 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
>how to create control file ,
use any text editor

>and where to write following code:- sqlldr username@server/password control=loader.ctl
at OS command line.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Re: how to import table data from sql plus [message #608237 is a reply to message #608236] Mon, 17 February 2014 23:32 Go to previous messageGo to next message
jgjeetu
Messages: 237
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

can you attch control file for me , so that i could understand what to write inside control file and where to store it.
Re: how to import table data from sql plus [message #608238 is a reply to message #608237] Mon, 17 February 2014 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
jgjeetu wrote on Mon, 17 February 2014 21:32
can you attch control file for me , so that i could understand what to write inside control file and where to store it.


are you incapable or unwilling to Read The Fine Manual yourself?

http://docs.oracle.com/cd/E16655_01/server.121/e17639/ldr_control_file.htm#SUTIL1049
Re: how to import table data from sql plus [message #608241 is a reply to message #608199] Tue, 18 February 2014 01:20 Go to previous message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you suffering from partial blindness, or are you simply ignoring what I say (feel free to say so, so that I could stop bothering answering your questions).

Littlefoot wrote on Mon, 17 February 2014 12:26

Every step is described in documentation. Take some time (hours, if not days) to read it. Then come back if you still have some questions.

Previous Topic: EMAIL from ORACLE8i using Corporate Netwrok
Next Topic: materialized view
Goto Forum:
  


Current Time: Fri Oct 24 09:57:25 CDT 2014

Total time taken to generate the page: 0.08479 seconds