Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Yong.

Asked: August 23, 2000 - 10:56 am UTC

Last updated: April 16, 2012 - 4:14 pm UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Hi, Tom,

I've been using your p procedure very happily. It cuts a long
string into pieces, unlike dbms_output.put_line:

procedure p(p_str in varchar2)
as
l_str long default p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;

Now I want to combine this with your

select utl_raw.cast_to_varchar2(dbms_lob.substr(blob_col,2000,1))
from blobtable;

That is, I want to show *all* content of a BLOB column on
screen, in spite of its garbage-looking nature. Note I
want to see *all* not just the first 2000 characters.
(BTW, you said we can substr the first 4000. On my 8.1.5
database, I can only substr 2000, not 2001 or beyond.)

In fact, if there's a way to extract BLOB content into a file
in the file system, preferably using UTL_FILE, that would be
even better. Less importantly, is there any way to load a
binary file into BLOB using SQL*Loader? The Oracle manual for
SQL*Loader gives an example of loading CLOB which won't work
on BLOB for me. It's the example 5-13 at
</code> http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76955/ch05.htm <code>
I like to use LOBFILE but I don't think I can use TERMINATED
BY EOF.

Thank you very much.

Yong Huang
yhuang@indigopool.com


and Tom said...


do this to get the first 4000:

ps$tkyte@8i> create or replace function blobtochar( p_blob in blob ) return varchar2
2 as
3 begin
4 return utl_raw.cast_to_varchar2(dbms_lob.substr(p_blob,4000,1));
5 end;
6 /

Function created.

ops$tkyte@8i> select blobtochar(resume) from emp;


beyond 4000, cannot do it -- best we could do is write a stored procedure to dump some blobs but only upto 1,000,000 bytes of them. Beyond that -- no go. SQL won't let you go >4,000.

so, in sqlplus the ability to see the entire blob is not available.


As for writing the file to the OS, not using UTL_FILE. UTL_FILE will not do a binary file.

Do you have a C compiler? what is your OS? Do you have Pro*C Use Ask Tom to ask this if you want to persue it please... I can post an example of getting a lob to a file using an extproc. Java would work as well.

An example of using sqlldr to load a binary file is as follows. You do use the terminated by EOF since you want to load the entire file (load until EOF). EOF is not a ^D or ^Z -- its "the end of file".

I wrote a small C program:


void main()
{
int i;

for( i = 0; i < 1000; i++ )
printf( "%c", i%256 );
}

and ran it into allbytes.dat:

$ ./test > allbytes.dat

Then, I altered EMP to have a column "resume" of type blob and using the following CTL file:


LOAD DATA
INFILE *
INTO TABLE EMP
REPLACE
FIELDS TERMINATED BY ','
( EMPNO INTEGER EXTERNAL,
ENAME CHAR,
JOB CHAR,
MGR INTEGER EXTERNAL,
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO INTEGER EXTERNAL,
RES_FILE FILLER CHAR,
"RESUME" LOBFILE (RES_FILE) terminated by eof NULLIF RES_FILE = 'NONE'
)

BEGINDATA
7782,CLARK,MANAGER,7839,2572.50,,10,allbytes.dat


loaded the data. Now, in sqlplus I can see:



ops$tkyte@8i> select dbms_lob.getlength(resume) from emp;

DBMS_LOB.GETLENGTH(RESUME)
--------------------------
1000

it loaded all 1,000 bytes (and we know that it did not stop at ^D or ^Z since I put every ascii character in there). Using DUMP I can see that the data was input correctly:



1* select dump( utl_raw.cast_to_varchar2(dbms_lob.substr(resume,300,1)) ) from emp
ops$tkyte@8i> /

DUMP(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(RESUME,300,1))
------------------------------------------------------------
Typ=1 Len=300: 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,1
8,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,3
8,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,5
8,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,7
8,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,9
8,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113
,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128
,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143
,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158
,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173
,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188
,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203
,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218
,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233
,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248
,249,250,251,252,253,254,255,0,1,2,3,4,5,6,7,8,9,10,11,12,13
,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33
,34,35,36,37,38,39,40,41,42,43



All of the ascii codes are sequential from 0 on up -- as I expected. I get out what I put in.

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL*Loader and BLOB

Jo Sat, October 31, 2001 - 10:33 pm UTC

