Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE and csv [merged]
icon6.gif  UTL_FILE and csv [merged] [message #343306] Tue, 26 August 2008 22:08 Go to next message
JuzMe
Messages: 6
Registered: August 2008
Location: Singapore
Junior Member

I have this PL/SQL that I will want to generate a CSV file.


SET SERVEROUTPUT ON;
SET TIMING ON;

DECLARE
    CURSOR C1
    IS 
    select balance from contract ;
    
BEGIN
    FOR i IN C1 LOOP

 IF i.balance <> '0' THEN
      DBMS_OUTPUT.PUT_LINE('Amount is not 0 ');  
[b]I will like to put the statement 'Amount is not 0 ' in a CSV file here[/b]

     else 
     DBMS_OUTPUT.PUT_LINE('Amount is 0 ');  
     END IF;
    
     end loop;
END;
/



How am I suppose to do this?
Re: Generate CSV file [message #343307 is a reply to message #343306] Tue, 26 August 2008 22:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How am I suppose to do this?
Use UTL_FILE.

[Updated on: Tue, 26 August 2008 22:13] by Moderator

Report message to a moderator

Re: Generate CSV file [message #343313 is a reply to message #343306] Tue, 26 August 2008 22:57 Go to previous messageGo to next message
JuzMe
Messages: 6
Registered: August 2008
Location: Singapore
Junior Member

I tried this code:


DECLARE 
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'C:\', 'Output.txt', 'w' );
 
  
 
  utl_file.put_line( l_file, 'String I want to write to the file' );
 
 
  utl_file.fclose( l_file );
END;




However, I encountered and error as follows:


ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 70



I have already make sure the Output.txt file exists in my C drive. What could be the problem?
Re: Generate CSV file [message #343315 is a reply to message #343306] Tue, 26 August 2008 23:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>What could be the problem?

29280, 00000, "invalid directory path"
// *Cause:  A corresponding directory object does not exist.
// *Action: Correct the directory object parameter, or create a corresponding
//          directory object with the CREATE DIRECTORY command.


http://asktom.oracle.com
above contains many fine coding examples/

http://tahiti.oracle.com
above contains complete Oracle documentation set.
UTL FILE saving as csv file [message #343390 is a reply to message #343306] Wed, 27 August 2008 03:05 Go to previous messageGo to next message
JuzMe
Messages: 6
Registered: August 2008
Location: Singapore
Junior Member

I have this line of code:

DECLARE

l_Date DATE := SYSDATE;

BEGIN
 l_file := utl_file.fopen('UTL_TMP_DIR','ZERORISATION_'||l_Date||'.csv', 'w' );
END;



I encountered this error while running my query with the line above :


PLS-00103: Encountered the symbol "L_FILE" when expecting one of the following:
:= . ( @ % ;
The symbol "." was substituted for "L_FILE" to continue.



May I know what was the problem?

Re: UTL FILE saving as csv file [message #343391 is a reply to message #343390] Wed, 27 August 2008 03:12 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Glad to see that you have formatted the post. But you have missed the most important point. Please always copy and paste the actual error. I am not able to reproduce the error. Find my observation.
1 declare
2 l_date date := sysdate;
3 begin
4    l_file := utl_file('TEST','TEST'||l_Date||'.csv','w');
5 end;
6 /
l_file := utl_file('TEST','TEST'||l_Date||'.csv','w');
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00201: identifier 'L_FILE' must be declared
ORA-06550: linie 4, column 1:
PL/SQL: Statement ignored

It clearly states the variable I am using is not declared. So please do the same from next time.

Regards

Raj
Re: UTL FILE saving as csv file [message #343396 is a reply to message #343390] Wed, 27 August 2008 03:22 Go to previous messageGo to next message
JuzMe
Messages: 6
Registered: August 2008
Location: Singapore
Junior Member

Oh my god! I am sorry. Here should be the full exact codes:

DECLARE
CURSOR C3 IS
SELECT distinct status from order; 

l_file utl_file.file_type;
l_Date DATE := SYSDATE;

BEGIN
 l_file := utl_file.fopen('UTL_TMP_DIR','ZERORISATION_'||l_Date||'.csv', 'w' );
    utl_file.put_line( l_file,'Status is '||c3_rec.status);  
     utl_file.fclose( l_file );
END;


Please note: 'UTL_TMP_DIR' is the directory that is inside my system already.
Re: UTL FILE saving as csv file [message #343399 is a reply to message #343396] Wed, 27 August 2008 03:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Where is the error message ? I think you should read the oracle reference manual. Refer this link for more information.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

Regards

Raj

[Edit:] To me it looks like a re-post.

http://www.orafaq.com/forum/t/123921/94420/

[Updated on: Wed, 27 August 2008 03:37]

Report message to a moderator

Re: UTL FILE saving as csv file [message #343402 is a reply to message #343399] Wed, 27 August 2008 03:42 Go to previous messageGo to next message
JuzMe
Messages: 6
Registered: August 2008
Location: Singapore
Junior Member

This error is caused becoz I changed the value in L_FILE. Hence, it is a new question.

The error is caused by this line of codes:


 l_file := utl_file.fopen('UTL_TMP_DIR','ZERORISATION_'||l_Date||'.csv', 'w' );



Here is the error:
PLS-00103: Encountered the symbol "L_FILE" when expecting one of the following:
:= . ( @ % ;
The symbol "." was substituted for "L_FILE" to continue.



Re: UTL FILE saving as csv file [message #343413 is a reply to message #343402] Wed, 27 August 2008 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, use SQL*Plus and copy and paste the session, the whole session.

Regards
Michel
Re: UTL FILE saving as csv file [message #343509 is a reply to message #343396] Wed, 27 August 2008 07:55 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
JuzMe wrote on Wed, 27 August 2008 04:22
Oh my god! I am sorry. Here should be the full exact codes:


You declare a cursor but never use it. Your code is getting dangerously close a trial and error approach to a problem. It may be time to read the documentation before attempting to code this problem.
Re: UTL_FILE and csv [merged] [message #343597 is a reply to message #343306] Wed, 27 August 2008 10:58 Go to previous message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
Quote:

I have this PL/SQL that I will want to generate a CSV file.
...
How am I suppose to do this?



You could, alternatively, use the SPOOL sqlplus command to spool the output of the anonymous PL/SQL block to a csv file in your client machine.

test@ora>
test@ora>
test@ora> -- check the content of the script
test@ora>
test@ora> host type c:\test.sql
begin
  for rec in (select empno, ename, job, mgr from emp)
  loop
    dbms_output.put_line(rec.empno||','||rec.ename||','||rec.job||','||rec.mgr);
  end loop;
end;
/

test@ora>
test@ora> set feedback off heading off pages 0 termout off
test@ora>
test@ora> -- spool the csv file
test@ora>
test@ora> spool c:\test.csv
test@ora> @c:\test.sql
test@ora> spool off
test@ora>
test@ora> -- now check the content of the csv file
test@ora>
test@ora> host type c:\test.csv
test@ora> @c:\test.sql
7369,SMITH,CLERK,7902
7499,ALLEN,SALESMAN,7698
7521,WARD,SALESMAN,7698
7566,JONES,MANAGER,7839
7654,MARTIN,SALESMAN,7698
7698,BLAKE,MANAGER,7839
7782,CLARK,MANAGER,7839
7788,SCOTT,ANALYST,7566
7839,KING,PRESIDENT,
7844,TURNER,SALESMAN,7698
7876,ADAMS,CLERK,7788
7900,JAMES,CLERK,7698
7902,FORD,ANALYST,7566
7934,MILLER,CLERK,7782
test@ora> spool off

test@ora>
test@ora>


tyler_durden
Previous Topic: bulk collect into nested tables
Next Topic: Procedure to calculate distance
Goto Forum:
  


Current Time: Fri Dec 09 00:06:32 CST 2016

Total time taken to generate the page: 0.12801 seconds