Home » RDBMS Server » Server Utilities » loading vertical data using sqlldr (Oracle RDBMS, 11.1, RHEL5.4)
loading vertical data using sqlldr [message #594119] Fri, 23 August 2013 12:21 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
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.
Re: loading vertical data using sqlldr [message #594139 is a reply to message #594119] Fri, 23 August 2013 17:03 Go to previous message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
You need to provide a few full rows of sample data and what you want the results of a select * to be after those rows are loaded.
Previous Topic: Loading String has double quotes in it
Next Topic: Exporting/Importing partitioned table
Goto Forum:
  


Current Time: Wed Sep 17 23:00:02 CDT 2014

Total time taken to generate the page: 0.09233 seconds