Tom..Thanks for very detail answer. I think to make it most useful, it would be helpful if you could also explain the last 2 lines of the control script: the RES_FILE ...
Thanks so much

Tom Kyte
November 01, 2001 - 8:20 am UTC

Sorry, thought that was somewhat obvious:

DEPTNO INTEGER EXTERNAL,
RES_FILE FILLER CHAR,
"RESUME" LOBFILE (RES_FILE) terminated by eof NULLIF RES_FILE = 'NONE'
)

BEGINDATA
7782,CLARK,MANAGER,7839,2572.50,,10,allbytes.dat



RES_FILE is a filler -- its not a column in the table I am loading. Its just there to hold whatever data is in the input load file (allbytes.dat in this example).

RESUME is a LOB. We are using the SQLLDR datatype LOBFILE(sqlldr_variable_with_name_of_file) to cause the file named in RES_FILE to be loaded into the LOB for us. The file is a simple one, terminated by EOF, not some file to be parsed specially... If we loaded a record in the data input stream with NONE were I have allbytes.dat -- the LOB would be NULL.

Helpful response, but why is UTL_FILE so lame?

Frustrated, November 01, 2001 - 9:36 am UTC

Your answer is very helpful. However, I marvel
every time I use UTL_FILE and realize how crummy it
really is. Why can't it read/write files of arbitrary
line lengths and file sizes? I breathe PL/SQL and write
Java with much less skill and efficiency. Yet silly restrictions in the libraries given to PL/SQL force me elsewhere. The one or two C programs from your site that
I've seen make use of Microsoft header files, which compels
me to install a Microsoft compiler, which I don't have. *sigh* Why can't I use what I have -- Oracle?

Tom Kyte
November 01, 2001 - 3:27 pm UTC

Well, UTL_FILE comments aside for a moment -- I'm at a loss to find a single article on my site with a "windows only" header? I write everything on Unix and have to port it to NT. Can anyone point me to an article here that has Windows specific C and no Unix counterpart? (i searched for "include h" -- and couldn't find one).

I'm somewhat insulted on that one ;)

To read anyfile, of any size and type -- BFILES with the dbms_lob pacakge work great. To write files -- it is true that UTL_FILE can only:

o create lines upto 32k in length.
o do TEXT IO -- no binary IO.
I don't have any solution for this beyond doing it in Java or C. Fortunately, these limits do not impact us very frequently.

sql

shalinderdeep singh, March 02, 2006 - 11:42 am UTC

hi i just need to know the code to pick up an image file in the same computer. basically i'm connecting an sqlplus codes to dreamweaver web page, i don't know how to insert an image file through the database, please help me if u can.
my email address is shalinder36@hotmail.com.
regards.

Using SQL Loader to load BLOB data

Darren, April 18, 2006 - 12:41 pm UTC

Exactly what I was looking for.

Perfect for me

Francois, April 20, 2006 - 11:22 am UTC

Very usefull for me.

Thanks

Applications Developer

Ed Wagner, September 07, 2006 - 9:19 am UTC

Tom: I love your site and use it frequently. I saw this and would like to suggest a possible alternative to extracting a blob as a varchar2. This may look familiary to you. Thanks for a great site and keep up the good work.

PROCEDURE WriteFileContent(p_FileID Number)
IS

MAX_LINE_WIDTH Constant PLS_INTEGER := 54;
lobData Blob;
dwFilePtr PLS_INTEGER := 1;
dwFileLen PLS_INTEGER;
dwBlockSize BINARY_INTEGER := 672 * 3; --ensures proper format; len=2016
dwBlocks PLS_INTEGER;
dwBytesAfterLastBlock PLS_INTEGER;
dwChunks PLS_INTEGER;
rawBuffer Raw(2100);
rawData Raw(2100);

BEGIN

SELECT blob_field, blob_size_bytes
INTO lobData, dwFileLen
FROM table_containing_blob
WHERE id = p_FileID;

