Thanks Tom,
I guess you left out the explaination abt indexes on LOB in my question.

Tom,
I did this:

create table test_lob (varchar_col varchar2(5), blob_col blob);

alter table test_lob modify blob_col default empty_Blob();

Then I SQL Load(ed) data using the control file:-,

LOAD DATA
INFILE *
INTO TABLE test_lob 
REPLACE
FIELDS TERMINATED BY ','
( varchar_col    CHAR,
   BLOB_FILE FILLER CHAR,
  "BLOB_COL" LOBFILE (BLOB_FILE) terminated by eof NULLIF BLOB_FILE = 'NONE'
)
BEGINDATA
doc1,test1.pdf

It returns error as:
SQL*Loader: Release 8.1.7.0.0 - Production on Fri Jun 7 10:55:31 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL*Loader-462: error inserting LOB into column "BLOB_COL", row 1, table TEST_LOB
SQL*Loader-646: lob set to EMPTY in column "BLOB_COL", row 1, table TEST_LOB
Commit point reached - logical record count 3

I did this after that:
SQL> select varchar_col from test_lob;

VARCH
-----
doc1

SQL> select blobtochar(blob_col) from test_lob;

BLOBTOCHAR(BLOB_COL)
--------------------------------------------------------------------------------

Didnt undestand whats happening.
Then i did this:

alter table test_lob modify blob_col ;

and ran SQLLoader...gave me the same error and same results on selecting the row.

Thanks



 

I cut and pasted your example "as is" with 8173 and:

$ sqlldr / test

SQL*Loader: Release 8.1.7.3.0 - Production on Fri Jun 7 21:15:08 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 1

(don't know why or even HOW you could be getting "logical record count 3"?

Then I:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select varchar_col, dbms_lob.getlength(blob_col) from test_lob;

VARCH DBMS_LOB.GETLENGTH(BLOB_COL)
----- ----------------------------
doc1                       3060340


which is exactly what I expected...


Well, wait -- I did just replicate your issue:

sqlldr / test

SQL*Loader: Release 8.1.7.3.0 - Production on Fri Jun 7 21:17:15 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL*Loader-462: error inserting LOB into column "BLOB_COL", row 1, table TEST_LOB
SQL*Loader-646: lob set to EMPTY in column "BLOB_COL", row 1, table TEST_LOB
Commit point reached - logical record count 3


the Logical record count gave it away.  You have extra blank lines in the ctl file -- you are trying to load 

LINE1: doc1,test1.pdf
LINE2:
LINE3: 

that is messing it up.  Correct your input file and all will be well.


 

Hi Tom,

I searched your site(load blob) but couldn't find (convinient to ourseleves) way to insert image to table.
We have specific request to insert image not via bfile, although that worked perfectly.
I wrote one procedure, and it works , but I think it is weird as I made some tricks, as converting from raw to varchar2 and vice versa.
Our VB guys tell that this is slower then way they do it
through ADO, in a way that they open a empty recordset and add a new record (rs.add) and simply assign a blob bytearray value (append chunk), and do recordset update.
I am sure that way through stored procedure should be faster but my code apparently is not.
So could you suggest different approach for that?
Is possible to insert blob to table via instead of trigger and if you have an example please share with us.


create or replace Procedure LoadImage2DB(pImage IN OUT NOCOPY BLOB)
as
Dest_loc BLOB;
Buffer RAW(32767);
amt BINARY_INTEGER := 32000;
bufchar varchar2(32000);
k number;
l number :=1;
BEGIN
insert into SlipImage (slipimage) values(empty_blob()) returning slipimage into dest_loc ;
DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
k := ceil(dbms_lob.getlength(pImage)/amt);
for i in 1..k
loop
bufchar := utl_raw.cast_to_varchar2(dbms_lob.substrpImage, amt, l));
l := amt+l;
Buffer := utl_raw.cast_to_raw (bufchar);
dbms_lob.writeappend( dest_loc, dbms_lob.getlength(buffer), Buffer );
end loop;
dbms_lob.close(dest_loc);
commit;
END LoadImage2DB;


Thanks

Finally discovered Holy Grail secret.

I have read a ton of doc (and many conversation from your site) but none said that I could do simply :

insert into slipImage( slipimage) values ( pImage );

Thanks a lot for this simplicity (or thanks to Oracle).

Oracle Version: 8.1.7.4

Tom,
Can chunk size be less than the database block size...
Our database block size is 8k....and average size of a file stored in the blob colum would be 4k....so can chunk be 4k?

