You Asked

Hi There,

I have a PIPELINED function which retrieves me filenames which I feed to UTL_FILE.FCOPY like below:

DECLARE
   PROCEDURE copy_var_templates (p_var_report_name st_string)
   IS
      lkv_template_dir   CONSTANT st_string := 'TEMPLATES';
      lkv_working_dir    CONSTANT st_string := 'WORKING';
   BEGIN
      --Before generating PDF, We need to move the template files to working directory:
      FOR i
         IN (SELECT COLUMN_VALUE template_name
               FROM TABLE (fn_templates)) --This Pipelined SQL spits the filenames
      LOOP
         UTL_FILE.fcopy (
            lkv_template_dir,
            i.template_name,
            lkv_working_dir,
            replace (lv_file_name, 'TEMPLATE', var_file_name));
      END LOOP;
   END;
BEGIN
   copy_var_templates ('BUSINESS_REPORT');
END;
/

--The Above code throws the FILE READ Error.

--Strangely Enough, When I try to do a simple UTL_FILE.FCOPY like below it just works:

BEGIN
utl_file.fcopy ('TEMPLATES', 'TEMPLATE_rep.svg', 'DET_WORKING', 'BUSINESS_REPORT_rep.svg');
END;
/

Is there any explanation on why this happens.

Thanks,

Regards,
Praveen Kumar.

and Connor said...

Your code is sound, eg

SQL> create or replace directory TEMP as 'c:\temp';

Directory created.

SQL> create or replace directory TMP as 'c:\tmp';

Directory created.

SQL>
SQL> host "echo Hello > c:\temp\file1.dat"

SQL> host "echo Hello > c:\temp\file2.dat"

SQL> host "echo Hello > c:\temp\file3.dat"

SQL> host "echo Hello > c:\temp\file4.dat"

SQL>
SQL>
SQL> DECLARE
  2     PROCEDURE copy_var_templates
  3     IS
  4        lkv_template_dir   CONSTANT varchar2(20) := 'TEMP';
  5        lkv_working_dir    CONSTANT varchar2(20) := 'TMP';
  6        fn_templates sys.odcivarchar2list := sys.odcivarchar2list('file1.dat','file2.dat','file3.dat','file4.dat');
  7     BEGIN
  8        FOR i
  9           IN (SELECT COLUMN_VALUE template_name
 10                 FROM TABLE (fn_templates)) --This Pipelined SQL spits the filenames
 11        LOOP
 12           UTL_FILE.fcopy (
 13              lkv_template_dir,
 14              i.template_name,
 15              lkv_working_dir,
 16              replace (i.template_name, 'file', 'newfile'));
 17        END LOOP;
 18     END;
 19  BEGIN
 20     copy_var_templates;
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL> host dir c:\tmp\new*
 Volume in drive C is OS
 Volume Serial Number is 66D0-4AB5

 Directory of c:\tmp

15/06/2018  01:25 PM                 8 newfile1.dat
15/06/2018  01:25 PM                 8 newfile2.dat
15/06/2018  01:25 PM                 8 newfile3.dat
15/06/2018  01:25 PM                 8 newfile4.dat
               4 File(s)             32 bytes
               0 Dir(s)  84,435,816,448 bytes free

SQL>

so I can assume is that you are missing some permissions on the file, or some other logic error in terms of the file names etc. We'd need to see a more complete test case.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Looks Ok, But still how does the standalone call works

Praveen Kumar, June 15, 2018 - 7:22 pm UTC

Thanks Connor for checking this out.

Sure I need to come up with a more complete test case, but to be honest I've tried all the file copies using stand alone and they worked without any problems, but this loop method never worked out as its working for you.

I'll share another example soon, But Thanks for your help.

June 16, 2018 - 1:20 am UTC

And you're sure its a file read error? I was just looking at ways where an error could error only in PLSQL and there's the familiar "privileges through roles" situation where anonymous blocks work but procedures don't eg

SQL>  DECLARE
  2         PROCEDURE copy_var_templates
  3         IS
  4            lkv_template_dir   CONSTANT varchar2(20) := 'TEMP';
  5            lkv_working_dir    CONSTANT varchar2(20) := 'TMP';
  6            fn_templates sys.odcivarchar2list := sys.odcivarchar2list('file1.dat','file2.dat','file3.dat','file4.dat');
  7         BEGIN
  8            FOR i
  9               IN (SELECT COLUMN_VALUE template_name
 10                    FROM TABLE (fn_templates)) --This Pipelined SQL spits the filenames
 11           LOOP
 12              UTL_FILE.fcopy (
 13                 lkv_template_dir,
 14                 i.template_name,
 15                 lkv_working_dir,
 16                 replace (i.template_name, 'file', 'newfile'));
 17           END LOOP;
 18        END;
 19     BEGIN
 20        copy_var_templates;
 21     END;
 22     /

PL/SQL procedure successfully completed.

SQL>
SQL>    create or replace procedure P is
  2            lkv_template_dir   CONSTANT varchar2(20) := 'TEMP';
  3            lkv_working_dir    CONSTANT varchar2(20) := 'TMP';
  4            fn_templates sys.odcivarchar2list := sys.odcivarchar2list('file1.dat','file2.dat','file3.dat','file4.dat');
  5         BEGIN
  6            FOR i
  7               IN (SELECT COLUMN_VALUE template_name
  8                    FROM TABLE (fn_templates)) --This Pipelined SQL spits the filenames
  9           LOOP
 10              UTL_FILE.fcopy (
 11                 lkv_template_dir,
 12                 i.template_name,
 13                 lkv_working_dir,
 14                 replace (i.template_name, 'file', 'newfile'));
 15           END LOOP;
 16        END;
 17  /

Procedure created.

SQL> exec p
BEGIN p; END;

*
ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 270
ORA-06512: at "SYS.UTL_FILE", line 1243
ORA-06512: at "SCOTT.P", line 10
ORA-06512: at "SCOTT.P", line 10
ORA-06512: at line 1

because the directory privileges here were granted via a role.

Marcus, June 16, 2018 - 9:20 am UTC

We had a similar problem recently where files seemed to be locked randomly. We never found the real error but a workaround:

Copy a Text File From One Directory To Another Using Stored Procedure? (Doc ID 317338.1)

In short: while fcopy officially supports copying binary files it is better to copy them in chunks as raw data

June 16, 2018 - 11:26 am UTC

Thanks for the additional info.

Here's a solution from a slightly different perspective ... use the OS level combined with the scheduler, eg

SQL> host cat c:\temp\runscript.cmd
copy %1 %2

SQL> begin
  2    dbms_credential.create_credential(
  3      credential_name => 'my_account',
  4      username        => 'oracle',
  5      password        => 'oracle'
  6    );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> declare
  2    l_job_name varchar2(30);
  3    l_script   varchar2(32767);
  4  begin
  5    l_job_name := dbms_scheduler.generate_job_name;
  6    l_script := 'C:\temp\runscript.cmd c:\temp\file1.dat c:\tmp\newfile1.dat';
  7
  8    dbms_scheduler.create_job(
  9      job_name        => l_job_name,
 10      job_type        => 'EXTERNAL_SCRIPT',
 11      job_action      => l_script,
 12      credential_name => 'MY_ACCOUNT',
 13      enabled         => true
 14    );
 15  end;
 16  /

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Looks Ok, But still how does the standalone call works

More to Explore

PL/SQL demos

PL/SQL docs

UTL_FILE.FCOPY not working in FOR LOOP <file read error>

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Looks Ok, But still how does the standalone call works

More to Explore

PL/SQL demos

PL/SQL docs