?
Solved

UTL_FILE ORACLE Problem

Posted on 2010-03-30
12
Medium Priority
?
3,225 Views
Last Modified: 2013-12-07
I keep getting an error when trying to create this UTL_FILE procedure.

CREATE OR REPLACE PROCEDURE AMCAT_REPORT_F_M_D_PROC
AS
DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'FILE_LOCATION\Email_notification\', 'TEST.csv', 'W' );
  FOR x IN (SELECT * FROM S_PUB WHERE pub_cnfrm_rcpt_flg = 'F' or pub_cnfrm_rcpt_flg = 'M' or pub_cnfrm_rcpt_flg = 'D')
  LOOP
    utl_file.put_line(l_file, x.line);
  END LOOP;
  utl_file.fclose_all;
END AMCAT_REPORT_F_M_D_PROC;

Error(3,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

Error(12,28): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge
0
Comment
Question by:lulubell-b
  • 6
  • 6
12 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29126185
First error tells you the issue:  In procedures, you don't use DECLARE.  This is only in anonymous PL/SQL blocks.

I also think there's a problem with the 'PATH' in the fopen call.  I think this needs to just the the Oracle Directory with no additions subdirectory paths.
0
 

Author Comment

by:lulubell-b
ID: 29126739
So is it just the ORACLE_HOME PATH, our the directory of the file, because now I'm recieving these errors

Error(3,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.
Error(12,28): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge
Error(8,5): PL/SQL: Statement ignored
Error(8,33): PLS-00302: component 'LINE' must be declared
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29127323
The DELCARE error is a syntax problem

Change it to:
CREATE OR REPLACE PROCEDURE AMCAT_REPORT_F_M_D_PROC
AS
  l_file utl_file.file_type;
BEGIN
...

>>So is it just the ORACLE_HOME PATH, our the directory of the file
I'm not sure I understand.  Did you create a directory within the database with the  create directory command?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 29132900
Now that I have a chance to play with this.  Check out t he following.
create or replace directory myTestDIR as 'c:\';

CREATE OR REPLACE PROCEDURE junkproc
AS
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'MYTESTDIR', 'TEST.csv', 'W' );
  FOR x IN (SELECT dummy col1, sysdate col2 FROM dual)
  LOOP
    utl_file.put_line(l_file, x.col1 || ',' || x.col2);
  END LOOP;
  utl_file.fclose_all;
END junkproc;
/

show errors

Open in new window

0
 

Author Comment

by:lulubell-b
ID: 29791452
Awesome, I got it to work perfectly. One more thing before I go, How do you place a header column into the csv file?

Thanks
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29792840
I suggest just hard-coding the values outside the loop:

 utl_file.put_line('heading1,heading2,heading3');
0
 

Author Comment

by:lulubell-b
ID: 29793452
Its before the end loop,


FOR x IN (SELECT PUB_ID col1, CNFRM_RCPT_FLG col2, CHNG_CD col3 MOD_C_DT col4, CAT col5,

PRIM_TYP col6 FROM B WHERE cnfrm_rcpt_flg = 'F' or cnfrm_rcpt_flg = 'M' or cnfrm_rcpt_flg = 'D')
 
LOOP
          utl_file.put_line(l_file, x.col1, x.col2, x.col3, x.col4, x.col5, x.col6);
END LOOP;



Im also recieving the following error when I add more than two columns. Do  I have to perform some conversions?

Error(9,5): PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

Thank you
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29795151
>>Its before the end loop,

I'm not sure I understand what you mean by this.  Can you explain a little more?

>>Error(9,5): PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'


put_line can only output one string.  You need to concatinate the columns.

Check back up in my code:
utl_file.put_line(l_file, x.col1 || ',' || x.col2);


0
 

Author Comment

by:lulubell-b
ID: 29797084
The concatination worked, I didnt realize the put_line only output one string



x.PUBID || x.RECEIPT_FLAG || x.CHANGE_CODE || x.MODIFIED_DATE || x.AMCTID || x.PRIM_TYPE


Now, I would like to add column header

ID NUMBER (PUBID)
------------
1000
....so on
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29797320
http:#29792840

just write it out w/o concatinating.
0
 

Author Comment

by:lulubell-b
ID: 29800231
Everything worked Thank you very much

Have a nice day
0
 

Author Closing Comment

by:lulubell-b
ID: 31709141
awesome just great explanation
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question