6 Replies Latest reply: Dec 10, 2012 4:12 PM by 978204 RSS

    How to synchronously copy an external UNIX file?

    978204
      We are running Oracle Database 11g Enterprise Edition Release 11.2.0.3.0.
      We need to do the following:

      FOR C IN ( SELECT FILENAME FROM FILETABLE )
      LOOP
      COPY_FILE(FILE_IN => C.FILENAME);
      OTHER_PROCESS((FILE_IN => C.FILENAME);
      END LOOP;

      The COPY_FILE procedure will copy a file from one UNIX directory [which the Oracle User does NOT have access to] to another UNIX directory [which the Oracle User does have access to].

      I know we could create a JAVA function to accomplish this, but we want to keep all of the code in PL/SQL, if possible.

      Another idea was to use the UTL_FILE.FCOPY procedure:

      UTL_FILE.FCOPY (
      SRC_LOCATION => 'DIR1',
      SRC_FILENAME => 'MYFILE',
      DEST_LOCATION => 'DIR2',
      DEST_FILENAME => 'MYFILE'
      );

      But his requires that the Oracle User have READ permission to DIR1 [which on our server, it does not].

      Lastly, we considered setting up a Credential that will be granted READ access to DIR1. I know how to use a Credential with a DBMS_SCHEDULER to create an EXEXCUTABLE JOB, but that job will be run ASYNCHRONOUSLY.

      We need a solution that can do the copy SYNCHRONOUSLY.

      Does anyone have any ideas how we could do this???
        • 1. Re: How to synchronously copy an external UNIX file?
          JustinCave
          The requirement seems confusing to me...

          In order to copy a file from A to B, the code that does the copy needs to have read access to A and write access to B. If you want the code doing the copying to run inside the database (whether via PL/SQL or Java), the Oracle operating system user would need read access on A. Why is it that you can't grant read access on A? You do, after all, want the Oracle user to be able to copy the file from A to B which means that you do want the Oracle user to be able to read A.

          You can, of course, use DBMS_SCHEDULER to run an operating system job and then wait for that job to finish before continuing (turning an asynchronous process into a synchronous one). You could create a Java stored procedure that calls an operating system shell script that runs as a different user and does the copy. But neither of these seem as sensible as granting Oracle the ability to read from A.

          Justin
          • 2. Re: How to synchronously copy an external UNIX file?
            6363
            To copy a Unix file from one directory to another, a database is probably the most unsuitable tool to choose.

            Whatever tool is used will require read access on the source file, and write access to the target destination.
            • 3. Re: How to synchronously copy an external UNIX file?
              odie_63
              Lastly, we considered setting up a Credential that will be granted READ access to DIR1. I know how to use a Credential with a DBMS_SCHEDULER to create an EXEXCUTABLE JOB, but that job will be run ASYNCHRONOUSLY.

              We need a solution that can do the copy SYNCHRONOUSLY.
              The RUN_JOB procedure can do that, with use_current_session = TRUE :
              http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#i1013568

              You may be interested in the DBMS_FILE_TRANSFER package too : Copying Files Using the Database Server
              • 4. Re: How to synchronously copy an external UNIX file?
                978204
                We did try using the USER_CURRENT_SESSION=TRUE parm on the DBMS_SCHEDULER.RUN_JOB procedure. Problem is the procedure that wraps the cursor loop is run via a job itself.

                When you try to run a job synchronously using the DBMS_SCHEDULER.RUN_JOB procedure and USER_CURRENT_SESSION=TRUE from within a job you get the following error.

                ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted.
                • 5. Re: How to synchronously copy an external UNIX file?
                  978204
                  Thank you to everyone for your input. I believe we finally found a working solution.

                  First, we create a disabled job to perform the CP command and attach a CREDENTIAL that has access to both directories:

                  begin
                  dbms_scheduler.create_job (
                  job_name => 'JOB_COPY_FILE',
                  job_type => 'EXECUTABLE',
                  job_action => '/bin/cp',
                  number_of_arguments => 2,
                  enabled => false,
                  auto_drop => false,
                  credential_name => 'COPY_FILE_CREDENTIAL'
                  );

                  dbms_scheduler.set_job_argument_value(
                  job_name => 'JOB_COPY_FILE',
                  argument_position => 1,
                  argument_value => '/yourdirectory/yourfile'
                  );
                  dbms_scheduler.set_job_argument_value(
                  job_name => 'JOB_COPY_FILE',
                  argument_position => 2,
                  argument_value => '/mydirectory/myfile'
                  );

                  end;

                  Then, the COPY_FILE() procedure in the cursor loop looks like this:


                  procedure COPY_FILE( file_in in varchar2 ) as
                  begin
                  -- Set Copy Job Parameters
                  dbms_scheduler.set_job_argument_value (
                  job_name => 'JOB_COPY_FILE',
                  argument_position => 1,
                  argument_value => '/yourdirectory/'||file_in
                  );
                  dbms_scheduler.set_job_argument_value (
                  job_name => 'JOB_COPY_FILE',
                  argument_position => 2,
                  argument_value => '/mydirectory/'||file_in
                  );
                  -- Run Copy Job Synchronously
                  dbms_scheduler.run_job (
                  job_name => 'JOB_COPY_FILE',
                  use_current_session => true
                  );
                  end COPY_FILE;

                  The Copy Job is run synchronously and uses the access specified in the COPY_FILE_CREDENTIAL to perform the UNIX CP command. The Database User does not have access to the "from" directory.
                  • 6. Re: How to synchronously copy an external UNIX file?
                    978204
                    I failed to mention that the code containing the cursor loop is inside a scheduled job [ _not_ using the COPY_CREDENTIAL] ... so we were able to RUN an existing JOB synchronously from within another JOB.