1 2 Previous Next 15 Replies Latest reply: Apr 17, 2012 6:53 AM by B Dave RSS

    Not able to search from BLOB(PDF File) using Oracle Text

    B Dave
      Hello.
      I'm not able to search any text which is stored inside PDF. And uploaded inside BLOB.

      Tried using Oracle Text but not working.

      This is what I'm doing.

      --table
      create table CONTENT_ORATEXT_BLOB
      ( CONTENT_ID number(2),
       pdf_file BLOB,
       created date,
       filename varchar2(150));
      
       --index
      Create index Content_ORATEXT_BLOBIndex on CONTENT_ORATEXT_BLOB(pdf_file) INDEXTYPE  
                                             is ctxsys.context PARAMETERS('SYNC (ON COMMIT)');
      
      
      --procedure
      CREATE OR REPLACE PROCEDURE LOAD_BLOB_FROM_FILE(id IN NUMBER, pfname IN VARCHAR2) IS
           src_file BFILE;
           dst_file BLOB;
           lgh_file BINARY_INTEGER;
         BEGIN
           src_file := bfilename('EXAMPLE_CLOB_DIR', pfname);
      
           INSERT INTO CONTENT_ORATEXT_BLOB
             (CONTENT_ID, pdf_file,created,filename)
           VALUES
             (id, EMPTY_BLOB(),sysdate,pfname)
           RETURNING pdf_file INTO dst_file;
           lgh_file:=dbms_lob.getlength(src_file);
           DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
           DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
           dbms_lob.close(src_file);
           COMMIT;
           dbms_output.put_line('File : '|| pfname || ' successfully loaded');
      end;
      /
      
      --inserted two pdf files successfully.
      exec LOAD_BLOB_FROM_FILE(1,'sample.pdf');
      exec LOAD_BLOB_FROM_FILE(2,'largepdf.pdf');
      
      --search
      SELECT t.content_id,t.created,t.filename FROM CONTENT_ORATEXT_BLOB  t 
      WHERE CONTAINS (t.pdf_file,'UGO') >0; 
      I used above code for CLOB. (for .doc and .xls files)
      And it worked like charm.
      But still not able to search from PDF(BLOB).

      Thanks.

      Regards,
      Dave

      Edited by: 897450 on Mar 15, 2012 4:57 AM
        1 2 Previous Next