Here's my table:
SQL> desc stg_query_overflow
Name Null? Type
----------------------------------------- -------- ----------------------------
HOSTNAME VARCHAR2(50)
NPSID NUMBER
NPSINSTANCEID NUMBER
OPID NUMBER
LOGENTRYID NUMBER
SESSIONID NUMBER
SEQUENCEID NUMBER
NEXT NUMBER
QUERYTEXT CLOB
Here's my controlfile:
load data
infile '/u01/tony/server_name/query_overflow.dat'
badfile '/opt/oracle/tony/sql_dir/bad/server_name_query_overflow.bad'
discardfile '/opt/oracle/tony/sql_dir/discard/server_name_query_overflow.dsc'
append
into table stg_query_overflow
fields terminated by 'Ç'
trailing nullcols
(hostname constant 'server_name',npsid,npsinstanceid,opid,logentryid,sessionid,sequenceid,next,querytext CHAR(10000000) terminated by 'Ç')
Here's a sample of data that I can't load into the table via sqlldr:
\echo
\echo ***** Creating view: "pul_promotion_response"
CREATE or replace VIEW "pul_promotion_response"
(
"promo_rsp_id",
"promo_hist_dtl_id",
"indiv_id",
"kit_id",
"doc_id",
"rsp_src_id",
"rsp_ts",
"media_orig_id",
"extrn_id",
"rule_id",
"estar_id",
"offer_id",
.....
Here's the error(s) I receive in my log file:
Record 272: Rejected - Error on table STG_QUERY_OVERFLOW, column NPSID.
ORA-01722: invalid number
Record 273: Rejected - Error on table STG_QUERY_OVERFLOW, column NPSID.
ORA-01722: invalid number
As you can see, sqlldr is interpreting this vertical sql code as the npsid column, when in fact it is the querytext column. How can I insert each record when some of my data is in this vertical format?
Thanks.