Oracle csv export question

I'm trying to run my sql code and have it export to a file.
I can see records being generated, but when I check the actual csv file, all it states are errors.
Can someone take a look and let me know if I'm missing something.

here is my sql code:

spool c:\Work\PS PR Register.csv
> SELECT /*csv*/prd.EMPLOYEE,
       prd.PROCESS_LEVEL,
       prd.DEPARTMENT,
       prd.DST_ACCT_UNIT,
       prd.DST_ACCOUNT,
       prd.DST_SUB_ACCT,
       prd.PCD_SEQ_NBR,
       prp.PAY_CODE,
       prd.DED_CODE,
       prd.HOURS,
       prd.DIST_AMT,
       prd.GL_DATE,
       prd.RUN_DATE,
       prd.RECORD_TYPE
     
FROM prod.PRDISTRIB prd,
     prod.prpaycode prp
WHERE prd.pcd_seq_nbr = prp.seq_nbr (+)
--and PRDISTRIBGL_DATE Between (10/27/2017 And 11/30/2017#)
AND prd.RUN_DATE = '24 JAN 2018'
AND prd.COMPANY=1;
metalteckAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
Try this:
spool c:\Work\PS PR Register.csv
SELECT /*csv*/prd.EMPLOYEE,
       prd.PROCESS_LEVEL, 
       prd.DEPARTMENT, 
       prd.DST_ACCT_UNIT, 
       prd.DST_ACCOUNT, 
       prd.DST_SUB_ACCT, 
       prd.PCD_SEQ_NBR, 
       prp.PAY_CODE, 
       prd.DED_CODE, 
       prd.HOURS, 
       prd.DIST_AMT, 
       prd.GL_DATE, 
       prd.RUN_DATE, 
       prd.RECORD_TYPE
FROM prod.PRDISTRIB prd,
     prod.prpaycode prp 
WHERE prd.pcd_seq_nbr = prp.seq_nbr (+)
--and PRDISTRIBGL_DATE Between (10/27/2017 And 11/30/2017#) 
AND prd.RUN_DATE = '24 JAN 2018' 
AND prd.COMPANY=1;
spool off

Open in new window

If that isn't working, then please post table creation statements and sample data (preferably in the form of inserts).

I don't work with SQL*Developer much, but a simple test that I put together worked just fine.
create table mytab (a number, b varchar2(10), c date);
insert into mytab values (1, 'one', sysdate);
insert into mytab values (2, 'two', sysdate+1);
commit;

Open in new window

Then in SQL*Developer
spool /tmp/qry.out
select * from mytab;
spool off

Open in new window

Results in
SQL> select /*csv*/ * from mytab;

"A","B","C"
1,"one",19-FEB-18
2,"two",20-FEB-18

SQL> spool off

Open in new window

No errors and the output looks correct.
0
 
johnsoneSenior Oracle DBACommented:
Added Oracle Database topic.

I am assuming that you are using SQL*Developer to run this.

What exactly are the errors?

The more information you give the better answer you get.
0
 
metalteckAuthor Commented:
Hi johnsone,

I get the following error.
The error message is repeated for each value and table in the query.

> > SELECT /*csv*/prd.EMPLOYEE,

Error starting at line 2 in command:
> SELECT /*csv*/prd.EMPLOYEE,
Error report:
Unknown Command
> prd.PROCESS_LEVEL,
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
metalteckAuthor Commented:
Thanks johnsone. Feels like I'm making progress now.
So now when I run that query, instead of the errors I mentioned beforehand, now I get the actual sql code in the csv file.
0
 
awking00Commented:
You might also try using a common table expression, something like -
spool c:\Work\PS\Register.csv
with csvquery as
(SELECT
 EMPLOYEE, PROCESS_LEVEL, DEPARTMENT, DST_ACCT_UNIT, DST_ACCOUNT, DST_SUB_ACCT, PCD_SEQ_NBR,
 PAY_CODE, DED_CODE, HOURS, DIST_AMT, GL_DATE, RUN_DATE, RECORD_TYPE
 FROM PRDISTRIB, PRPAYCODE
 WHERE PRDISTRIB.pcd_seq_nbr = PRPAYCODE.seq_nbr (+)
 --and PRDISTRIBGL_DATE Between (10/27/2017 And 11/30/2017#)
 AND RUN_DATE = '24 JAN 2018'
 AND COMPANY=1
)
SELECT /*csv*/ * FROM csvquery;
spool off;
0
 
Mark GeerlingsDatabase AdministratorCommented:
I don't recommend adding the complexity of a "with ..." statement at the top.  I think the main problem in your query was no space on the top line after your "csv" comment before the "prd.".

So, if you change this:
"SELECT /*csv*/prd.EMPLOYEE,"

to this:
SELECT /*csv*/ prd.EMPLOYEE,"

it may work as you intend.  

I don't recognize that "csv" comment as being something that Oracle will understand.  That syntax "/*  ...*/"  is legal in Oracle queries.  But, usually whatever you put there is ignored by Oracle when it executes the query.  The exception is: something that Oracle recognizes there as an "optimizer hint", that is: an instruction that you want to pass to Oracle's query optimizer so it uses a different "access method" or "explain plan" for the query than what it would use by default.  An "optimizer hint" can affect the performance of the query, but it is not supposed to affect the output that gets returned.

The normal way to tell Oracle that you want the output of a query saved to a file is to use "spool" and "spool off" commands like Johnsone suggested.
0
 
johnsoneSenior Oracle DBACommented:
/* csv */ is a hint that SQL*Developer understands.  It is directing SQL*Developer how to output the data.  I don't know of anything else that would understand that.  That was my reason for assuming that SQL*Developer is being used.  Oracle itself would recognize that as a comment and ignore it.

Spacing after the comment shouldn't make a difference, but it may.

As suggested, make sure you have the spool off command.  You may not see output because of buffered output and that command should flush the buffer.
0
 
slightwv (䄆 Netminder) Commented:
I believe the original issue was a ">" in the script.

You posted:
> SELECT /*csv*/prd.EMPLOYEE,


SQLCl is pretty much SQL Developer without the GUI also recognizes it.
0
 
johnsoneSenior Oracle DBACommented:
Honestly, never tried it.  The only references I found to it were specific to SQL*Developer.
0
 
slightwv (䄆 Netminder) Commented:
>>Honestly, never tried it.

I NEVER thought I would EVER say this but:  I'm slowly starting to experiment with it and like what I'm seeing.  I despise SQL Developer and have avoided SQLCl because of it.

THEN I can across commands that don't exist in SQLPlus like:  "info table_name" and "ddl table_name".  In addition to the CSV output you have some other nice formatting options.

The info command is almost worth the cutover by itself!

I've been spending a lot of time in the archives here:  https://www.thatjeffsmith.com/

You can color your results:  Choosing you’re output format with SQLFORMAT
https://mikesmithers.wordpress.com/2015/04/12/sqlcl-the-new-sqlplus/

There are still a few things I've not been able to get around yet.  I'm sure I can but I've not found the assistance yet.  Might be an SR someday.
0
 
johnsoneSenior Oracle DBACommented:
My thing has always been speed.  The tool takes too long to open.  I can start up SQL*Plus, run my query and get my answer long before SQL*Developer ever starts.  I guess old habits die hard.
0
 
slightwv (䄆 Netminder) Commented:
>> I can start up SQL*Plus, run my query and get my answer long before SQL*Developer ever starts.

We are both really old dogs that hate new tricks!!!

That is the main reason I hate it as well.  I find SQLCl doesn't take much longer to open than SQL*Plus on a Windows box.  Not sure about any *nix systems.
0
 
johnsoneSenior Oracle DBACommented:
Why learn something new when the old stuff works just fine?  If it ain't broke don't fix it.

I have tried SQL*Developer a few times on Windows and *nix systems.  To me, definitely not worth it.  Takes too long to load and moves too slow.  I know my tables and data, I don't need a GUI to navigate it.  I will admit that if you can put the time in to getting PL/SQL debugging to work, it can be helpful.

I wasn't aware of SQLcl.  The command on a *nix system is simply sql.  Seems a little odd.  It does take a while for it to start up, but not nearly as long as SQL*Developer.  I didn't spend a lot of time looking at it, but I may try it in the future.  The info command on a table is pretty good.  The show all command seems to have a lot less options than SQL*Plus, but that may be a good thing.  It does seem that it runs the glogin.sql, which is helpful, although I have code in there that sets the prompt to include the database name.  Works fine in SQL*Plus, but not SQLcl.  Run the commands individually in SQLcl and it works, but not in the glogin.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.