Tom,
I'm trying to use the function BFILENAME, along with DBMS_LOB.GETLENGTH to return the size of each BLOB that I am loading with SQLLDR. But I get the error "column not found".
select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
CREATE TABLE ATTACHMENT
(
ATTACHMENT_SEQ" NUMBER,
FILENAME VARCHAR2(256 BYTE) NOT NULL ENABLE,
DESCRIPTION VARCHAR2(256 BYTE),
MIMETYPE VARCHAR2(50 BYTE),
UPLOADED_DT DATE DEFAULT sysdate,
USER_SEQ NUMBER,
FILE_CONTENT BLOB,
OBSOLETE NUMBER(1,0) DEFAULT 0,
LAST_UPDATE_USER_SEQ NUMBER,
LAST_UPDATE_DATE DATE DEFAULT sysdate,
FILE_SIZE NUMBER
)
/
CREATE OR REPLACE DIRECTORY BLOB_DIR as '/tmp';
GRANT READ, WRITE ON DIRECTORY BLOB_DIR TO jimmyb ;LOAD DATA
infile *
append INTO TABLE attachment
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
( attachment_seq SEQUENCE(MAX,1)
, filename CHAR
, description CONSTANT 'appraisal'
, mimetype CONSTANT 'application/pdf'
, uploaded_dt SYSDATE
, user_seq CHAR
, file_content LOBFILE(filename) TERMINATED BY EOF
, obsolete CONSTANT '0'
, last_update_user_seq CONSTANT '3070'
, last_update_date SYSDATE
, file_size INTEGER "dbms_lob.getlength(BFILENAME('BLOB_DIR', filename))"
)
begindata
"Smith, Tommy D.pdf",9000
"Jones, Sammy D.pdf",9015
"James, Robert T..pdf",9043
Record 1: Rejected - Error on table ATTACHMENT, column FILE_SIZE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table ATTACHMENT, column FILE_SIZE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table ATTACHMENT, column FILE_SIZE.
ops$tkyte%ORA10GR2> CREATE TABLE ATTACHMENT
2 (
3 ATTACHMENT_SEQ NUMBER,
4 FILENAME VARCHAR2(256 BYTE) NOT NULL ENABLE,
5 DESCRIPTION VARCHAR2(256 BYTE),
6 MIMETYPE VARCHAR2(50 BYTE),
7 UPLOADED_DT DATE DEFAULT sysdate,
8 USER_SEQ NUMBER,
9 FILE_CONTENT BLOB,
10 OBSOLETE NUMBER(1,0) DEFAULT 0,
11 LAST_UPDATE_USER_SEQ NUMBER,
12 LAST_UPDATE_DATE DATE DEFAULT sysdate,
13 FILE_SIZE NUMBER
14 )
15 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE OR REPLACE DIRECTORY BLOB_DIR as '/tmp';
Directory created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> !pwd
/tmp
<b>have to be where the files are given that you have no path on them..</b>
ops$tkyte%ORA10GR2> !cat a.ctl
LOAD DATA
infile *
append INTO TABLE attachment
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
<b>trailing nullcols</b>
( attachment_seq SEQUENCE(MAX,1)
, filename CHAR
, description CONSTANT 'appraisal'
, mimetype CONSTANT 'application/pdf'
, uploaded_dt SYSDATE
, user_seq CHAR
, file_content LOBFILE(filename) TERMINATED BY EOF
, obsolete CONSTANT '0'
, last_update_user_seq CONSTANT '3070'
, last_update_date SYSDATE
, file_size INTEGER "dbms_lob.getlength(BFILENAME('BLOB_DIR', <b>:filename</b>))"
)
begindata
"x.pdf",9000
"y.pdf",9015
"z.pdf",9043
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> !sqlldr / a
SQL*Loader: Release 10.2.0.4.0 - Production on Fri Jul 23 15:44:59 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 3
ops$tkyte%ORA10GR2> !ls -l [x-z].pdf
-rw-rw-r-- 1 tkyte tkyte 114476 Jul 23 15:39 x.pdf
-rw-rw-r-- 1 tkyte tkyte 16634 Jul 23 15:44 y.pdf
-rw-rw-r-- 1 tkyte tkyte 112574 Jul 23 15:44 z.pdf
ops$tkyte%ORA10GR2> select filename, file_size from attachment;
FILENAME FILE_SIZE
-------------------- ----------
x.pdf 114476
y.pdf 16634
z.pdf 112574