Thanks.

Hi Tom,

we have lot of image files need to load into Oracle9i, can I use SQL*Loader to do this? any examples for control file.
table structure is very simple, id and image -- blob.

Thanks

Sharon

Hello Tom,

I am trying a simple insert into BLOB.. but getting the following error :

sql>select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

sql>declare
2 l_blob blob;
3 l_bfile bfile;
4 l_uti_file_dir varchar2(200) := '/u05/oradata/bacs2dev/utl_file_dir';
5 l_File_name varchar2(200) := 'array.sh';
6 begin
7
8 insert into t_report(PK_T_REPORT_ID,report_contents) values (1, empty_blob())
9 returning report_contents into l_blob;
10
11 l_bfile := bfilename( l_uti_file_dir, l_File_name);
12
13 dbms_lob.fileopen( l_bfile,dbms_lob.file_readonly );
14
15 dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
16
17 dbms_lob.fileclose( l_bfile );
18 end;
19 /
declare
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at line 13

bfiles work with directory objects.

create directory my_dir as '/u05.....'
/

....
  l_bfile := bfilename( 'MY_DIR', l_file_name );



your directory name is >30 characters (that is the max length of an identifier).  If it had been a valid length, you would get a more "meaningful" error:

 19  /
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at line 13
 


In any case:

ops$tkyte@ORA9IR2> create or replace directory MY_DIR as '/tmp/'
  2  /
 
Directory created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2       l_blob    blob;
  3       l_bfile   bfile;
  4       l_uti_file_dir varchar2(200) := 'MY_DIR';
  5       l_File_name varchar2(200) := 'array.sh';
  6  begin
  7
  8       insert into t_report(PK_T_REPORT_ID,report_contents) values (1, empty_blob())
  9       returning report_contents into l_blob;
 10
 11       l_bfile := bfilename( l_uti_file_dir, l_File_name);
 12
 13       dbms_lob.fileopen( l_bfile,dbms_lob.file_readonly );
 14
 15       dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
 16
 17       dbms_lob.fileclose( l_bfile );
 18  end;
 19  /
 
PL/SQL procedure successfully completed.
 

 

I had not created Directory Object before.. now I have rectified it and it works perfectly.

I want to add a default value to the CLOB column.
So first I updated all the empty records with the ####### data by using the following command.

SQL> update test_lob set test_lob_column = empty_clob() where dbms_lob.getlength(test_lob_column) = 0;
But when i try to alter the table I'm getting the following SQL and i got the error meesage

alter table test_lob modify blob_col default empty_Blob();

"ORA-02262: ORA-932 occurs while type-checking column default value expression".

Thanks
Babu 

But the default setting doesn't seems to be working. If I set Null value to CLOB column, my oracle forms screen displays '#######' for the entire page.

Thanks again
Babu

I have a table with one CLOB column which I'm trying to display in Forms. I'm having some difficulty displaying Null data in a CLOB column. If I update the CLOB column with Null value, it sores the Null.
But when I query the record, oracle form displays only "##########" instead of blank.
And Getting ORA-06502: Pl/SQL: number or value error. I'm not getting this error message when the data for the CLOB column is NOT null.

Thanks
Babu

Does CHUNK apply to LOBs that are stored inline?

Is there a data dictionary views that would tell me how many chunks are allocated for each LOB instance in the table?

Isn't CHUNK size an integer multiple of the Oracle blocksize?  Why couldn't I create a table with the following CHUNK sizes?

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
db_block_size                        integer     8192
SQL> 
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> 
SQL> DROP TABLE t;
DROP TABLE t
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> CREATE TABLE t (theBlob BLOB)
  2  LOB (THEBLOB) STORE AS (CHUNK 8192);

Table created.

SQL> 
SQL> DROP TABLE t;

Table dropped.

SQL> 
SQL> CREATE TABLE t (theBlob BLOB)
  2  LOB (THEBLOB) STORE AS (CHUNK 81920);
LOB (THEBLOB) STORE AS (CHUNK 81920)
                              *
ERROR at line 2:
ORA-22851: invalid CHUNK LOB storage option value


SQL> 
SQL> CREATE TABLE t (theBlob BLOB)
  2  LOB (THEBLOB) STORE AS (CHUNK 131072);
LOB (THEBLOB) STORE AS (CHUNK 131072)
                              *
ERROR at line 2:
ORA-22851: invalid CHUNK LOB storage option value