dwBytesAfterLastBlock := MOD(dwFileLen, dwBlockSize);
dwBlocks := TRUNC(dwFileLen / dwBlockSize);
IF dwBytesAfterLastBlock <> 0 THEN
dwBlocks := dwBlocks + 1;
END IF;
DBMS_LOB.Read(lobData, dwBlockSize, dwFilePtr, rawBuffer);
rawData := NULL;
FOR dwBlock IN 1..dwBlocks LOOP
dwFilePtr := dwBlock * dwBlockSize + 1;
dwFileLen := dwFileLen - dwBlockSize;
rawData := UTL_RAW.Concat(rawData, rawBuffer);
dwChunks := TRUNC(UTL_RAW.Length(rawData) / MAX_LINE_WIDTH);
IF dwBlock <> dwBlocks THEN
dwChunks := dwChunks - 1;
END IF;
--TODO: do something with the data instead of htp.p-ing it
htp.p(UTL_RAW.cast_to_varchar2(rawData));
rawData := NULL;
IF dwFileLen < dwBlockSize AND dwFileLen > 0 THEN
dwBlockSize := dwFileLen;
END IF;
IF dwFileLen > 0 THEN
DBMS_LOB.Read(lobData, dwBlockSize, dwFilePtr, rawBuffer);
END IF;
END LOOP;

END WriteFileContent;


displaying pdf files in a form through developer 2000

chitra, November 18, 2006 - 5:44 am UTC

I work on oracle and D2K .
I want to store any format file from any local machine and store it into database, for backup purpose.
Also, i want to maintain a system where any user can see his or her saved file , Front end using is Developer 2000.
How to do all this job??????????????
1. save any file in database from any user's machine without giving them any access to the server.
2. after storing, how can one seehis/her file on the screen agin, not talking about character data only, rather it can be pdf,xls,dmp, or any other format also.


loading blob to file

AADHI, January 15, 2008 - 6:24 am UTC

Hi tom,
I did in the same method as you said to load a blob in to a file which is as follows

In my procedure 'dir' is the directory name and 'theblob' is table name and 'content' is the column name containg blob data..

when i execute this procedure i am able to load the blob into the directory but "NOT ABLE TO OPEN THE FILE"
Instead of recieving 69 kb file iam recieving 70 kb file
and when i open the file it say "gd token found "

CAN YOU PLEASE HELP ME WITH THIS PROBLEM

procedure createExtLocalPrice_temp12 (l_file_name varchar2)
IS

x NUMBER;
vblob BLOB;
vstart NUMBER:=1;
bytelen NUMBER := 10000;
len NUMBER;
my_vr RAW(32000);
l_output utl_file.file_type;
BEGIN

l_output := utl_file.fopen('dir', l_file_name, 'a',10000);

