9 Replies Latest reply: Oct 6, 2010 3:20 PM by mannamal RSS

    dbms_lob.loadfromfile help

    540875
      sorry in advance if this is basic - SQL developer virgin here . . .

      Using the examples in this document
      http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10840/mm_uses.htm

      specifically the code:

      INSERT INTO soundtable(id, sound) VALUES (1, EMPTY_BLOB());
      COMMIT;

      DECLARE
      f_lob BFILE := BFILENAME('AUDDIR','chimes.wav');
      b_lob BLOB;
      Lob BLOB;
      Length INTEGER;
      BEGIN

      SELECT sound INTO b_lob FROM soundtable WHERE id=1 FOR UPDATE;

      -- Open the LOBs.
      dbms_lob.open(f_lob, dbms_lob.file_readonly);
      dbms_lob.open(b_lob, dbms_lob.lob_readwrite);
      dbms_lob.loadfromfile
      (b_lob, f_lob, dbms_lob.getlength(f_lob));
      -- Close the LOBs.
      dbms_lob.close(b_lob);
      dbms_lob.close(f_lob);
      COMMIT;

      -- Select the LOB:
      SELECT sound INTO Lob FROM soundtable
      WHERE ID = 1;
      -- Opening the LOB is optional.
      DBMS_LOB.OPEN (Lob, DBMS_LOB.LOB_READONLY);
      -- Get the length of the LOB.
      length := DBMS_LOB.GETLENGTH(Lob);
      IF length IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('LOB is null.');
      ELSE
      DBMS_OUTPUT.PUT_LINE('The length is '|| length);
      END IF;
      -- Closing the LOB is mandatory if you have opened it.
      DBMS_LOB.CLOSE (Lob);

      END;
      /


      2 questions for anyone who has done this before or has an opinion:

      1) In the initial insert -- INSERT INTO soundtable(id, sound) VALUES (1, EMPTY_BLOB()); -- if the value for the column "id" is sequence.next_val I could end up with an "id" of 3012 for example:

      so how would I find out what value the "id" is before doing the next step:
      SELECT sound INTO b_lob FROM soundtable WHERE id=1 FOR UPDATE;

      I would have to do:
      SELECT sound INTO b_lob FROM soundtable WHERE id=<whatever_was_generated_by_the_sequence> FOR UPDATE;

      and if there are 10 people inserting at about the same time, how the heck am I supposed to know which "id" goes with which content file?

      any help is appreciated
        • 1. Re: dbms_lob.loadfromfile help
          Barbara Boehmer
          You can use the returning clause to store the generated sequence value that was inserted into id into a variable, then reference that variable in the rest of your code, as demonstrated below.
          SCOTT@10gXE> CREATE TABLE soundtable
            2    (id    NUMBER,
            3       sound BLOB DEFAULT EMPTY_BLOB ())
            4  /

          Table created.

          SCOTT@10gXE> CREATE SEQUENCE your_sequence
            2  /

          Sequence created.

          SCOTT@10gXE> VARIABLE g_id_seq NUMBER
          SCOTT@10gXE> INSERT INTO soundtable (id)
            2  VALUES (your_sequence.NEXTVAL)
            3  RETURNING id INTO :g_id_seq
            4  /

          1 row created.

          SCOTT@10gXE> COMMIT
            2  /

          Commit complete.

          SCOTT@10gXE> CREATE OR REPLACE DIRECTORY auddir AS 'C:\WINDOWS\Media'
            2  /

          Directory created.

          SCOTT@10gXE> SET SERVEROUTPUT ON
          SCOTT@10gXE> DECLARE
            2    f_lob  BFILE := BFILENAME ('AUDDIR', 'chimes.wav');
            3    b_lob  BLOB;
            4    Lob    BLOB;
            5    Length INTEGER;
            6  BEGIN
            7 
            8    SELECT sound
            9    INTO   b_lob
          10    FROM   soundtable
          11    WHERE  id = :g_id_seq
          12    FOR UPDATE;
          13 
          14    dbms_lob.open (f_lob, dbms_lob.file_readonly);
          15    dbms_lob.open (b_lob, dbms_lob.lob_readwrite);
          16    dbms_lob.loadfromfile
          17        (b_lob, f_lob, dbms_lob.getlength (f_lob));
          18    dbms_lob.close(b_lob);
          19    dbms_lob.close(f_lob);
          20    COMMIT;
          21 
          22    SELECT sound
          23    INTO   Lob
          24    FROM   soundtable
          25    WHERE  ID = :g_id_seq;
          26    length := DBMS_LOB.GETLENGTH (Lob);
          27    IF length IS NULL THEN
          28        DBMS_OUTPUT.PUT_LINE ('LOB is null.');
          29    ELSE
          30        DBMS_OUTPUT.PUT_LINE ('The length is '|| length);
          31    END IF;
          32  END;
          33  /
          The length is 55776

          PL/SQL procedure successfully completed.

          SCOTT@10gXE>
          • 2. Re: dbms_lob.loadfromfile help
            540875
            Thank you Barbara, I appreciate your help.

            Do you think the same example would apply if I were to use something other than a sequence to generate the "id" value?

            I'm thinking about using sys_guid() to generate the value of the "id" column, but the downside is that I cannot then use the NEXTVAL feature in the RETURNING ... INTO... clause.

            The sys_guid() value will be a randomly generated 32 byte number. Do you have an example how I can capture that value by using the RETURNING...INTO clause?

            Thanks in advance
            • 3. Re: dbms_lob.loadfromfile help
              Barbara Boehmer
              SCOTT@10gXE> CREATE TABLE soundtable
                2    (id    RAW (32),
                3       sound BLOB DEFAULT EMPTY_BLOB ())
                4  /

              Table created.

              SCOTT@10gXE> CREATE OR REPLACE DIRECTORY auddir AS 'C:\WINDOWS\Media'
                2  /

              Directory created.

              SCOTT@10gXE> SET SERVEROUTPUT ON
              SCOTT@10gXE> DECLARE
                2    v_id   RAW (32);
                3    f_lob  BFILE := BFILENAME ('AUDDIR', 'chimes.wav');
                4    b_lob  BLOB;
                5    Lob    BLOB;
                6    Length INTEGER;
                7  BEGIN
                8    INSERT INTO soundtable (id)
                9    VALUES (sys_guid)
              10    RETURNING id INTO v_id;
              11 
              12    SELECT sound
              13    INTO   b_lob
              14    FROM   soundtable
              15    WHERE  id = v_id
              16    FOR UPDATE;
              17 
              18    dbms_lob.open (f_lob, dbms_lob.file_readonly);
              19    dbms_lob.open (b_lob, dbms_lob.lob_readwrite);
              20    dbms_lob.loadfromfile
              21        (b_lob, f_lob, dbms_lob.getlength (f_lob));
              22    dbms_lob.close(b_lob);
              23    dbms_lob.close(f_lob);
              24    COMMIT;
              25 
              26    SELECT sound
              27    INTO   Lob
              28    FROM   soundtable
              29    WHERE  ID = v_id;
              30    length := DBMS_LOB.GETLENGTH (Lob);
              31    IF length IS NULL THEN
              32        DBMS_OUTPUT.PUT_LINE ('LOB is null.');
              33    ELSE
              34        DBMS_OUTPUT.PUT_LINE ('Id from sys_guid is ' || v_id);
              35        DBMS_OUTPUT.PUT_LINE ('Length of blob is ' || length);
              36    END IF;
              37  END;
              38  /
              Id from sys_guid is 1AD6ED5CFC204B778221EE7EF23F38FA
              Length of blob is 55776

              PL/SQL procedure successfully completed.

              SCOTT@10gXE>
              • 4. Re: dbms_lob.loadfromfile help
                540875
                Barbara -

                Well, I don't think saying "Thank You" quite sums up my appeciation for your kindness and the example helping me understand this.

                I very much appreciate your help :-)
                • 5. Re: dbms_lob.loadfromfile help
                  540875
                  Barbara -

                  One last question (very sorry)

                  The directory 'C:\WINDOWS\Media' you mention in

                  CREATE OR REPLACE DIRECTORY auddir AS 'C:\WINDOWS\Media'

                  this directory has to be on the Oracle Server (?), or can it be on the Web Application server that is running the IIS (or Tomcat) where the application code is?

                  If my application is running on a Windows Server, using IIS, and users load content from their PC, via a web browser, onto the Web App. Server (running IIS) I can put the incoming file in the 'C:\WINDOWS\Media' directory. But if I'm running my Oracle Server on a separate server under Linux, I would not be able to use 'C:\WINDOWS\Media' as my directory . . . I suppose I'd have to use a directory on Linux ('/oracle/media' for example) - but then how would the file go from user PC --> Browser --> App Server --> Oracle Server while still keeping track of which file belongs to which metadata?

                  It's probably a very simple solution, but I'm so new to this that I can't yet understand how that works.

                  Thanks again in advance for any advice you can provide, as always I appreciate it :-)
                  • 6. Re: dbms_lob.loadfromfile help
                    Barbara Boehmer
                    The file needs to be on the Oracle server, so that Oracle can see it, not on a local machine. It could be put in any directory that you choose on the server , then you can create an Oracle directory object that points to that path. When you issue "create or replace directory" it does not create a directory on your operating system, it just creates an Oracle directory object to that path. The path that you supply is not validated at the time that you create the directory object. It is validated when you try to use it. So, you could "create or replace directory whatever as 'some non-existenet path or jibberish' and not receive an error, until you tried to access that directory object whatever. I ran the example that I provided on a notebook computer, so my C: drive is on my server and I just happened to already have the .wav file in the windows\media directory, so I used that. I believe in Linux, your directory paths will be slightly different, with the slashes in the other direction. If you are going to upload multiple files with the same name from different sources at the same time, they will overwrite one another, unless you provide unique names somehow. I don't know how you will get your files from one place to another. Perhaps FTP? I don't have a clear picture of your overall application, so I don't quite know what you need to do. You might want to start a new topic regarding the file transfer from user PC --> Browser --> App Server --> Oracle Server, so that others with more expertise in that area will see it and respond.
                    • 7. Re: dbms_lob.loadfromfile help
                      417291
                      Hi Barbara -

                      Thanks for the additional information. I did happen to find a good article on the internet at
                      http://www.openfree.org/pet/index.php/Mount_an_NFS_share_from_Windows

                      which describes how to NFS mount Windows to Linux, so I would be able to create a directory on the Linux side which is actually a NFS share to the Windows machine where the content will be.

                      Thanks again for your help, I really appreciate it.
                      • 8. Re: dbms_lob.loadfromfile help
                        Don G
                        I follow the example below for loading blobs. In fact I am loading PDF files into the database. However I am finding that about 2% of my images are loading as 0 bytes. Thus the image is not loading. The image on the UNIX hard drive is not corrupt. Is selecting the image and checking the length the best way to verify it loaded. I am selecting where dbms_lob.getlength(BACKUP_DOCS)= 0 to tell me which records did not load and I can reload them.

                        So there are two questions:
                        1. Why would an image not load following the example?
                        2. What is the best way to verify a file did load?

                        Thank you
                        • 9. Re: dbms_lob.loadfromfile help
                          mannamal
                          Checking the length is a good way to see if an image has been loaded. I am not sure why 2% of your images are not loading. Are they in a different location? I see that the example does not include a PL/SQL exception block. Perhaps the LOB_OPEN call for those 2% images is throwing an exception and seeing what error that is will give you an idea of what could be going wrong for those 2% of your images?

                          Melli