SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why DBMS_LOB.CONVERTTOBLOB gives me exception?

lxiscasMay 15 2013 — edited May 17 2013

Hi, guys:

I have a task to implement a download link in apex to download RTF file. I have finished the APEX part, however, I got the problem in the PL/SQl part. Here is my code:

      procedure downloadTest is
        
                strLetter clob;
                strLetter_len number;
                v_mime  varchar2(48) := 'application/msword';
                v_blob      BLOB;
               
                v_blob_offset integer;
                v_clob_offset integer;
                v_blob_csid integer:=DBMS_LOB.DEFAULT_CSID;
                v_lang_context integer:=DBMS_LOB.DEFAULT_LANG_CTX;
                v_warning integer;
                    
                
                l_server sor_email_config.server%type;
                l_sender sor_email_config.sender%type;

                error_message varchar2(500);
      
      begin
      
                  
              strLetter:='Test if this file downloadable!';
              --strLetter_len:=DBMS_LOB.GETLENGTH(strLetter);
              
              strLetter_len:=DBMS_LOB.LOBMAXSIZE;
              v_blob_offset:=1;
              v_clob_offset:=1;
              v_blob:=null;
              
               
              DBMS_LOB.CONVERTTOBLOB(v_blob, strLetter, strLetter_len, v_blob_offset, v_clob_offset, v_blob_csid, v_lang_context, v_warning); 
              
              OWA_UTIL.mime_header (NVL (v_mime, 'application/pdf'), FALSE);
               HTP.p ('Content-length: ' || strLetter_len);
               OWA_UTIL.http_header_close;
             
               WPG_DOCLOAD.download_file (v_blob);
               
             
      exception
          WHEN OTHERS THEN
                  error_message:=substr(SQLERRM, 1, 400);
                  raise_application_error(SOR_ERROR_CONSTANTS_PKG.errnum_GENERAL, 'SOR_COMPLIANCE_ADMIN.downloadTest when generating document '||SOR_ERROR_CONSTANTS_PKG.errmsg_GENERAL||error_message);
                       
            
      end downloadTest;

however, I got exception as follows:

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 for Execute PL/SQL Code.

I know the problem occurs during DBMS_LOB.CONVERTTOBLOB. Could anyone give me a hint about this problem?

Thanks a lot!

Sam

Edited by: lxiscas on May 15, 2013 10:09 AM