Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE and csv [merged]
UTL_FILE and csv [merged] [message #343306] |
Tue, 26 August 2008 22:08  |
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 #343313 is a reply to message #343306] |
Tue, 26 August 2008 22:57   |
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?
|
|
|
|
UTL FILE saving as csv file [message #343390 is a reply to message #343306] |
Wed, 27 August 2008 03:05   |
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   |
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   |
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 #343402 is a reply to message #343399] |
Wed, 27 August 2008 03:42   |
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 #343509 is a reply to message #343396] |
Wed, 27 August 2008 07:55   |
joy_division
Messages: 4963 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  |
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
|
|
|
Goto Forum:
Current Time: Fri Feb 07 00:01:19 CST 2025
|