4 Replies Latest reply: Feb 9, 2013 3:15 AM by Osama_Mustafa RSS

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

    user13427480
      i am getting this error in a procedure.


      ORA-22290: operation would exceed the maximum number of opened files or LOBs
      22290, 00000, "operation would exceed the maximum number of opened files or LOBs"
      // *Cause: The number of open files or LOBs has reached the maximum limit.
      // *Action: Close some of the opened files or LOBs and retry the operation.


      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      session_max_open_files integer 10

      Procuedure:
      CREATE OR REPLACE PROCEDURE WMSOWN."PROC_WMS_XML_READ"
      (
      P_EVENT_KEY IN VARCHAR2,
      X_STATUS_MSG OUT VARCHAR2,
      X_STATUS OUT NUMBER
      )AS
      l_parser dbms_xmlparser.Parser;
      domdoc xmldom.DOMDocument;
      nodelist XMLDOM.DOMNODELIST;
      node XMLDOM.DOMNODE;
      n_child XMLDOM.DOMNODE;
      elements XMLDOM.DOMELEMENT;
      name_node_map XMLDOM.DOMNAMEDNODEMAP;
      parent_seg varchar2(4000);
      tag_name_bkp varchar2(4000); -- LOOK OUT BRAD IS CODING AGAIN
      chile_seg VARCHAR2(4000);
      p_seg VARCHAR2(4000);
      p_seg1 VARCHAR2(4000);
      p_seg2 VARCHAR2(30);
      p_int_name VARCHAR2(50);
      col_value VARCHAR2(100):=NULL;
      len1 NUMBER;
      cnt NUMBER;
      seg_id_bkp NUMBER; -- LOOK OUT BRAD IS CODING AGAIN
      sequence_bkp NUMBER; -- LOOK OUT BRAD IS CODING AGAIN
      prev_sequence NUMBER; -- LOOK OUT BRAD IS CODING AGAIN
      prev_seq_set VARCHAR2(3); --brad coding
      parent_id number; ---brad coding
      valid_seg NUMBER; -- LOOK OUT BRAD IS CODING AGAIN
      data_status VARCHAR2(10);
      v_main_seg VARCHAR2(50);
      v_seq_no NUMBER;
      V_CLOBLOCATOR CLOB;
      V_FILELOCATOR BFILE;
      v_amount_to_load NUMBER;
      dest_offset NUMBER := 1;
      src_offset NUMBER := 1;
      lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
      warning NUMBER;
      v_event_name USR_OUB_FILE_PROCESS_DETAILS.EVENT_NAME%TYPE;
      v_file_name USR_OUB_FILE_PROCESS_DETAILS.FILE_NAME%TYPE;
      DIRECTORY_PATH_INVALID EXCEPTION;
      PRAGMA EXCEPTION_INIT(DIRECTORY_PATH_INVALID,-22285);
      NO_PRIVILEGES EXCEPTION;
      PRAGMA EXCEPTION_INIT(NO_PRIVILEGES,-22286);
      INVALID_DIRECTORY EXCEPTION;
      PRAGMA EXCEPTION_INIT(INVALID_DIRECTORY,-22287);
      FILE_NOT_FOUND EXCEPTION;
      PRAGMA EXCEPTION_INIT(FILE_NOT_FOUND,-22289);
      P_DIRECTORY VARCHAR2(50) :='WMS_XML_DIR_OUB';
      v_whid poldat_view.wh_id%type;
      BEGIN
      /********************************************************************************************************************************************/
      --NAME :  PROC_WMS_XML_READ.PLS
      --
      --DESCRIPTION :
      -- Procedure PROC_WMS_XML_READ search XML files from remote location.
      -- Open,Parse and Read XML files. Store all XML values into tables.
      --
      -- Developed by Dharmesh Patidar(jw782)
      --
      -- History: New condition is added i.e. p_seg:=parent_seg to maintain PARENT and CHILD relationship
      -- by Vishwanath Dubey(jl246) on 17-June-2011
      -- BRAD_XML_DEBUG table removed for CLEANING Activity by DHARMESH PATIDAR(JW782) ON 29-JUNE-2011.
      /********************************************************************************************************************************************/
      /*BLOCK FOR CAPTURING EVENT NAME BASED ON EVENT ID START*/
      BEGIN
      SELECT event_name,file_name,WAREHOUSE_ID
      INTO v_event_name, v_file_name,v_whid
      FROM usr_oub_file_process_details
      WHERE event_id=p_event_key
      AND process_flag='U';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      x_status_msg:=SQLCODE||':'||' Error while selecting event name and event id in Procedure PROC_WMS_XML_READ : Record is not available in USR_OUB_FILE_PROCESS_DETAILS table for event id '|| P_EVENT_KEY;
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      WHEN TOO_MANY_ROWS THEN
      x_status_msg:=SQLCODE||':'||' Error while selecting event name and event id in Procedure PROC_WMS_XML_READ : More than one Records found in USR_OUB_FILE_PROCESS_DETAILS table for event id '|| P_EVENT_KEY;
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message

      RETURN;
      WHEN VALUE_ERROR THEN
      x_status_msg:=SQLCODE||':'||' Error while selecting event name and event id in Procedure PROC_WMS_XML_READ : Varibale length is small or data type mismatch while selecting event id and event name in USR_OUB_FILE_PROCESS_DETAILS table for event id '|| P_EVENT_KEY;
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      WHEN OTHERS THEN
      x_status_msg:=SQLCODE||':'||'Error in Procedure PROC_WMS_XML_READ while selecting event name and event id ';
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      END;
      /*BLOCK FOR CAPTURING EVENT NAME BASED ON EVENT ID END*/
      /******************************************************************************************************************/


      /******************************************************************************************************************/
      /*LOGIC TO READ XML FROM REMOTE LOCATION START*/
      DBMS_LOB.CREATETEMPORARY(V_CLOBLOCATOR, TRUE);
      V_FILELOCATOR := BFILENAME(P_DIRECTORY,V_FILE_NAME);
      DBMS_LOB.OPEN(V_FILELOCATOR,DBMS_LOB.FILE_READONLY);
      V_AMOUNT_TO_LOAD := DBMS_LOB.GETLENGTH(V_FILELOCATOR);
      DBMS_LOB.LOADCLOBFROMFILE(V_CLOBLOCATOR,
      V_FILELOCATOR ,
      V_AMOUNT_TO_LOAD,
      DEST_OFFSET,
      SRC_OFFSET,
      0,
      LANG_CONTEXT,
      WARNING);
      dbms_lob.close(V_FILELOCATOR);
      /*LOGIC TO READ XML FROM REMOTE LOCATION END*/
      /*******************************************************************************************************************/

      /*Temporary Code to help with debug Clear the table before populating it with new data*/
      --delete table BRAD_XML_DEBUG;
      cnt:=1;
      seg_id_bkp:=0;
      data_status:='N';
      v_seq_no:=0;
      prev_seq_set:='NO';
      /*create new parser.*/
      l_parser := dbms_xmlparser.newParser;
      dbms_xmlparser.parseClob(l_parser, replace(V_CLOBLOCATOR,'&','1x2x3x4x5'));

      /*Parse the document and create a new DOM document.*/
      domdoc :=dbms_xmlparser.getDocument(l_parser);

      /* get all elements in the DOM*/
      nodelist := XMLDOM.getElementsByTagName(DOMDoc, '*');
      len1 := XMLDOM.getLength(nodelist);

      /* loop through elements of the DOM */
      FOR j in 1..len1-1 LOOP --MAIN LOOP START
      BEGIN
      /*below sql will fetch Node from table to travel xml data*/
      BEGIN
      SELECT int_name,tag_name
      INTO p_int_name, p_seg1
      FROM usr_wms_tag_det
      WHERE int_name=v_event_name
      AND seq_no =cnt;
      EXCEPTION
      --PLEASE DO NOT HANDLE ANY EXCEPTION APART MENTIONED BELOW
      WHEN OTHERS THEN
      NULL;
      END;
      IF cnt=1 THEN
      v_main_seg:=p_seg1;
      END IF;
      EXCEPTION
      --PLEASE DO NOT HANDLE ANY EXCEPTION APART MENTIONED BELOW
      WHEN no_data_found THEN
      null;
      WHEN OTHERS THEN
      x_status_msg:=SQLCODE||':'||'Error in Procedure PROC_WMS_XML_READ while selecting interface name and tag name'||sqlerrm;
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      END;
      /*LOGICS TO READ XML START*/
      node:=XMLDOM.item(nodelist, j);
      elements:=XMLDOM.makeElement(node);
      parent_seg:=(xmldom.getTagName(elements));
      tag_name_bkp:=(xmldom.getTagName(elements));
      name_node_map:=xmldom.getAttributes(node);
      n_child:=xmldom.getFirstChild(node);
      col_value:=xmldom.getNodeValue(n_child);
      /*************************************************************************************************/
      /*get the sequence number from the interface hierarchy table */
      SELECT count(1)
      INTO valid_seg
      FROM usr_wms_tag_det
      WHERE int_name=v_event_name
      AND tag_name = tag_name_bkp;

      if valid_seg>0 then
      begin
      SELECT seq_no
      INTO sequence_bkp
      FROM usr_wms_tag_det
      WHERE int_name=v_event_name
      AND tag_name = tag_name_bkp;
      seg_id_bkp:=seg_id_bkp+1;
      p_seg:=parent_seg;--Modified by Vishwanath Dubey dated 16-jun-2011
      end;
      end if;

      if prev_seq_set = 'NO' then
      begin
      prev_sequence := sequence_bkp;
      prev_seq_set := 'YES';
      end;
      end if;

      if sequence_bkp < prev_sequence then --you just moved up level(s) in the message structure
      begin
      select max(seg_id)
      into parent_id
      from usr_wms_global_xml_det
      where seg_sequence = sequence_bkp-1;
      prev_sequence := sequence_bkp;
      end;
      end if;

      if sequence_bkp > prev_sequence then --you just moved down a level in the message structure
      parent_id := seg_id_bkp-1;
      prev_sequence := sequence_bkp;
      end if;

      /*end getting the hierarchy table sequence */
      /************************************************************************************************/

      /*LOGICS TO READ XML END */
      IF (parent_seg =p_seg1) or (parent_seg=p_seg2) THEN
      if parent_seg=v_main_seg then
      v_seq_no:=v_seq_no+1;
      end if;
      BEGIN
      /* INSERTING DATA LOGICS TO READ XML END */
      INSERT INTO usr_wms_global_xml_det values(p_int_name,tag_name_bkp,parent_seg,seg_id_bkp,sequence_bkp,parent_id,'','','',J,v_seq_no,data_status,cnt);
      EXCEPTION
      WHEN OTHERS THEN
      x_status_msg:=SQLCODE||' : Error in Procedure PROC_WMS_XML_READ while inserting records in USR_WMS_GLOBAL_XML_DET table for interface name and parent segment '||P_INT_NAME||','||PARENT_SEG;
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      END ;
      p_seg:=parent_seg;
      p_seg2:=P_SEG1;
      cnt:=cnt+1;
      ELSE
      chile_seg:=parent_seg;
      BEGIN
      /* INSERTING DATA LOGICS TO READ XML END */
      INSERT INTO usr_wms_global_xml_det values(p_int_name,tag_name_bkp,p_seg,seg_id_bkp,sequence_bkp,parent_id,'',chile_seg,replace(TRIM(Col_Value),'1x2x3x4x5','&'),J,v_seq_no,data_status,cnt);
      EXCEPTION
      WHEN OTHERS THEN
      x_status_msg:=SQLCODE||' : Error in Procedure PROC_WMS_XML_READ while inserting records in USR_WMS_GLOBAL_XML_DET table for interface name and parent segment '||P_INT_NAME||','||PARENT_SEG;
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      END;
      END IF;
      END LOOP; --MAIN LOOP END
      dbms_xmldom.freeDocument(DOMDoc);
      x_status:=0;
      EXCEPTION
      WHEN DIRECTORY_PATH_INVALID THEN
      x_status_msg:=SQLCODE||' : Error in Procedure PROC_WMS_XML_READ DIRECTORY PATH IS INVALID';
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      WHEN FILE_NOT_FOUND THEN
      x_status_msg:=SQLCODE||' : Error in Procedure PROC_WMS_XML_READ INVALID XML FILE NAME OR FILE DOES NOT EXISTS';
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      WHEN NO_PRIVILEGES THEN
      x_status_msg:=SQLCODE||' : Error in Procedure PROC_WMS_XML_READ Insufficient privileges on file or directory NAME- '||p_directory||' to perform FILEOPEN operation.';
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      RETURN;
      WHEN OTHERS THEN
      x_status_msg:=SQLCODE||' : Error in Procedure PROC_WMS_XML_READ '|| SQLERRM;
      x_status:=SQLCODE;
      proc_wms_error_trace(v_whid, --warehouse id
      null , --event id
      v_event_name , --event name
      x_status, --error code
      x_status_msg ); --error message
      dbms_xmlparser.freeParser(l_parser);
      dbms_xmldom.freeDocument(DOMDoc);
      RETURN;
      END PROC_WMS_XML_READ;
      /

      Edited by: user13427480 on Feb 8, 2013 7:08 PM
        • 1. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
          sb92075
          user13427480 wrote:
          i am getting this error in a procedure.


          ORA-22290: operation would exceed the maximum number of opened files or LOBs
          22290, 00000, "operation would exceed the maximum number of opened files or LOBs"
          // *Cause: The number of open files or LOBs has reached the maximum limit.
          // *Action: Close some of the opened files or LOBs and retry the operation.
          https://cn.forums.oracle.com/forums/thread.jspa?threadID=2236929

          scroll down to the bottom & read what Billy said
          • 2. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
            user13427480
            /*LOGIC TO READ XML FROM REMOTE LOCATION START*/
            DBMS_LOB.CREATETEMPORARY(V_CLOBLOCATOR, TRUE);
            V_FILELOCATOR := BFILENAME(P_DIRECTORY,V_FILE_NAME);
            DBMS_LOB.OPEN(V_FILELOCATOR,DBMS_LOB.FILE_READONLY);
            V_AMOUNT_TO_LOAD := DBMS_LOB.GETLENGTH(V_FILELOCATOR);
            DBMS_LOB.LOADCLOBFROMFILE(V_CLOBLOCATOR,
            V_FILELOCATOR ,
            V_AMOUNT_TO_LOAD,
            DEST_OFFSET,
            SRC_OFFSET,
            0,
            LANG_CONTEXT,
            WARNING);
            dbms_lob.close(V_FILELOCATOR);
            /*LOGIC TO READ XML FROM REMOTE LOCATION END*/

            ---this is part of the procedure where lob are opeded and are closed.


            i dont think there is a bug in the code, if you can find one do let us know.
            • 3. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
              sb92075
              user13427480 wrote:

              i dont think there is a bug in the code, if you can find one do let us know.
              Just as part of the debugging effort what results when you REMOVE, DELETE & ELIMINATE all & every
              EXCEPTION handler code?
              • 4. Re: ORA-22290: operation would exceed the maximum number of opened files or LOB
                Osama_Mustafa
                when you post sql statement use
                also similar threads :
                ORA-22290: operation would exceed the maximum number of opened files or LOB
                https://kr.forums.oracle.com/forums/thread.jspa?messageID=10842417