OraCmd - Oracle access without client tool »

Download OraCmd Free Trial »

Export LOB Data

OraCmd can export LOB (BLOB, CLOB) data.

A Short Example

This section contains a short example showing how OraCmd export LOB (BLOB, CLOB) data.

The Table

We used the following SQL statement to create the table:

	create table T_FILE
	(
	  ID          NUMBER not null,
	  FILEDATA    BLOB,
	  constraint PK_T_FILE primary key (ID)
	)
		

We need export the BLOB data in "FILEDATA" field.

OraCmd commmand

	exportlob table=t_file lobfield=filedata namefield=id fileext=dat 
	folder="E:\temp\lob" where="id<10";
		

Or a batch file(replace the userid with your own database user info. The lines wrap in this page, but in the file is 1 line):

	"C:\Program files\OraCmd\OraCmd" userid=hr/hr@localhost:1521:xe 
	task=exportlob table=t_file lobfield=filedata namefield=id fileext=dat 
	folder="E:\temp\lob" where="id<10" 
	log="E:\temp\exportlob.log" quit=y
		

See data files:

	E:\temp\lob>dir

	2008-07-30  20:21    <DIR>          .
	2008-07-30  20:21    <DIR>          ..
	2008-07-30  20:21             6,518 1.dat
	2008-07-30  20:21         5,571,204 2.dat
	2008-07-30  20:21            82,084 3.dat
	2008-07-30  20:21             1,033 4.dat
	2008-07-30  20:21            49,304 5.dat
	2008-07-30  20:21            85,799 6.dat
	2008-07-30  20:21           921,654 7.dat
	2008-07-30  20:21            36,304 8.dat
	2008-07-30  20:21            45,104 9.dat
		

schema: schema name, use this prameter to unload specific schema's data, if not set, use logon user's default schema

table: table name 

lobfield: the lob field to be exported

namefield: files named field. If not set, files will named by serial: 1, 2, 3

extfield: set file extension by field value

fileext: set file extension direct

where: where clause

folder: data files folder 

countpersub: save files to subfolder by every {countpersub} records, set this parameter will create subfolder automaticlly

buffer: Number of rows that will be transferred across the network at the same time. 
  This property can have a great impact on performance. 
  So it is preferable to choose the optimal value of buffer for each SQL statement and software - hardware configuration experimentally. 
  The default value is 25.