FOR l_cur IN (SELECT content FROM theblob WHERE id=1)
LOOP
len := DBMS_LOB.GETLENGTH(l_cur.theblob);
vblob := l_cur.theblob ;
dbms_output.put_line('Length of the Column : ' || TO_CHAR(len));
vstart := 1;
WHILE (vstart < len) LOOP -- loop till entire data is fetched
dbms_output.put_line('vstart : ' || TO_CHAR(vstart));
DBMS_LOB.READ(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
vstart := vstart + bytelen ;
END LOOP;
utl_file.fclose(l_output);
END LOOP;
COMMIT;
END createExtLocalPrice_temp12;


thanks
AADHI

Tom Kyte
January 15, 2008 - 7:23 am UTC

let me see, you say

"NOT ABLE TO OPEN THE FILE"

(shouting like...) and then you say

and when i open the file it say "gd token found "
    ^^^^^^^^^^^^^^^^^^^^


hmmmmmmmm. I cannot open the file, but when I open the file....

Perhaps you are a multi-byte database and you are creating a multi-byte unicode file and your text editor it not capable of dealing with the leading bytes in an unicode file

Re: Loading blob to File

Sawsan Anwar, June 28, 2008 - 10:02 am UTC

Hi Tom,

I have this problem with blob

I create table demo
CREATE TABLE DEMO
(
ID INTEGER,
THEBLOB BLOB
)
TABLESPACE blobtabs
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
LOB (THEBLOB) STORE AS
( TABLESPACE blobtabs
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL;

create tow directories
CREATE OR REPLACE DIRECTORY FROMMY_FILES AS 'C:\DATA';
To get files from this directory

CREATE OR REPLACE DIRECTORY TOMY_FILES AS 'C:\DATA2';
To put files from db to this directory


and create procedure to insert files to blob field

CREATE OR REPLACE procedure LOAD_A_FILE
as
l_clob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theblob into l_clob;
l_bfile := bfilename( FROMMY_FILES' , 'ASP.PDF' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );

commit;

end LOAD_A_FILE;
/
it work and insert file into blob and size of file'ASP.PDF' in Windows =2.97 KB from properties of file


and i do this proceure to get file from db


CREATE OR REPLACE procedure SAVE_TO_FILE
AS
vblob blob;
i2 number:=1;
amt number := 32676;
len number;
my_vr raw(32767);
l_output utl_file.file_type;
p_dir varchar2(30) default 'TOMY_FILES';
p_file varchar2(30) := 'TOASP.PDF';
Begin

l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
For l_cur in (SELECT theblob mylob FROM demo where id = 1)
Loop

len := DBMS_LOB.GETLENGTH(l_cur.mylob);
vblob := l_cur.mylob ;
dbms_output.put_line('Length of the Column : ' || to_char(len));

While (i2 < len) loop
dbms_output.put_line('i2 : ' || to_char(i2));
DBMS_LOB.READ(vblob,amt,i2,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
i2 := i2 + amt ;

End loop;
dbms_output.put_line('len : ' || to_char(len));
utl_file.fclose(l_output);

End loop;

end SAVE_TO_FILE;
/
also
it work put FILE into myfolder
BUT THE size of file'TOASP.PDF' in Windows =3.11 KB from properties of file
I CAN'T OPEN FILE AT ALL

Tom what is the wrrong the procedure which i wrote it or the utl_file


Regards


blob

A reader, March 30, 2010 - 6:59 pm UTC


How do I evaluate dbms_lob vs sqlldr for blob upload?

Mridul Mathew, April 16, 2012 - 9:42 am UTC 

Hello Tom,

I have been searching for the easiest way to upload blob content into the database, and this article interested me with the sqlldr option. 

My question is, is there any reason to prefer blob inserts through dbms_lob package over a simple sqlldr approach? Especially if I have 1000s of rows to insert, isnt it easier to just use sqlldr? Or am I missing something here? Any limitation with sqlldr perhaps?

Please advise.

Thanks in advance,
Mridul.

Tom Kyte
April 16, 2012 - 4:01 pm UTC



If you have 1,000's of files to load, sqlldr starts to look very appealing, yes.

we sort of said that with the advice above.

How do I evaluate dbms_lob vs sqlldr for blob upload?

Mridul Mathew, April 16, 2012 - 12:57 pm UTC 

Hi Tom,

Writing the previous question triggered another. Hope you can clarify.

* Oracle exp utility has direct=y as an option for exporting data, and it bypasses the sql evaluating buffer to speed up the export process by streaming data blocks on disk to client directly.

* sqlldr has direct=true, and per documentation, it does the same thing in reverse? To quote from http://www.orafaq.com/wiki/SQL*Loader_FAQ, "The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files."

My confusion is this -

If sqlldr and exp serve two different ends of the database spectrum, how are its loading/retrieving augmentations so uncannily similar?

And if direct path does the same thing for sqlldr and exp, shouldnt imp also have it?

Thanks in advance,
Mridul.

Tom Kyte
April 16, 2012 - 4:14 pm UTC

they are very different "direct" methods.

but sqlldr is a loader, export is an unloader - I don't know what you mean by your question?

imp is a legacy tool, really old - data pump has this ability and is what you should be using in the 21st century. Please ignore exp and imp, pretend they do not exist unless you are in 9i and before.

Loading of BLOB data with any kind of embedded file

surendra chintalwar, July 24, 2014 - 1:43 pm UTC

i am having Excel files in which ".pdf"(Embedded files in cells) files present in Excel file column cells & even it contains title in the same cells.
so wanted to insert title and pdf file in same cell into the oracle table column as it is.
please help me out how can we do this

Embedded File BLOB data importing

surendra chintalwar, July 25, 2014 - 12:04 pm UTC

Hi Tom,

I am having Excel file which contains SUMMARY column in which some text is there along with the embedded resume file[doc embedded file attached].

first_name Last_name Summary
ABC XYZ TITLE:IT'S MY RESUME ATTACHED IN cell

Table Script:
create table INFO(
first_name varchar2(10),
Last_name varchar2(10),
Summary BLOB
);

As I can not attach screenshot here so consider SUMMARY column having embedded resume file attached in a cell.
So,please let me know how can I insert summary column[contains both text and embedded file] present in excel file into BLOB datatype of oracle INFO table.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here