OraFAQ Forum: Server Utilities » SELECT INTO OUTFILE ERROR

Home » RDBMS Server » Server Utilities » SELECT INTO OUTFILE ERROR (ORACLE10G and LINUX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SELECT INTO OUTFILE ERROR [message #334296]

Wed, 16 July 2008 02:15

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

Report message to a moderator

Re: SELECT INTO OUTFILE ERROR [message #334299 is a reply to message #334296]

Wed, 16 July 2008 02:23

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: SELECT INTO OUTFILE ERROR [message #334319 is a reply to message #334299]

Wed, 16 July 2008 03:38

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

Report message to a moderator

Re: SELECT INTO OUTFILE ERROR [message #334325 is a reply to message #334319]

Wed, 16 July 2008 03:45

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you execute it from a script and not interactively you have not the statements.

Regards
Michel

Report message to a moderator

Re: SELECT INTO OUTFILE ERROR [message #334331 is a reply to message #334325]

Wed, 16 July 2008 04:00

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

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SELECT INTO OUTFILE ERROR [message #334349 is a reply to message #334319]

Wed, 16 July 2008 04:53

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	Copying data from SQL Server to Oracle (merged)
Next Topic:	IMPDP oracle8 dmp

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 13 00:18:35 CST 2024