3 Replies Latest reply: Jan 28, 2013 1:35 AM by user13050902 RSS

    PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

    user13050902
      Hi All ,

      I want to copy blob data from coloum of type blob to procedure out parameter of blob type .
      For that i m using
      dbms_lob.copy(document, bdoc, dbms_lob.getlength(bdoc);

      Procedure is compiled successfully but during execution it gives below error
      The Error number is ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275


      Mention below is my sample procedure :

      CREATE OR REPLACE PROCEDURE APPS.XXHDFC_ATTACH_CAPEX_TEST_PROC
      (
      document_id IN VARCHAR2
      --,display_type IN VARCHAR2
      ,document IN OUT bLOB
      ,P_media_id out number
      ,document_type IN OUT VARCHAR2
      ) IS
      lob_id NUMBER;
      l_media_id number;
      bdoc bLOB;
      content_type VARCHAR2(100);
      filename VARCHAR2(300);
      l_layout_id boolean;
      l_request_id number;
      l_rowid rowid;
      l_attached_document_id number;
      l_document_id number;
      l_document_id1 number;
      -- l_media_id number;
      l_category_id number := 1; -- OE_ORDER_HEADERS
      l_pk1_value fnd_attached_documents.pk1_value%TYPE ;
      l_description fnd_documents_tl.description%TYPE := 'CAPEX HISTORY OUTPUT';
      l_filename fnd_documents_tl.file_name%TYPE ;
      k_filename fnd_documents_tl.file_name%TYPE := 'HDFCE_ERGO_CAPEX_HISTORY_OUTPUT.PDF' ;
      l_seq_num number;
      l_blob_data blob;
      v_blob blob ;
      l_blob blob;
      l_bfile bfile;
      l_byte number;
      v_finished BOOLEAN;
      v_phase VARCHAR2(240);
      v_status VARCHAR2(240);
      v_request_phase VARCHAR2(240);
      v_request_status VARCHAR2(240);
      v_message VARCHAR2(240);
      l_document_type varchar2(25);
      l_doc_subtype varchar2(25);
      L_ITEM_TYPE WF_ITEMS.ITEM_TYPE%TYPE;
      L_ITEM_KEY WF_ITEMS.ITEM_KEY%TYPE;
      l_release_no number;
      P_HEADER_ID number;
      v_in Pls_Integer := 1;
      v_out Pls_Integer := 1;
      v_lang number := dbms_lob.default_lang_ctx;
      v_warning Pls_Integer := 0;
      l_FILE_DAta blob ;

      BEGIN

      fnd_global.apps_initialize(1210,50756,7000);
      l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
      l_item_key := substr(document_id, instr(document_id, ':') + 1,length(document_id) - 2);


      -- l_document_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
      -- itemkey => l_item_key,
      -- aname => 'DOCUMENT_TYPE');


      -- l_doc_subtype := wf_engine.GetItemAttrText (itemtype => l_item_type,
      -- itemkey => l_item_key,
      -- aname => 'DOCUMENT_SUBTYPE');

      l_document_id := wf_engine.GetItemAttrNumber
      (itemtype => l_item_type,
      itemkey => l_item_key,
      aname => 'DOCUMENT_ID');


      lob_id := to_number(l_document_id);

      l_pk1_value := to_number(l_document_id);



      -- Obtain the BLOB version of the document




      l_layout_id := fnd_request.add_layout (template_appl_name => 'PO',
      template_code => 'XXHDFCCAPEXAPPRHIS',
      template_language => 'en',
      template_territory => 'US',
      output_format => 'PDF'
      );

      l_request_id := fnd_request.submit_request('PO',
      'XXHDFCCAPEXAPPRHIS',
      null,
      null,
      false,
      lob_id, --P_report_type
      null , --P_agend_id
      null, --P_po_num_from
      null , --P_po_num_to
      null , --P_relaese_num_from
      null , --P_release_num_to
      null , --P_date_from
      null , --P_date_to
      null , --P_approved_flag
      null, --P_test_flag
      null , --P_print_releases
      null , --P_sortby
      null , --P_user_id
      null , --P_fax_enable
      null , --P_fax_number
      null , --P_BLANKET_LINES
      null, --View_or_Communicate,
      null, --P_WITHTERMS
      null, --P_storeFlag
      null, --P_PRINT_FLAG
      null, --P_DOCUMENT_ID
      null, --P_REVISION_NUM
      null, --P_AUTHORIZATION_STATUS
      null, --P_DOCUMENT_TYPE
      null,--P_max_zip_size, <PO Attachment Support 11i.11>
      null, -- P_PO_TEMPLATE_CODE
      null, -- P_CONTRACT_TEMPLATE_CODE
      null,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL, NULL, NULL, NULL, NULL, NULL,
      NULL, NULL);


      commit;


      v_finished := fnd_concurrent.wait_for_request
      (
      request_id => l_request_id,
      interval => 5,
      max_wait => 120,
      phase => v_phase,
      status => v_status,
      dev_phase => v_request_phase,
      dev_status => v_request_status,
      message => v_message
      );


      select FND_DOCUMENTS_S.nextval
      into l_document_id1
      from dual;

      select FND_ATTACHED_DOCUMENTS_S.nextval
      into l_attached_document_id
      from dual;

      select nvl(max(seq_num),0) + 10
      into l_seq_num
      from fnd_attached_documents
      where pk1_value = l_pk1_value
      and entity_name = 'REQ_HEADERS';

      select substr(file_name,instr(file_name ,'/',-1,1)+1)
      into l_filename
      from FND_CONC_REQ_OUTPUTS where CONCURRENT_REQUEST_ID =l_request_id;

      fnd_documents_pkg.insert_row
      ( X_ROWID => l_rowid
      , X_DOCUMENT_ID => l_document_id1
      , X_CREATION_DATE => sysdate
      , X_CREATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
      , X_DATATYPE_ID => 6 -- FILE
      , X_CATEGORY_ID => l_category_id
      , X_SECURITY_TYPE => 4
      , X_PUBLISH_FLAG => 'Y'
      , X_USAGE_TYPE => 'O'
      , X_LANGUAGE => 'US'
      , X_DESCRIPTION => l_description
      , X_FILE_NAME => l_filename
      , X_MEDIA_ID => l_media_id
      );

      commit;
      INSERT INTO fnd_lobs (file_id
      ,file_name
      ,file_content_type
      ,upload_date
      ,expiration_date
      ,program_name
      ,program_tag
      ,file_data
      ,language
      ,oracle_charset
      ,file_format
      )
      values
      (
      l_media_id,
      k_filename,
      'application/pdf',
      sysdate,
      null,
      null,
      null,
      empty_blob (), --l_blob_data,
      null,
      null,
      'binary'
      ) returning file_data into l_blob;


      DBMS_LOB.CREATETEMPORARY(l_blob,true);
      l_bfile := bfilename ('EXAMPLEDIR3', l_filename);
      l_byte := dbms_lob.getlength(l_bfile);
      dbms_output.put_line ('size is '||l_byte);
      dbms_lob.fileopen (l_bfile);
      dbms_lob.loadfromfile (l_blob, l_bfile, l_byte);
      dbms_lob.fileclose(l_bfile);
      dbms_output.put_line ('document id is '||l_document_id1 );



      update fnd_lobs
      set file_data=l_blob
      where file_id=l_media_id;



      fnd_documents_pkg.insert_tl_row
      ( X_DOCUMENT_ID => l_document_id1
      , X_CREATION_DATE => sysdate
      , X_CREATED_BY => 1175
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => 1175
      , X_LAST_UPDATE_LOGIN =>1175
      , X_LANGUAGE => 'US'
      , X_DESCRIPTION => l_description
      );
      commit;

      fnd_attached_documents_pkg.insert_row
      ( X_ROWID => l_rowid
      , X_ATTACHED_DOCUMENT_ID => l_attached_document_id
      , X_DOCUMENT_ID => l_document_id1
      , X_CREATION_DATE => sysdate
      , X_CREATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_DATE => sysdate
      , X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
      , X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
      , X_SEQ_NUM => l_seq_num
      , X_ENTITY_NAME => 'PO_HEADERS'
      , X_COLUMN1 => null
      , X_PK1_VALUE => l_pk1_value
      , X_PK2_VALUE => null
      , X_PK3_VALUE => null
      , X_PK4_VALUE => null
      , X_PK5_VALUE => null
      , X_AUTOMATICALLY_ADDED_FLAG => 'N'
      , X_DATATYPE_ID => 6
      , X_CATEGORY_ID => l_category_id
      , X_SECURITY_TYPE => 4
      , X_PUBLISH_FLAG => 'Y'
      , X_LANGUAGE => 'US'
      , X_DESCRIPTION => l_description
      , X_FILE_NAME => l_filename
      , X_MEDIA_ID => l_media_id
      );


      commit;

      DBMS_OUTPUT.PUT_LINE ('MEDIA ID CREATED IS '||L_MEDIA_ID);

      -- P_media_id := l_media_id ;


      ------------ old

      /* SELECT distinct media_id into l_media_id
      FROM FND_ATTACHED_DOCS_FORM_VL
      where pk1_value = lob_id
      and USER_ENTITY_PROMPT = 'PO Header'
      and category_id =1000504;
      */
      SELECT file_name
      ,file_content_type
      ,file_data
      INTO filename
      ,content_type
      ,bdoc
      FROM fnd_lobs
      WHERE file_id = l_media_id;
      document_type := content_type || ';name=' || filename;

      --DBMS_LOB.convertToBlob(bdoc, bdoc, DBMS_LOB.getlength(bdoc), v_in, v_out,DBMS_LOB.default_csid, 
      -- v_lang, v_warning);

      dbms_output.put_line( 'The Length is '||dbms_lob.getlength(bdoc)) ;

      dbms_lob.copy(document, bdoc, dbms_lob.getlength(bdoc);

      EXCEPTION
      WHEN OTHERS THEN
      dbms_output.put_line( 'The Error number is '||Sqlerrm) ;
      dbms_output.put_line( 'The Error is '||Sqlerrm) ;
      -- null;
      END ;


      Thanks ,

      Prafulla Chaskar
      /
        • 1. Re: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
          Hoek
          Remove your EXCEPTION WHEN OTHERS ... completely.
          That way you'll get the line number that causes the error.
          Regarding the error, you could search asktom for similar cases, for example:
          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:388196216305#69404795946830
          (ctrl+f for ora-22275)
          • 2. Re: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
            Billy~Verreynne
            Code does not abuse uppercase - which is nice.

            Unfortunately, it goes pretty much downhill after that. Lots wrong.

            The most fundamental principle of writing software is modularise. A module is designed to do one thing, and only thing only. And do it well.

            Why this principle? Easy to read and understand it if it does only one thing. Easy to test. East to re-use again and again. Easy to modify. Easy to tune. Easy to port. Etc. Etc.

            You have a single procedure that tries to do lots of different things. One of the clear signs of a flawed design is lots and lots of private variables in a module. You have about 40 variables. Which means the code is going to try at least 40 different things as it needs 40 variables to assign, evaluate, keep track of, and so on.

            The code also does not make sense. Why this:
            lob_id := to_number(l_document_id);
            l_pk1_value := to_number(l_document_id);
            The variable l_document_id is already a number. Why convert it to a number?

            Why keep 3 copies of that variable value in 3 different variables? What for? Besides making it more complex as more variables means more stuff to keep track of.. and which of the 3 duplicate variables should be used where and when?


            And this!?
            l_request_id := fnd_request.submit_request('PO',
            ..snipped...
            NULL, NULL, NULL, NULL, NULL, NULL, NULL,
            NULL, NULL);
            I stopped counting after 40 parameters. THIS IS WRONG. A parameter signature of that size does not make sense.

            Another fundamental software engineer principle. Structured data - and not just structured programming.

            Define a record type (or SQL user type) that contains related data fields in single structure that can be passed between modules.

            The following is not performant. Why 2 context switches to the SQL engine?
            >
            select FND_DOCUMENTS_S.nextval
            into l_document_id1
            from dual;

            select FND_ATTACHED_DOCUMENTS_S.nextval
            into l_attached_document_id
            from dual;
            Thus can be done using a single context switch and a single SQL statement:
            select FND_DOCUMENTS_S.nextval, FND_ATTACHED_DOCUMENTS_S.nextval into l_document_id1, l_attached_document_id from dual;

            Also, what is the difference between variables l_document_id and l_document_id1. Variable names need to be meaningful. How is the poor sod who is going to maintain this mess, going to know whether to use l_document_id or l_document_id1, when updating or modifying the code?

             
            Then there is a number of commits that happen. If there is an error, how does the caller know what this module managed to do and commit? This module will result in inconsistent behaviour as it may do all the commits with the 1st call, run into errors with the 2nd and 3rd call, doing 2 commits and 4 commits respectively.

            Another fundamental software engineering principle violated. The caller has no idea WHAT this module actually did and commit. It does not have consistent and predictable behaviour.

            Then there's the shoddy exception handler that is also totally wrong.

             
            Writing good code is actually easy. Understand the fundamentals. Stick to the fundamentals. Which is why the sample code posted is a complete software engineering failure. It violates fundamentals. This is not how software should be designed or written.
            • 3. Re: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
              user13050902
              Hi All ,

              After removing an exception i m getting error at below statments

              dbms_lob.copy(document, bdoc, dbms_lob.getlength(bdoc);

              both documnet amd bdoc variable are blob type .


              revised code is mentioned below ;

              CREATE OR REPLACE PROCEDURE APPS.XXHDFC_ATTACH_CAPEX_PROCEDURE
              (
              document_id IN VARCHAR2
              ,display_type IN VARCHAR2
              ,document IN OUT BLOB
              ,document_type IN OUT VARCHAR2
              ) IS
              ---lob_id NUMBER;
              l_media_id number;
              bdoc BLOB;
              content_type VARCHAR2(100);
              filename VARCHAR2(300);
              l_layout_id boolean;
              l_request_id number;
              l_rowid rowid;
              l_attached_document_id number;
              l_document_id number;
              l_document_id1 number;
              -- l_media_id number;
              l_category_id number := 1; -- OE_ORDER_HEADERS
              -- l_pk1_value fnd_attached_documents.pk1_value%TYPE ;
              l_description fnd_documents_tl.description%TYPE := 'CAPEX HISTORY OUTPUT';
              l_filename fnd_documents_tl.file_name%TYPE ;
              k_filename fnd_documents_tl.file_name%TYPE := 'HDFCE_ERGO_CAPEX_HISTORY_OUTPUT.PDF' ;
              l_seq_num number;
              l_blob_data blob;
              l_blob blob;
              l_bfile bfile;
              l_byte number;
              v_finished BOOLEAN;
              v_phase VARCHAR2(240);
              v_status VARCHAR2(240);
              v_request_phase VARCHAR2(240);
              v_request_status VARCHAR2(240);
              v_message VARCHAR2(240);
              l_document_type varchar2(25);
              l_doc_subtype varchar2(25);
              L_ITEM_TYPE WF_ITEMS.ITEM_TYPE%TYPE;
              L_ITEM_KEY WF_ITEMS.ITEM_KEY%TYPE;
              l_release_no number;
              P_HEADER_ID number;
              BEGIN

              fnd_global.apps_initialize(1175,50644,7000);
              l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
              l_item_key := substr(document_id, instr(document_id, ':') + 1,length(document_id) - 2);


              l_document_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
              itemkey => l_item_key,
              aname => 'DOCUMENT_TYPE');


              l_doc_subtype := wf_engine.GetItemAttrText (itemtype => l_item_type,
              itemkey => l_item_key,
              aname => 'DOCUMENT_SUBTYPE');
              l_document_id := wf_engine.GetItemAttrNumber
              (itemtype => l_item_type,
              itemkey => l_item_key,
              aname => 'DOCUMENT_ID');


              --lob_id := l_document_id;

              -- l_pk1_value := l_document_id; -- Obtain the BLOB version of the document

              l_layout_id := fnd_request.add_layout (template_appl_name => 'PO',
              template_code => 'XXHDFCCAPEXAPPRHIS',
              template_language => 'en',
              template_territory => 'US',
              output_format => 'PDF'
              );

              l_request_id := fnd_request.submit_request('PO',
              'XXHDFCCAPEXAPPRHIS',
              null,
              null,
              false,
              l_document_id , --P_report_type
              null , --P_agend_id
              null, --P_po_num_from
              null , --P_po_num_to
              null , --P_relaese_num_from
              null , --P_release_num_to
              null , --P_date_from
              null , --P_date_to
              null , --P_approved_flag
              null, --P_test_flag
              null , --P_print_releases
              null , --P_sortby
              null , --P_user_id
              null , --P_fax_enable
              null , --P_fax_number
              null , --P_BLANKET_LINES
              null, --View_or_Communicate,
              null, --P_WITHTERMS
              null, --P_storeFlag
              null, --P_PRINT_FLAG
              null, --P_DOCUMENT_ID
              null, --P_REVISION_NUM
              null, --P_AUTHORIZATION_STATUS
              null, --P_DOCUMENT_TYPE
              null,--P_max_zip_size, <PO Attachment Support 11i.11>
              null, -- P_PO_TEMPLATE_CODE
              null, -- P_CONTRACT_TEMPLATE_CODE
              null,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL, NULL, NULL, NULL, NULL, NULL,
              NULL, NULL);


              commit;


              v_finished := fnd_concurrent.wait_for_request
              (
              request_id => l_request_id,
              interval => 5,
              max_wait => 120,
              phase => v_phase,
              status => v_status,
              dev_phase => v_request_phase,
              dev_status => v_request_status,
              message => v_message
              );


              select FND_DOCUMENTS_S.nextval
              into l_document_id1
              from dual;

              select FND_ATTACHED_DOCUMENTS_S.nextval
              into l_attached_document_id
              from dual;

              select nvl(max(seq_num),0) + 10
              into l_seq_num
              from fnd_attached_documents
              where pk1_value = l_document_id
              and entity_name = 'REQ_HEADERS';

              select substr(file_name,instr(file_name ,'/',-1,1)+1)
              into l_filename
              from FND_CONC_REQ_OUTPUTS where CONCURRENT_REQUEST_ID =l_request_id;

              fnd_documents_pkg.insert_row
              ( X_ROWID => l_rowid
              , X_DOCUMENT_ID => l_document_id1
              , X_CREATION_DATE => sysdate
              , X_CREATED_BY => fnd_profile.value('USER_ID')
              , X_LAST_UPDATE_DATE => sysdate
              , X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
              , X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
              , X_DATATYPE_ID => 6 -- FILE
              , X_CATEGORY_ID => l_category_id
              , X_SECURITY_TYPE => 4
              , X_PUBLISH_FLAG => 'Y'
              , X_USAGE_TYPE => 'O'
              , X_LANGUAGE => 'US'
              , X_DESCRIPTION => l_description
              , X_FILE_NAME => l_filename
              , X_MEDIA_ID => l_media_id
              );

              commit;
              INSERT INTO fnd_lobs (file_id
              ,file_name
              ,file_content_type
              ,upload_date
              ,expiration_date
              ,program_name
              ,program_tag
              ,file_data
              ,language
              ,oracle_charset
              ,file_format
              )
              values
              (
              l_media_id,
              k_filename,
              'application/pdf',
              sysdate,
              null,
              null,
              null,
              empty_blob (), --l_blob_data,
              null,
              null,
              'binary'
              ) returning file_data into l_blob;


              DBMS_LOB.CREATETEMPORARY(l_blob,true);
              l_bfile := bfilename ('EXAMPLEDIR3', l_filename);
              l_byte := dbms_lob.getlength(l_bfile);
              dbms_output.put_line ('size is '||l_byte);
              dbms_lob.fileopen (l_bfile);
              dbms_lob.loadfromfile (l_blob, l_bfile, l_byte);
              dbms_lob.fileclose(l_bfile);
              dbms_output.put_line ('document id is '||l_document_id1 );

              update fnd_lobs
              set file_data=l_blob
              where file_id=l_media_id;

              fnd_documents_pkg.insert_tl_row
              ( X_DOCUMENT_ID => l_document_id1
              , X_CREATION_DATE => sysdate
              , X_CREATED_BY => fnd_profile.value('USER_ID')
              , X_LAST_UPDATE_DATE => sysdate
              , X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
              , X_LAST_UPDATE_LOGIN =>fnd_profile.value('USER_ID')
              , X_LANGUAGE => 'US'
              , X_DESCRIPTION => l_description
              );
              commit;



              DBMS_OUTPUT.PUT_LINE ('MEDIA ID CREATED IS '||L_MEDIA_ID);

              SELECT file_name
              ,file_content_type
              ,file_data
              INTO filename
              ,content_type
              ,bdoc
              FROM fnd_lobs
              WHERE file_id = l_media_id;
              document_type := content_type || ';name=' || filename;


              dbms_lob.copy(document, bdoc, dbms_lob.getlength(bdoc));
              EXCEPTION
              WHEN OTHERS THEN
              dbms_output.put_line('The Error Code is'||sqlcode) ;
              dbms_output.put_line('The Error is'||sqlerrm) ;
              END ;
              /