How to include a huge SQL statement into UTL_FILE procedure?

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.

Solution

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.