7 Replies Latest reply: Dec 4, 2010 4:58 PM by 788763 RSS

    how to store the pdf file

    615573
      hi all,
      i need a pl/sql code to store the pdf file into the oracle database using blob.

      help me.
      Thanks in advance
        • 1. Re: how to store the pdf file
          251301
          My first question is "where is the PDF file located"? If the answer is "on a file system accessible to the Oracle database" Then you can use directory objects and the DBMS_LOB package. It would look something like this:

          as SYS user do:

          -- assume the PDF files are in /data/documents filesystem directory
          create or replace directory pdfdir as '/data/documents';
          grant read on directory pdfdir to <USER>;

          as USER do:

          create table mydocs (id integer primary key, doc blob);

          declare
          bf bfile;
          b blob;
          src_offset integer := 1;
          dest_offset integer := 1;
          begin
          -- insert a new blob and return it to local variable
          insert into mydocs values(1, empty_blob()) returning doc into b;

          -- open the bfile for file "summary.pdf"
          bf := bfilename('PDFDIR', 'summary.pdf');
          dbms_lob.loadBlobFromFile(b, bf, dbms_lob.lobmaxsize, dest_offset, src_offset);

          -- done
          commit;
          end;
          /
          • 2. Re: how to store the pdf file
            615573
            Hi rabbott,

            Oracle server
            Os: Win2000Adv Ser
            Ora : 9i
            File system : FAT32

            Oracle Client
            Os: Win2000Adv Ser
            File system : FAT32
            Ora : 9i

            Pdf file location : ‘d:\pdf\test.pdf ‘ (In client system)

            The pdf file is in client system and I need to load it from the client.

            Logon : as sysdba (server)

            I have created the directory with your command as :
            ‘ create or replace directory pdfdir as ‘d:/pdf’; ’
            ‘ grant read on directory pdfdir to user1; ’


            Logon : user1 (client)

            I have create the table using :

            ‘ CREATE TABLE MYDOCS(id integer primary key,doc blob); ‘

            1 declare
            2 bf bfile;
            3 b blob;
            4 src_offset integer :=1;
            5 dest_offset integer:=1;
            6 begin
            7 insert into mydocs values(1,empty_blob()) returning doc into b;
            8 bf:=bfilename('PDFDIR','a.pdf');
            9 dbms_lob.loadBlobFromFile(b,bf,dbms_lob.lobmaxsize,
            10 dest_offset,src_offset);

            11 commit;
            12* end;
            SQL> /
            declare
            *
            ERROR at line 1:
            ORA-22289: cannot perform LOADFROMFILE operation on an unopened file or LOB
            ORA-06512: at "SYS.DBMS_LOB", line 625
            ORA-06512: at line 9


            please help me how to resolve this problem.

            Thank u
            • 3. Re: how to store the pdf file
              251301
              If you are loading from a client machine then you won't be able to use pl/sql since those programs run in the Oracle Database on the server.

              Depending on what programming languages you use:

              If you are using .NET then look at the ODB.NET documentation for how to program with BLOBs

              If you are using Java then you can look at the Multimedia Quick Start for Java guide to learn how to program with Java and Images. You would need to modify this to use Blobs in stead of ORDImage columns.

              If you are using a web server then you can use the browser to upload and the webserver will insert into the databse. You will need to write the code for the webserver.
              • 4. Re: how to store the pdf file
                251301
                I assumed that you are working with 2 different machines, one client, one server.

                If you are working on a single machine, there may be a simple bug in the code.

                try adding the line
                dbms_lob.open(bf, dbms_lob.file_readonly);

                between the calls to bfilename() and loadBlobFromFile()
                • 5. Re: how to store the pdf file
                  615573
                  hi rabbott,
                  Thanks for ur reply. i have included that code in my pl/sql. The pdf file is stored within the database. i have checked it out. but i cannot able to retrieve that pdf file from the database. plz help me out how to retrieve it.
                  • 6. Re: how to store the pdf file
                    251301
                    You need to decide what retrieve means. What type of component is the final destination for this PDF file? And what are the protocols and programming languages that will be used?

                    You can learn more looking at the Multimedia Quick Start guides for PL/SQL and Java clients. You should also look at the Application Dev Guide for Large Objects (BLOBs) and read the documentation for the PL/SQL DBMS_LOB package.
                    • 7. Re: how to store the pdf file
                      788763
                      rabbott wrote:

                      My first question is "where is the PDF file located"? If the answer is "on a file system accessible to the Oracle database" Then you can use directory objects and the DBMS_LOB package. It would look something like this:



                      as SYS user do:



                      -- assume the PDF files are in /data/documents <font face="tahoma,verdana,sans-serif" size="1" color="#000">files</font>ystem directory

                      create or replace directory pdfdir as '/data/documents';

                      grant read on directory pdfdir to <USER>;



                      as USER do:



                      create table mydocs (id integer primary key, doc blob);



                      declare

                      bf bfile;

                      b blob;

                      src_offset integer := 1;

                      dest_offset integer := 1;

                      begin

                      -- insert a new blob and return it to local variable

                      insert into mydocs values(1, empty_blob()) returning doc into b;



                      -- open the bfile for file "summary.pdf"

                      bf := bfilename('PDFDIR', 'summary.pdf');

                      dbms_lob.loadBlobFromFile(b, bf, dbms_lob.lobmaxsize, dest_offset, src_offset);



                      -- done

                      commit;

                      end;

                      /
                      I have the issue which is similar to what you have faced, I'll follow what you said to take a try, Thanks a lot!