OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » LOB & CLOB

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

LOB & CLOB [message #59198]

Mon, 03 November 2003 23:29

Sudheer
Messages: 19
Registered: July 1999

Junior Member

How can we create an table with datatype LOB and CLOB and how can we insert 100mb of text file into that field.thanks in advance

Report message to a moderator

Re: LOB & CLOB [message #59199 is a reply to message #59198]

Tue, 04 November 2003 00:09

Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas

Senior Member

Hi,

Well LOB itself is not a datatype which u can u use in ur table. To use clob just use clob datatype as u use varchar2 but dont specify any size for the column.

If u want to store text which is stored in any text file into ur table then u have to write a PL/SQL code in which u have to read the file and put the read data into the table. Further to read the text from the file u have to use UTL_FILE package. Just read the documentation to use it.

Alternatively u can use BFILE datatype instead of CLOB.
It will store the pointer of ur OS files in the database instead of storing the whole data. To deal with BFILE datatype you have to use DBMS_LOB package supplied by ORACLE. Again read the documentation to use it.

Special care should also be taken in case of BFILE bcz ur data is in the form of OS files which r stored openely on the drive and if someone delete the file then u will lose the data. Means oracle doesn't guarantee abt the data stored in the form of BFILE.

Report message to a moderator

Re: LOB & CLOB [message #59203 is a reply to message #59199]

Tue, 04 November 2003 02:03

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You have 3 types of LOB objects:
1. CLOB (Character Large Object)
2. BFILE ( Which is indeed a pointer to OS files)
3. BLOB (Binary Large Object)

The limit of BLOB and CLOB is 4GB I believe.

You can create a table with a BLOB column just like you'd create any other table:

SQL> create table t1(c1 blob, c2 blob, c3 clob);

Table created.

SQL> desc t1
 Name                            Null?    Type
 ------------------------------- -------- ----
 C1                                       BLOB
 C2                                       BLOB
 C3                                       CLOB

Now, for a text you might want to use a CLOB. In the DBMS_LOB package, you even have the LOADFROMFILE procedure.

If you want to load the file from your client, you would need a Java API I'm afraid.

MHE

Report message to a moderator

Previous Topic:	QUERY_REWRITE_INTEGRITY
Next Topic:	updating

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 14 21:42:13 CST 2025