3 Replies Latest reply: Dec 3, 2013 10:39 AM by user13283765 RSS

    oracle 11gR2 dbms_scheduler.put_file not working

    user13283765

      Hello all,

      I have not been able to find any good example of using the dbsm_scheduler.put_file function.

      I've tried the below and I get errors for wrong number of arguments of invalid types.

      The error seem to rotate around the file_contents. However, this function should be an alternative to UTL_FILE if you have no database on the remote server.

       

      Thanks in advance for any assistance.

       

      Jan S.

      SET SERVEROUTPUT
      vClob clob;
      BEGIN
         
        --Put the files on the remote server
        
        DBMS_SCHEDULER.PUT_FILE (
            destination_file => '/home/oracle/scripts/execute_sql.sh',
            destination_host => '10.0.30.174:1500',
            credential_name =>  'ORACLECREDENTIALS',
            file_contents => vClob,
            source_file_name => '/home/oracle/scripts/execute_sql.sh',
            source_directory_object => 'SCRIPT_REPO');
        
      END;
      
      
        • 1. Re: oracle 11gR2 dbms_scheduler.put_file not working
          HDeiby

          Please try to use this format:

          DBMS_SCHEDULER.PUT_FILE (
             destination_file         IN VARCHAR2,
             destination_host         IN VARCHAR2,
             credential_name          IN VARCHAR2,
             source_file_name         IN VARCHAR2,
             source_directory_object  IN VARCHAR2,
             destination_permissions  IN VARCHAR2 DEFAULT NULL);
          • 2. Re: oracle 11gR2 dbms_scheduler.put_file not working
            rp0428

            I have not been able to find any good example of using the dbsm_scheduler.put_file function.

             

             

            I've tried the below and I get errors for wrong number of arguments of invalid types.

             

            So naturally your first reaction was to RTFM about the PUT_FILE Procedure methods that were available?

            http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#CIHEHDHA

            PUT_FILE Procedure

            This procedure saves a file to the operating system file system of a specified remote host or of the local computer. It differs from the equivalent UTL_FILE procedure in that it uses a credential and can save files to a remote host that has only a Scheduler agent (and not an Oracle Database) installed.

            Syntax

            DBMS_SCHEDULER.PUT_FILE (
               destination_file         IN VARCHAR2,
               destination_host         IN VARCHAR2,
               credential_name          IN VARCHAR2,
               file_contents            IN {BLOB|CLOB},
               destination_permissions  IN VARCHAR2 DEFAULT NULL); 
            DBMS_SCHEDULER.PUT_FILE (
               destination_file         IN VARCHAR2,
               destination_host         IN VARCHAR2,
               credential_name          IN VARCHAR2,
               source_file_name         IN VARCHAR2,
               source_directory_object  IN VARCHAR2,
               destination_permissions  IN VARCHAR2 DEFAULT NULL); 

             

            And then, clever person that you are I bet the next thing you did was compare those two method declarations above with what you are using:

            DBMS_SCHEDULER.PUT_FILE (    

            •       destination_file => '/home/oracle/scripts/execute_sql.sh',  
            •       destination_host => '10.0.30.174:1500',  
            •       credential_name =>  'ORACLECREDENTIALS',  
            •       file_contents => vClob,  
            •       source_file_name => '/home/oracle/scripts/execute_sql.sh',  
            •       source_directory_object => 'SCRIPT_REPO');  

            Uh-oh - Houston, we have a problem. Was it then that you realized what this really means?

            I get errors for wrong number of arguments of invalid types.

            I don't see any method that uses the six arguments of the type that you provided, do you?

            • 3. Re: oracle 11gR2 dbms_scheduler.put_file not working
              user13283765

              Hello All,

               

              The error was the the way I was passing the parameters.

               

              BEGIN
                 
                --Put the files on the remote server using scheduler agent
                
                DBMS_SCHEDULER.PUT_FILE ('/home/oracle/scripts/execute_sql.sh',
                                         '10.0.30.174:1500',
                                         'REMOTECREDENTIALS',
                                         'execute_sql.sh',
                                         'SCRIPT_REPO');
              END;
              /