5 Replies Latest reply: Jun 14, 2011 11:45 PM by Billy~Verreynne RSS

    ORA-22290: operation would exceed the maximum number of opened files or LOB

    San
      Hi All,

      I am using the below procedure for sending the email with attachements.

      I am getting the below error when i executed the below procedure in the loop for sending the multiple mail attachements.

      create or replace
      PROCEDURE              "SSSL_SEND_MAIL" ( p_sender varchar2,
            p_recipient varchar2,
            p_cc varchar2,
            p_subject varchar2,
            p_filename varchar2,
            text varchar2) is  
      
          /*LOB operation related varriables */
         v_src_loc  BFILE; 
         l_buffer   RAW(54);
         l_amount   BINARY_INTEGER := 54;
         l_pos      INTEGER := 1;
         l_blob     BLOB := EMPTY_BLOB;
         l_blob_len INTEGER;
         v_amount   INTEGER;
        
          /*UTL_SMTP related varriavles. */
          v_connection_handle  UTL_SMTP.CONNECTION;
          v_from_email_address VARCHAR2(200); 
          v_to_email_address   VARCHAR2(200) ;
          v_cc                 VARCHAR2(200);
          v_smtp_host          VARCHAR2(10) ;
          v_subject            VARCHAR2(500) ;
          l_message            VARCHAR2(3000);
          l_filename           VARCHAR2(4000);
          v_filename           VARCHAR2(4000);
          v_errorcode          VARCHAR2(3000);
          v_errormsg           VARCHAR2(4000);
          
          CURSOR C1 IS
          SELECT MAILID FROM sssl_group_mail;
        
          /* This send_header procedure is written in the documentation */
          PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
          BEGIN
          --dbms_output.put_line('entering into procedure');
          --dbms_output.put_line(pi_name || ': ' || pi_header);
            UTL_SMTP.WRITE_DATA(v_connection_handle,
                                pi_name || ': ' || pi_header || UTL_TCP.CRLF);
          END;
       
        BEGIN
       
       IF UPPER(p_filename) LIKE '%.XLS' THEN
        v_filename:=REPLACE(UPPER(p_filename),'XLS','xlsx');
        ELSE
        v_filename:=p_filename;
        END IF;
          
         v_src_loc             := BFILENAME('BROKERREPORTS',v_filename);
         v_from_email_address  := p_sender;
         v_to_email_address    := p_recipient;
         v_cc                  := p_cc;
         v_smtp_host           := 'sbssld1'; --My mail server, replace it with yours.
         v_subject             := p_subject;
          
         l_message      := '
      Dear Sir / Madam,
         
      Please find the mailback report as subscribed.
         
      With Regards
      Distributor Services Team
      
      Sundaram BNP Paribas Fund Services Ltd';
        
        --dbms_output.put_line(v_src_loc);
         --dbms_output.put_line(v_from_email_address);
        --  dbms_output.put_line(v_to_email_address);
        --   dbms_output.put_line(v_smtp_host);
        --    dbms_output.put_line(v_subject);
        --     dbms_output.put_line(l_message);
        
          /*Preparing the LOB from file for attachment. */
          DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
          DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
          v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
          DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
          l_blob_len := DBMS_LOB.getlength(l_blob);    
          
          
          l_filename:= v_filename;
        IF l_blob_len > 3000000 THEN
          BEGIN
          SSSL_FILE_ZIP.COMPRESSFILE(
          P_IN_FILE => '/mfundb/prdapex/BROKER-REPORTS/'||v_filename,
          P_OUT_FILE => '/mfundb/prdapex/BROKER-REPORTS/'|| REPLACE(REPLACE(REPLACE(UPPER(v_filename),'XLSX','ZIP'),'DBF','ZIP'),'CSV','ZIP'));
        l_filename:=NULL;
        l_filename:= REPLACE(REPLACE(REPLACE(UPPER(v_filename),'XLSX','ZIP'),'DBF','ZIP'),'CSV','ZIP');
        dbms_output.put_line(l_blob_len);
          v_src_loc := BFILENAME('BROKERREPORTS',l_filename);
        DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
        DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
        v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
        DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
        l_blob_len := DBMS_LOB.getlength(l_blob);
        
        
       EXCEPTION WHEN OTHERS THEN
       sssl_internal_error_track(sqlcode,sqlerrm,'SSSL_FILE_ZIP',l_filename||'-'||p_recipient);
      END; 
      END IF;
          
        
       /*UTL_SMTP related coding. */
          v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
          UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
          UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
          UTL_SMTP.RCPT(v_connection_handle, v_to_email_address); 
          IF v_cc IS NOT NULL THEN
          UTL_SMTP.RCPT(v_connection_handle, v_cc);
          END IF;
          
           IF text = 'BROKERREPORTSTATUS' THEN
              FOR I IN C1 LOOP
              UTL_SMTP.RCPT(v_connection_handle, i.mailid);
              END LOOP;
           END IF;
              
          UTL_SMTP.OPEN_DATA(v_connection_handle);
          
          send_header('From', v_from_email_address);-- || '<'||'>');
         
          send_header('TO', v_to_email_address);--|| '<'||'>');
          IF v_cc IS NOT NULL THEN
          send_header('CC', v_cc); 
          END IF;
          
         IF text = 'BROKERREPORTSTATUS' THEN
              FOR J IN C1 LOOP
               send_header('CC', j.mailid);
              END LOOP;
          END IF;
          
          send_header('Subject', v_subject);
      
        
          --MIME header.
         UTL_SMTP.WRITE_DATA(v_connection_handle,
                            'MIME-Version: 1.0' || UTL_TCP.CRLF);
         UTL_SMTP.WRITE_DATA(v_connection_handle,
                              'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
                              UTL_TCP.CRLF);
         UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
        
          -- Mail Body
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              'Content-Type: text/plain;' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              ' charset=US-ASCII' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
        
          -- Mail Attachment
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                             'Content-Type: application/octet-stream' ||
                              UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              ' filename="' || l_filename || '"' || --My filename
                              UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
        /* Writing the BLOL in chunks */
          WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
            UTL_SMTP.write_raw_data(v_connection_handle,
                                    UTL_ENCODE.BASE64_ENCODE(l_buffer));
            UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
            l_buffer := NULL;
            l_pos    := l_pos + l_amount;
          END LOOP;
          UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
        
          -- Close Email
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
        
          UTL_SMTP.CLOSE_DATA(v_connection_handle);
          UTL_SMTP.QUIT(v_connection_handle);
          DBMS_LOB.FREETEMPORARY(l_blob);
          DBMS_LOB.FILECLOSE(v_src_loc);
       
        EXCEPTION
          WHEN OTHERS THEN
          v_errorcode:=sqlcode;
          v_errormsg:=sqlerrm;
          
          IF v_errormsg like '%SMTP permanent error: 552 Message size exceeds fixed maximum message size set by administrator%' THEN
          
             
          BEGIN
      SYS.UTL_MAIL.SEND(sender=>'DistributorServices@sundarambnpparibasfs.in',
      RECIPIENTS=>'Arulkumar.N@sundarambnpparibasfs.in',
      cc=>'DistributorServices@sundarambnpparibasfs.in',
      subject=>'File size exceeds!!!!' ,
      MESSAGE=>'File size exceeds the limit!!!' 
      ||l_filename||
      
      ' Please contact Application Support for more details!!!
      
      With Regards
      
      Application Support Team,
      Sundaram BNP Paribas Fund Services Ltd',
      MIME_TYPE=>'text; charset=us-ascii');
      EXCEPTION WHEN OTHERS THEN
      sssl_internal_error_track(sqlcode,sqlerrm,'SSSL_utl_MAIL',l_filename||'-'||p_recipient);
      end;
      
      END IF;
          
            UTL_SMTP.QUIT(v_connection_handle);
            DBMS_LOB.FREETEMPORARY(l_blob);
            DBMS_LOB.FILECLOSE(V_SRC_LOC);
           sssl_internal_error_track(sqlcode,sqlerrm,'SSSL_SEND_MAIL',l_filename||'-'||p_recipient);
        END;
      
       
      i am getting the below error
      ORA-22290: operation would exceed the maximum number of opened files or LOB
      can anyone suggest me how to resolve this????

      cheers,
      Shan.

      Edited by: San on 13 Jun, 2011 10:50 AM
        • 1. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
          Billy~Verreynne
          San wrote:

          ORA-22290: operation would exceed the maximum number of opened files or LOB
          This error is usually an indication of buggy code that leaks LOB pointers (or locators as the manuals call these pointers).

          In PL/SQL you cannot code resource protection blocks using the following type of syntax (as supported by a number of other languages):
          try
            <processing>
          finally
            <release resources>
          end
          The processing block has a single exit point enforced by the finally clause. So irrespective of what happens in the processing (exceptions raised, etc), the code in the final block is guaranteed to execute.

          In PL/SQL you need to do this using a normal exception handler. So in PL/SQL it will look something as follows:
          begin
            <processing>
            <release resources>  
            --// first exit point for normal termination
          
          exception when OTHERS then
            <release resources> 
            raise;
            --// second exit point for abnormal termination
          end;
          So in PL/SQL we cannot force a single exit point in a code unit. At minimum there are 2 exit points. One for normal termination and one for abnormal termination. You need to cover both exit points ito releasing allocated resources.

          Thus make sure that your code is properly releasing all LOB pointers under both normal and abnormal conditions.
          • 2. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
            San
            hi,

            Thx for your reply. I tried to change the code as per your advice. still i am getting the same error.

            i execute the above send mail procedure in the loop for 1500 . i am getting the error for only last 100 records.


            Can anyone suggest me how to resolve this error.

            error code
            ORA-29278: SMTP transient error: 421 Service not available
            ORA-22290: operation would exceed the maximum number of opened files or LOBs
            cheers,
            San
            • 3. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
              Peter Gjelstrup
              Hi San,

              In two places you have identical code:
                  DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
              Not sure what it means, but it seems suspicious to me.

              Regards
              Peter
              • 4. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
                407338
                Try to use the temp lob duration CALL for the call duration and see if Oracle frees up those not needed for you. Your call to the create temp lob uses the default session duration. I am not sure if that makes any difference, but you won't lose anything by using the CALL duration.

                Ben
                • 5. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
                  Billy~Verreynne
                  San wrote:

                  error code
                  ORA-29278: SMTP transient error: 421 Service not available
                  ORA-22290: operation would exceed the maximum number of opened files or LOBs
                  2 different errors.

                  The first is a SMTP (mail server) error. Not an Oracle error as such - you need to identify what instruction your code is sending to the SMTP server and why it deems its unable to service that.

                  The second error is a bug in your code. It is not closing LOB locators after opening and using them. Thus it is leaking resources. Go through your code and find where LOBs are opened and not closed. Find it. Fix it. That is how experience is gained.