Home » RDBMS Server » Server Utilities » SELECT INTO OUTFILE ERROR (ORACLE10G and LINUX)
SELECT INTO OUTFILE ERROR [message #334296] Wed, 16 July 2008 02:15 Go to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
Helo
i am using a query for output all the columns to a file called result.txt , fields seperated by ',' .
The query is stated below

select * INTO OUTFILE '/tmp/result.txt' fields terminated by ',' from user_master;

But i got the error
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Please help me , thanks in advance
Re: SELECT INTO OUTFILE ERROR [message #334299 is a reply to message #334296] Wed, 16 July 2008 02:23 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SELECT INTO OUTFILE?

Is this valid Oracle syntax? I haven't heard of it earlier. And guess what? There's no OUTFILE keyword in SELECT statement at all!

Google says that something like this exists in MySQL, but - this is Oracle forum.

In Oracle, you'd SPOOL result of a SELECT statement into a file. Set column separator (SET COLSEP) to semi-colon. Read more about these commands in SQL*Plus User's Guide and Reference book.
Re: SELECT INTO OUTFILE ERROR [message #334319 is a reply to message #334299] Wed, 16 July 2008 03:38 Go to previous messageGo to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
I think SPOOL HAS somany drobacks
when i use the codes
SQL> Set echo off heading off feedback off
SQL> set COLSEP '|';
SQL> spool /tmp/user_master.txt
SQL> select * from user_master;
SQL> spool off

the out put file is as follows
SQL> select user_id , user_name from user_master;

bank |bank
sherin |sherin
LIJITH |lijith
RATHEESH |ratheesh
BINOY |binoy
ROOPAN |roopan
REEJESH |reejesh
JANAN |janan
NISHANTH |nishanth
SQL> spool off

I just want the contents of the table not the query
Re: SELECT INTO OUTFILE ERROR [message #334325 is a reply to message #334319] Wed, 16 July 2008 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you execute it from a script and not interactively you have not the statements.

Regards
Michel
Re: SELECT INTO OUTFILE ERROR [message #334331 is a reply to message #334325] Wed, 16 July 2008 04:00 Go to previous messageGo to next message
shiju_t_v
Messages: 56
Registered: May 2007
Location: kannur,kerala
Member
Friend one more doubt
If the result set exceeds the line size then the column adjustments are not correct , that is column seperator not comes in the second line

[Updated on: Wed, 16 July 2008 04:29]

Report message to a moderator

Re: SELECT INTO OUTFILE ERROR [message #334341 is a reply to message #334331] Wed, 16 July 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Set your linesize as greater as you need.
The limit is:
SQL> set linesize 10000000
SP2-0267: linesize option 10000000 out of range (1 through 32767)

Remove the blanks at the end of the lines with:
set trimout on
set trimspool on

Regards
Michel
Re: SELECT INTO OUTFILE ERROR [message #334349 is a reply to message #334319] Wed, 16 July 2008 04:53 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
shiju_t_v
I think SPOOL HAS somany drobacks

... or is it you who doesn't know how to use its features properly? I can never decide.
Previous Topic: Copying data from SQL Server to Oracle (merged)
Next Topic: IMPDP oracle8 dmp
Goto Forum:
  


Current Time: Sat Dec 10 15:08:40 CST 2016

Total time taken to generate the page: 0.27297 seconds