How to include a huge SQL statement into UTL_FILE procedure?

Question

I wrote a procedure using UTL_FILE:

CREATE OR REPLACE PROCEDURE UTL_CREATE_FILE 
(
       output_file in  UTL_FILE.file_type,
       log_file    in UTL_FILE.file_type,
       filename    in VARCHAR2 (64),
       ddate       in VARCHAR2 (19),
       sep         in NVARCHAR2 (3)

)


  IS

   BEGIN


           sep := Chr(9);
           ddate := TO_CHAR (SYSDATE, 'YYYYMMDD');
           filename := 'EXT' || ddate || '.dat';
           output_file := UTL_FILE.fopen ('C:/home/S/', filename, 'w', 32000);
           log_file := UTL_FILE.fopen ('C:/home/S/', 'WEEKLY.log', 'a', 32000);

           UTL_FILE.put_line (log_file, TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || 'Started with file ' || filename);

   select 'HUGE SQL STATEMENT'|| sep || 'Anykey' as OUTLINE from DUAL;
          UTL_FILE.put_line (output_file, OUTLINE);
          UTL_FILE.fclose (output_file);
          UTL_FILE.put_line (log_file, TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || 'Finished for file ' || filename);
          UTL_FILE.fclose (log_file);
  END;

But Toad returns Warning: compiled but with compilation errors.

Could anybody help me?

As a result I would like to receive a EXT.DAT (and logs) in C:/home/S/ directory. Thank you in advance.

What have you done, to try and work out what the problem is? Did Toad tell you what line contained the problem or any further details? — Burhan Ali, Apr 11 '12 at 13:19

score 1 · Accepted Answer · edited May 23 '17 at 12:11

TOAD should give you the compilation errors - they are probably on a separate tab (it's been a while since I used that particular IDE).

However, it easy to spot one bloomer: we cannot assign values to parameters defined in IN mode. The purpose of such parameters is that the calling program assigns their values.

However, in this case I think you need to assign ddate and filename, so you should move them out of the procedure's signature and into its declaration section.

sep I would keep as a parameter but give it a default value.

Bear in mind that SQL limits us to 4000 characters in a column . So if 'HUGE SQL STATEMENT' exceeds 3993 characters your code will hurl a runtime error.

If you're making these sorts of errors you're probably not up-to-speed with the intricacies of writing files from PL/SQL. I suggest you read this previous answer of mine and also this one regarding this topic.

score 0 · Answer 2 · answered Apr 12 '12 at 14:29

You should be able to append this to the end of your script to get the errors (I don't use TOAD, but I'd expect it to support it). It goes after the last end;.

/
show errors;

The compilation errors that stand out to me -

The parameters are being assigned to. This is illegal as "in" parameters. They don't seem to be used for input, so they should probably be removed from the signature. If this is a code snippet and they do provi

How to include a huge SQL statement into UTL_FILE procedure?

2 Answers2