This content has been marked as final. Show 9 replies
BLOBs (and CLOBs) can be written to the server's file system using UTL_FILE. Keep in mind to treat BLOBs as raw data and CLOBs as character data for file I/O.
UTL_FILE is documented in the [url http://oracle.telkom.co.za:7777/oracle/oradoc102/appdev.102/b14258/u_file.htm#sthref14093]Oracle® Database PL/SQL Packages and Types Reference guide.
Oh yes.. you also need to use DBMS_LOB to read the BLOBs contents. This is documented in the same guide as UTL_FILE.
Simplistically, you need to do the following (pseudo code):
select blob into blob_variable from table where filter condition open file using UTL_FILE loop read a raw chunk from the blob_variable into a raw buffer using DBMS_LOB write the raw buffer to disk using UTL_FILE exit when there are no more data to read from blob_variable end loop close file
I want to put an XML in a table's column using BLOBs.
I tried the following ways:
Created a table:
CREATE TABLE lob_table (id NUMBER, doc BLOB);
Created a directory:
create or replace directory XML_DIR as '\app\granite\rnd';
Created a PL/SQL procedure:
CREATE OR REPLACE
PROCEDURE BLOB_PROCEDURE AS
src_lob BFILE := BFILENAME('XML_DIR', '1.xml');
INSERT INTO lob_table VALUES(1, EMPTY_BLOB())
RETURNING doc INTO dest_lob;
DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
SRC_LOB => src_lob,
AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
when others then
I am using Oracle SQL Developer.
When I try to run the above procedure, it says:
"Source does not have a runnable target."
When I use Toad, the procedure executes properly, but there is no blob data in the table.
Kindly help me in this regard.
Please do not dig out a 4 years old post. There are lots of examples in this forum about inserting BLOB into database tables. See those, write yours and post in a new thread if you face any specific problem.
For tool specific problems (like SQL Developer and TOAD), they have separated forum, post the problem there.
Thanks for you reply Saubhik.
My earlier question involved in reading of all the related threads (in their respective forums) regarding insertion of an XML file into a BLOB using PL/SQL.
I also mentioned the method in which I tried and also posted the output there.
Kindly help me in achieving the task of successfully putting an XML file in a BLOB column of a table from a specified path.
My task(after insertion) is to retrieve the XML from the table and parse it. This parsing, I am able to do it using Java. No issues.
Thanks in advance. :)
What do you NOT understand? That ..
- resurrecting an old thread is unacceptable?
- hijacking someone else's thread for your own question is unacceptable?
Ask a question that you have as a new posting.
If you want to refer to another thread or posting, add a URL to your posting,
That simple. Asking questions meaningfully here (or ant other web forum) are governed by simple logical rules. Not rocket science.
So please follow these simple rules...