1 2 Previous Next 21 Replies Latest reply: Nov 20, 2012 11:59 PM by user12879396 RSS

    ORA-22288: file or LOB operation GETLENGTH failed

    user12879396
      I am using the following procedure for email the with attachment. it give the error, while I have check the directory rights is OK, also check the following thread
      Error Message is ORA-22288: file or LOB operation GETLENGTH failed
      but no result,

      I do this on clone working fine, but production not working fine while I found one thing that the file generate in temp folder of clone while in Production did not generate.


      CREATE OR REPLACE PROCEDURE APPS.mail_files (p_from_name VARCHAR2,
      p_to_name VARCHAR2,
      p_subject VARCHAR2,
      p_message VARCHAR2,
      p_oracle_directory VARCHAR2,
      p_binary_file VARCHAR2)
      IS
      -- Example procedure to send a mail with an in line attachment
      -- encoded in Base64
      -- this procedure uses the following nested functions:
      -- binary_attachment - calls:
      -- begin_attachment - calls:
      -- write_boundary
      -- write_mime_header
      --
      -- end attachment - calls;
      -- write_boundary

      -- change the following line to refer to your mail server
      v_smtp_server VARCHAR2(1000) := 'mail.company.com';
      v_smtp_server_port NUMBER := 25;
      v_directory_name VARCHAR2(1000) ;
      v_file_name VARCHAR2(1000);
      v_mesg VARCHAR2(32767);
      v_conn UTL_SMTP.CONNECTION;

      --

      PROCEDURE write_mime_header(p_conn in out nocopy utl_smtp.connection,
      p_name in varchar2,
      p_value in varchar2)
      IS
      BEGIN
      UTL_SMTP.WRITE_RAW_DATA(
      p_conn,
      UTL_RAW.CAST_TO_RAW( p_name || ': ' || p_value || UTL_TCP.CRLF)
      );
      END write_mime_header;

      --

      PROCEDURE write_boundary(p_conn IN OUT NOCOPY UTL_SMTP.CONNECTION,
      p_last IN BOOLEAN DEFAULT false)
      IS
      BEGIN
      IF (p_last) THEN
      UTL_SMTP.WRITE_DATA(p_conn, '--DMW.Boundary.605592468--'||UTL_TCP.CRLF);
      ELSE
      UTL_SMTP.WRITE_DATA(p_conn, '--DMW.Boundary.605592468'||UTL_TCP.CRLF);
      END IF;
      END write_boundary;

      --

      PROCEDURE end_attachment(p_conn IN OUT NOCOPY UTL_SMTP.CONNECTION,
      p_last IN BOOLEAN DEFAULT TRUE)
      IS
      BEGIN
      UTL_SMTP.WRITE_DATA(p_conn, UTL_TCP.CRLF);
      IF (p_last) THEN
      write_boundary(p_conn, p_last);
      END IF;
      END end_attachment;

      --

      PROCEDURE begin_attachment(p_conn IN OUT NOCOPY UTL_SMTP.CONNECTION,
      p_mime_type IN VARCHAR2 DEFAULT 'text/plain',
      p_inline IN BOOLEAN DEFAULT false,
      p_filename IN VARCHAR2 DEFAULT null,
      p_transfer_enc in VARCHAR2 DEFAULT null)
      IS
      BEGIN
      write_boundary(p_conn);
      IF (p_transfer_enc IS NOT NULL) THEN
      write_mime_header(p_conn, 'Content-Transfer-Encoding',p_transfer_enc);
      END IF;
      write_mime_header(p_conn, 'Content-Type', p_mime_type);
      IF (p_filename IS NOT NULL) THEN
      IF (p_inline) THEN
      write_mime_header(
      p_conn,
      'Content-Disposition', 'inline; filename="' || p_filename || '"'
      );
      ELSE
      write_mime_header(
      p_conn,
      'Content-Disposition', 'attachment; filename="' || p_filename || '"'
      );
      END IF;
      END IF;
      UTL_SMTP.WRITE_DATA(p_conn, UTL_TCP.CRLF);
      END begin_attachment;

      --

      PROCEDURE binary_attachment(p_conn IN OUT UTL_SMTP.CONNECTION,
      p_file_name IN VARCHAR2,
      p_mime_type in VARCHAR2)
      IS
      c_max_line_width CONSTANT PLS_INTEGER DEFAULT 54;
      v_amt BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */
      v_bfile BFILE;
      v_file_length PLS_INTEGER;
      v_buf RAW(2100);
      v_modulo PLS_INTEGER;
      v_pieces PLS_INTEGER;
      v_file_pos pls_integer := 1;
      BEGIN
      begin_attachment(
      p_conn => p_conn,
      p_mime_type => p_mime_type,
      p_inline => TRUE,
      p_filename => p_file_name,
      p_transfer_enc => 'base64');
      BEGIN
      v_bfile := BFILENAME(p_oracle_directory, p_file_name);
      -- Get the size of the file to be attached
      v_file_length := DBMS_LOB.GETLENGTH(v_bfile);
      -- Calculate the number of pieces the file will be split up into
      v_pieces := TRUNC(v_file_length / v_amt);
      -- Calculate the remainder after dividing the file into v_amt chunks
      v_modulo := MOD(v_file_length, v_amt);
      IF (v_modulo <> 0) THEN
      -- Since the file does not devide equally
      -- we need to go round the loop an extra time to write the last
      -- few bytes - so add one to the loop counter.
      v_pieces := v_pieces + 1;
      END IF;
      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
      FOR i IN 1 .. v_pieces LOOP
      -- we can read at the beginning of the loop as we have already calculated
      -- how many iterations we will take and so do not need to check
      -- end of file inside the loop.
      v_buf := NULL;
      DBMS_LOB.READ(v_bfile, v_amt, v_file_pos, v_buf);
      v_file_pos := I * v_amt + 1;
      UTL_SMTP.WRITE_RAW_DATA(p_conn, UTL_ENCODE.BASE64_ENCODE(v_buf));
      END LOOP;
      END;
      DBMS_LOB.FILECLOSE(v_bfile);
      end_attachment(p_conn => p_conn);
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      end_attachment(p_conn => p_conn);
      DBMS_LOB.FILECLOSE(v_bfile);
      END binary_attachment;

      --
      -- Main Routine
      --
      BEGIN
      --
      -- Connect and set up header information:
      --
      v_conn:= UTL_SMTP.OPEN_CONNECTION( v_smtp_server, v_smtp_server_port );
      UTL_SMTP.HELO( v_conn, v_smtp_server );
      UTL_SMTP.MAIL( v_conn, p_from_name );
      UTL_SMTP.RCPT( v_conn, p_to_name );
      UTL_SMTP.OPEN_DATA ( v_conn );
      UTL_SMTP.WRITE_DATA(v_conn, 'Subject: '||p_subject||UTL_TCP.CRLF);
      --
      v_mesg:= 'Content-Transfer-Encoding: 7bit' || UTL_TCP.CRLF ||
      'Content-Type: multipart/mixed;boundary="DMW.Boundary.605592468"' || UTL_TCP.CRLF ||
      'Mime-Version: 1.0' || UTL_TCP.CRLF ||
      '--DMW.Boundary.605592468' || UTL_TCP.CRLF ||
      'Content-Transfer-Encoding: binary'||UTL_TCP.CRLF||
      'Content-Type: text/plain' ||UTL_TCP.CRLF ||
      UTL_TCP.CRLF || p_message || UTL_TCP.CRLF ;
      --
      UTL_SMTP.write_data(v_conn, 'To: ' || p_to_name || UTL_TCP.crlf);
      UTL_SMTP.WRITE_RAW_DATA ( v_conn, UTL_RAW.CAST_TO_RAW(v_mesg) );
      --
      -- Add the Attachment
      --
      binary_attachment(
      p_conn => v_conn,
      p_file_name => p_binary_file,
      -- Modify the mime type at the beginning of this line depending
      -- on the type of file being loaded.
      p_mime_type => 'text/plain; name="'||p_binary_file||'"'
      );
      --
      -- Send the email
      --
      UTL_SMTP.CLOSE_DATA( v_conn );
      UTL_SMTP.QUIT( v_conn );
      END;
      /

      Error
      ORA-22288: file or LOB operation GETLENGTH failed
      No such file or directory
      ORA-06512: at "SYS.DBMS_LOB", line 678
      ORA-06512: at "APPS.MAIL_FILES", line 122
      ORA-06512: at "APPS.MAIL_FILES", line 179
      ORA-06512: at line 2
        • 1. Re: ORA-22288: file or LOB operation GETLENGTH failed
          Solomon Yakobson
          I don't see where p_oracle_directory is assigned.

          SY.
          • 2. Re: ORA-22288: file or LOB operation GETLENGTH failed
            user12879396
            /oracle/test/inst/apps/erp-server/logs/appl/conc/out


            CREATE OR REPLACE DIRECTORY
            DIR AS
            '/oracle/test/inst/apps/erp-server/logs/appl/conc/out';

            GRANT EXECUTE, READ, WRITE ON DIRECTORY DIR TO APPS WITH GRANT OPTION;

            GRANT READ ON DIRECTORY DIR TO PUBLIC;

            GRANT EXECUTE, READ, WRITE ON DIRECTORY DIR TO SYS;
            • 3. Re: ORA-22288: file or LOB operation GETLENGTH failed
              Solomon Yakobson
              It is not what I am asking. Your main code calls:
              binary_attachment(
              p_conn => v_conn,
              p_file_name => p_binary_file,
              -- Modify the mime type at the beginning of this line depending
              -- on the type of file being loaded.
              p_mime_type => 'text/plain; name="'||p_binary_file||'"'
              );
              And procedure binary_attachment tries to execute:
              v_bfile := BFILENAME(p_oracle_directory, p_file_name);
              while p_oracle_directory isn't assigned.

              SY.
              • 4. Re: ORA-22288: file or LOB operation GETLENGTH failed
                user12879396
                solomon this procedure work fine in clone but not work in production.
                • 5. Re: ORA-22288: file or LOB operation GETLENGTH failed
                  sb92075
                  user12879396 wrote:
                  solomon this procedure work fine in clone but not work in production.
                  100% devoid of actionable details.

                  is COPY & PASTE broken for you?

                  what error gets reported when is "not work"?
                  • 6. Re: ORA-22288: file or LOB operation GETLENGTH failed
                    user12879396
                    Error
                    ORA-22288: file or LOB operation GETLENGTH failed
                    No such file or directory
                    ORA-06512: at "SYS.DBMS_LOB", line 678
                    ORA-06512: at "APPS.MAIL_FILES", line 122
                    ORA-06512: at "APPS.MAIL_FILES", line 179
                    ORA-06512: at line 2
                    • 7. Re: ORA-22288: file or LOB operation GETLENGTH failed
                      user12879396
                      CREATE OR REPLACE PROCEDURE APPS.mail_files (p_from_name VARCHAR2,
                      p_to_name VARCHAR2,
                      p_subject VARCHAR2,
                      p_message VARCHAR2,
                      p_oracle_directory VARCHAR2,
                      p_binary_file VARCHAR2)
                      IS
                      -- Example procedure to send a mail with an in line attachment
                      -- encoded in Base64
                      -- this procedure uses the following nested functions:
                      -- binary_attachment - calls:
                      -- begin_attachment - calls:
                      -- write_boundary
                      -- write_mime_header
                      --
                      -- end attachment - calls;
                      -- write_boundary

                      -- change the following line to refer to your mail server
                      v_smtp_server VARCHAR2(1000) := 'mail.company.com';
                      v_smtp_server_port NUMBER := 25;
                      v_directory_name VARCHAR2(1000);
                      v_file_name VARCHAR2(1000);
                      v_mesg VARCHAR2(32767);
                      v_conn UTL_SMTP.CONNECTION;

                      --

                      PROCEDURE write_mime_header(p_conn in out nocopy utl_smtp.connection,
                      p_name in varchar2,
                      p_value in varchar2)
                      IS
                      BEGIN
                      UTL_SMTP.WRITE_RAW_DATA(
                      p_conn,
                      UTL_RAW.CAST_TO_RAW( p_name || ': ' || p_value || UTL_TCP.CRLF)
                      );
                      END write_mime_header;

                      --

                      PROCEDURE write_boundary(p_conn IN OUT NOCOPY UTL_SMTP.CONNECTION,
                      p_last IN BOOLEAN DEFAULT false)
                      IS
                      BEGIN
                      IF (p_last) THEN
                      UTL_SMTP.WRITE_DATA(p_conn, '--DMW.Boundary.605592468--'||UTL_TCP.CRLF);
                      ELSE
                      UTL_SMTP.WRITE_DATA(p_conn, '--DMW.Boundary.605592468'||UTL_TCP.CRLF);
                      END IF;
                      END write_boundary;

                      --

                      PROCEDURE end_attachment(p_conn IN OUT NOCOPY UTL_SMTP.CONNECTION,
                      p_last IN BOOLEAN DEFAULT TRUE)
                      IS
                      BEGIN
                      UTL_SMTP.WRITE_DATA(p_conn, UTL_TCP.CRLF);
                      IF (p_last) THEN
                      write_boundary(p_conn, p_last);
                      END IF;
                      END end_attachment;

                      --

                      PROCEDURE begin_attachment(p_conn IN OUT NOCOPY UTL_SMTP.CONNECTION,
                      p_mime_type IN VARCHAR2 DEFAULT 'text/plain',
                      p_inline IN BOOLEAN DEFAULT false,
                      p_filename IN VARCHAR2 DEFAULT null,
                      p_transfer_enc in VARCHAR2 DEFAULT null)
                      IS
                      BEGIN
                      write_boundary(p_conn);
                      IF (p_transfer_enc IS NOT NULL) THEN
                      write_mime_header(p_conn, 'Content-Transfer-Encoding',p_transfer_enc);
                      END IF;
                      write_mime_header(p_conn, 'Content-Type', p_mime_type);
                      IF (p_filename IS NOT NULL) THEN
                      IF (p_inline) THEN
                      write_mime_header(
                      p_conn,
                      'Content-Disposition', 'inline; filename="' || p_filename || '"'
                      );
                      ELSE
                      write_mime_header(
                      p_conn,
                      'Content-Disposition', 'attachment; filename="' || p_filename || '"'
                      );
                      END IF;
                      END IF;
                      UTL_SMTP.WRITE_DATA(p_conn, UTL_TCP.CRLF);
                      END begin_attachment;

                      --

                      PROCEDURE binary_attachment(p_conn IN OUT UTL_SMTP.CONNECTION,
                      p_file_name IN VARCHAR2,
                      p_mime_type in VARCHAR2)
                      IS
                      c_max_line_width CONSTANT PLS_INTEGER DEFAULT 54;
                      v_amt BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */
                      v_bfile BFILE;
                      v_file_length PLS_INTEGER;
                      v_buf RAW(2100);
                      v_modulo PLS_INTEGER;
                      v_pieces PLS_INTEGER;
                      v_file_pos pls_integer := 1;
                      BEGIN
                      begin_attachment(
                      p_conn => p_conn,
                      p_mime_type => p_mime_type,
                      p_inline => TRUE,
                      p_filename => p_file_name,
                      p_transfer_enc => 'base64');
                      BEGIN


                      v_bfile := BFILENAME(p_oracle_directory, p_file_name);

                      -- Get the size of the file to be attached
                      v_file_length := DBMS_LOB.GETLENGTH(v_bfile);

                      -- Calculate the number of pieces the file will be split up into
                      v_pieces := TRUNC(v_file_length / v_amt);
                      -- Calculate the remainder after dividing the file into v_amt chunks
                      v_modulo := MOD(v_file_length, v_amt);
                      IF (v_modulo <> 0) THEN
                      -- Since the file does not devide equally
                      -- we need to go round the loop an extra time to write the last
                      -- few bytes - so add one to the loop counter.
                      v_pieces := v_pieces + 1;
                      END IF;
                      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
                      FOR i IN 1 .. v_pieces LOOP
                      -- we can read at the beginning of the loop as we have already calculated
                      -- how many iterations we will take and so do not need to check
                      -- end of file inside the loop.
                      v_buf := NULL;
                      DBMS_LOB.READ(v_bfile, v_amt, v_file_pos, v_buf);
                      v_file_pos := I * v_amt + 1;
                      UTL_SMTP.WRITE_RAW_DATA(p_conn, UTL_ENCODE.BASE64_ENCODE(v_buf));
                      END LOOP;
                      END;
                      DBMS_LOB.FILECLOSE(v_bfile);
                      end_attachment(p_conn => p_conn);
                      EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                      end_attachment(p_conn => p_conn);
                      DBMS_LOB.FILECLOSE(v_bfile);
                      END binary_attachment;

                      --
                      -- Main Routine
                      --
                      BEGIN
                      --
                      -- Connect and set up header information:
                      --
                      v_conn:= UTL_SMTP.OPEN_CONNECTION( v_smtp_server, v_smtp_server_port );

                      UTL_SMTP.HELO( v_conn, v_smtp_server );
                      UTL_SMTP.MAIL( v_conn, p_from_name );
                      UTL_SMTP.RCPT( v_conn, p_to_name );
                      UTL_SMTP.OPEN_DATA ( v_conn );
                      UTL_SMTP.WRITE_DATA(v_conn, 'Subject: '||p_subject||UTL_TCP.CRLF);
                      --
                      v_mesg:= 'Content-Transfer-Encoding: 7bit' || UTL_TCP.CRLF ||
                      'Content-Type: multipart/mixed;boundary="DMW.Boundary.605592468"' || UTL_TCP.CRLF ||
                      'Mime-Version: 1.0' || UTL_TCP.CRLF ||
                      '--DMW.Boundary.605592468' || UTL_TCP.CRLF ||
                      'Content-Transfer-Encoding: binary'||UTL_TCP.CRLF||
                      'Content-Type: text/plain' ||UTL_TCP.CRLF ||
                      UTL_TCP.CRLF || p_message || UTL_TCP.CRLF ;
                      --
                      UTL_SMTP.write_data(v_conn, 'To: ' || p_to_name || UTL_TCP.crlf);
                      UTL_SMTP.WRITE_RAW_DATA ( v_conn, UTL_RAW.CAST_TO_RAW(v_mesg) );
                      --
                      -- Add the Attachment
                      --

                      binary_attachment(
                      p_conn => v_conn,
                      p_file_name => p_binary_file,
                      -- Modify the mime type at the beginning of this line depending
                      -- on the type of file being loaded.
                      p_mime_type => 'text/plain; name="'||p_binary_file||'"'
                      );
                      --
                      -- Send the email
                      --

                      UTL_SMTP.CLOSE_DATA( v_conn );
                      UTL_SMTP.QUIT( v_conn );
                      END;
                      /

                      Edited by: user12879396 on Oct 22, 2012 10:39 AM
                      • 8. Re: ORA-22288: file or LOB operation GETLENGTH failed
                        Solomon Yakobson
                        OK. I will assume value to p_oracle_directory is assigned somewhere in your code. Then you need to find that place in your code and make sure directory DIR is assigned to p_oracle_directory in upper case, otherwise you'll get exactly what you got:
                        SQL> CREATE OR REPLACE DIRECTORY 
                          2  DIR AS 
                          3  'c:\temp'
                          4  /
                        
                        Directory created.
                        
                        SQL> DECLARE
                          2      v_bfile BFILE;
                          3      p_oracle_directory varchar2(10) := 'DIR';
                          4      v_file_length PLS_INTEGER;
                          5  BEGIN
                          6      v_bfile := BFILENAME(p_oracle_directory, 'x.sql');
                          7      v_file_length := DBMS_LOB.GETLENGTH(v_bfile);
                          8      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
                          9  END;
                         10  /
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> DECLARE
                          2      v_bfile BFILE;
                          3      p_oracle_directory varchar2(10) := 'dir'; -- here directory name is in lower case
                          4      v_file_length PLS_INTEGER;
                          5  BEGIN
                          6      v_bfile := BFILENAME(p_oracle_directory, 'x.sql');
                          7      v_file_length := DBMS_LOB.GETLENGTH(v_bfile);
                          8      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
                          9  END;
                         10  /
                        DECLARE
                        *
                        ERROR at line 1:
                        ORA-22285: non-existent directory or file for GETLENGTH operation
                        ORA-06512: at "SYS.DBMS_LOB", line 566
                        ORA-06512: at line 7
                        
                        
                        SQL> 
                        SY.
                        • 9. Re: ORA-22288: file or LOB operation GETLENGTH failed
                          user12879396
                          Solomon Yakobson p_oracle_directory this is the parameter of procedure which pass at the time of execution in code it mention

                          EXEC
                          mail_files('abc@mycompanyi.com',
                          'abc@mycompanyi.com',
                          'Subject Test Email Attachment',
                          'Message Test Email Attachment',
                          'DIR','XXSUPPSTMTSTE_681129_1.PDF');

                          end;

                          Edited by: user12879396 on Oct 22, 2012 10:57 PM
                          • 10. Re: ORA-22288: file or LOB operation GETLENGTH failed
                            Solomon Yakobson
                            user12879396 wrote:
                            p_oracle_directory this is the parameter of procedure which pass at the time of execution in code it mention
                            Then make sure file /oracle/test/inst/apps/erp-server/logs/appl/conc/out/XXSUPPSTMTSTE_681129_1.PDF exists on database server and OS user oracle can read it.

                            SY.
                            • 12. Re: ORA-22288: file or LOB operation GETLENGTH failed
                              user12879396
                              oracle/test/inst/apps/erp-server/logs/appl/conc/out/XXSUPPSTMTSTE_681129_1.PDF thid file exist in DB Server with full Permission
                              • 13. Re: ORA-22288: file or LOB operation GETLENGTH failed
                                user12879396
                                select bfilename('DIR','ABC.PDF') from dual

                                When I use the above query it show the following

                                DIR//ABC.PDF  (NoExist)

                                while I have Create the directory by the following command and physical file exist in this path.

                                CREATE OR REPLACE DIRECTORY
                                DIR AS
                                '/oracle/PROD/inst/apps/PROD/logs/appl/conc/out';


                                GRANT EXECUTE, READ, WRITE ON DIRECTORY DIR TO APPS WITH GRANT OPTION;

                                GRANT READ, WRITE ON DIRECTORY DIR TO PUBLIC;

                                GRANT READ, WRITE ON DIRECTORY DIR TO SYS WITH GRANT OPTION;
                                GRANT EXECUTE ON DIRECTORY DIR TO SYS;
                                • 14. Re: ORA-22288: file or LOB operation GETLENGTH failed
                                  AlbertoFaenza
                                  Hi,

                                  as Solomon said the file should be accessible.

                                  Try the query below in your test environment. You should be able to access the file and get File exists as output:
                                  select CASE dbms_lob.fileexists(bfilename('DIR', 'XXSUPPSTMTSTE_681129_1.PDF')) 
                                            WHEN 0 THEN 'File does not exist or it is not accessible'
                                            WHEN 1 THEN 'File exists'
                                         END AS check_file
                                         FROM DUAL;
                                  Regards.
                                  Al
                                  1 2 Previous Next