UTL_FILE ORACLE Problem

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
lulubell-bAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
lulubell-bAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lulubell-bAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
I suggest just hard-coding the values outside the loop:

 utl_file.put_line('heading1,heading2,heading3');
0
lulubell-bAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
lulubell-bAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
http:#29792840

just write it out w/o concatinating.
0
lulubell-bAuthor Commented:
Everything worked Thank you very much

Have a nice day
0
lulubell-bAuthor Commented:
awesome just great explanation
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.