6 Replies Latest reply on Jun 28, 2007 5:26 AM by 12324-Oracle

    How to store PDF file in BLOB column without using indirect datastore

    12324-Oracle
      Hi ,

      I want to store a pdf file in a BLOB column.
      But , it should be a direct store. I cannot usre indirect datastore.

      BLOB column doesn't support indirect datastore. I get the following error.

      ERROR at line 1:
      ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
      ORA-20000: Oracle Text error:
      DRG-10581: indirect datastores cannot be used with long or lob text columns
      ORA-06512: at "CTXSYS.DRUE", line 160
      ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 365

      can anyone give me any clue onhow to manage this issue?
        • 1. Re: How to store PDF file in BLOB column without using indirect datastore
          Roger Ford-Oracle
          There should be no problem if the PDF file is stored in the actual BLOB column. You should then just use the default DIRECT_DATASTORE.

          Are you specifying a datastore type? Which one? Would help to see your CREATE INDEX statement and any associated CREATE_PREFERENCE or SET_ATTRIBUTE statements.

          - Roger
          • 2. Re: How to store PDF file in BLOB column without using indirect datastore
            12324-Oracle
            Hi Roger,

            Thanks. I use something like this.

            create table test
            (
            id number primary key,
            docs varchar2(4000)
            );

            insert into test values(1,'test1.pdf');

            exec ctx_ddl.create_preference('common_dir','FILE_DATASTORE');
            exec ctx_ddl.set_attribute('common_dir','PATH','&1');

            exec ctx_ddl.create_preference('mylex','BASIC_LEXER');

            create index testx on test(docs) indextype is ctxsys.context
            parameters
            ('datastore common_dir filter ctxsys.AUTO_FILTER LEXER mylex
            storage test_store');

            now, I want to change the docs in Test table to BLOB
            • 3. Re: How to store PDF file in BLOB column without using indirect datastore
              Roger Ford-Oracle
              So you want to store the actual document contents in the BLOB? Then just remove the "datastore" clause from your create index statement and it should work.

              If you just want to store the filename in the BLOB column, you can't. It doesn't make a great deal of sense to use a Binary Long OBject to store a filename, so Oracle Text doesn't support BLOB columns for indirect datastores, such as the file datastore.
              • 4. Re: How to store PDF file in BLOB column without using indirect datastore
                12324-Oracle
                Thanks once more.
                So you want to store the actual document contents in the BLOB?
                Yes,I want this.
                Then just remove the "datastore" clause from your create index statement and it should work.
                So what you are saying is

                create table test
                (
                id number primary key,
                docs BLOB
                );

                after this, how do I insert the document - test.pdf ?

                After this is done, I understand that this how it should be.

                exec ctx_ddl.create_preference('mylex','BASIC_LEXER');
                create index testx on test(docs) indextype is ctxsys.context
                parameters
                ('filter ctxsys.AUTO_FILTER LEXER mylex
                storage test_store');
                • 5. Re: How to store PDF file in BLOB column without using indirect datastore
                  Roger Ford-Oracle
                  There are a number of ways of loading a file into a BLOB column, none of them completely trivial. You could use SQL*Loader - see FAQ here:

                  http://orafaq.com/faqloadr.htm#LOBS

                  or tutorial here:

                  http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_9.shtml

                  or you could use a BFILE as a temporary location in PL/SQL - see

                  http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_10.shtml#Load_BLOB_From_File_Image
                  • 6. Re: How to store PDF file in BLOB column without using indirect datastore
                    12324-Oracle
                    Thanks This really helped.

                    For other readers, I am summarising what I did.

                    create table test
                    (
                    id number primary key,
                    docs BLOB
                    );

                    create or replace directory doc_loc
                    as 'c:\test';

                    CREATE OR REPLACE PROCEDURE Load_BLOB_From_File (file_name in varchar2)
                    AS
                    src_loc bfile:= bfilename('DOC_LOC',Load_BLOB_From_File.file_name);
                    dest_loc BLOB;
                    begin
                    insert into tkctsf15t values(1,empty_blob()) returning docs
                    into dest_loc;
                    dbms_lob.open(src_loc,DBMS_LOB.LOB_READONLY);
                    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
                    DBMS_LOB.LOADFROMFILE(
                    dest_lob => dest_loc
                    ,src_lob => src_loc
                    ,amount => DBMS_LOB.getLength(src_loc));
                    DBMS_LOB.CLOSE(dest_loc);
                    DBMS_LOB.CLOSE(src_loc);
                    COMMIT;
                    end;
                    /

                    show errors;
                    exec Load_BLOB_From_File('test.pdf');

                    exec ctx_ddl.create_preference('mylex','BASIC_LEXER');

                    create index testx on test(docs) indextype is ctxsys.context
                    parameters
                    ('filter ctxsys.AUTO_FILTER LEXER mylex ');

                    select id from test where contains(docs,'patch')>0;

                    Thanks Roger once more