CODEWITHSUNDEEP
oracle-databaseoracle10gsql-loader
Code Geas Coder
Code Geas Coder

Reputation: 1979

oracle sql loader maximum size of a expression

I have a ctl file to use sqlldr, but the length of the expression is greater than 258 and is the minimum length that i can get in the query is impossible to me create a query with less characters.

My ctl is:

OPTIONS (PARALLEL=TRUE, SILENT=(HEADER,FEEDBACK), ERRORS=1000000)
LOAD DATA
INFILE 'file.csv'
--BADFILE 'file.bad'
APPEND INTO TABLE table1
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
  id_user CONSTANT 2,
  number_user ,
  FULL_TIMESTAMP  date "YYYY-MM-DD HH24:MI:SS",
  id_date ,
  id_time ,
  pn BOUNDFILLER,
  service_name EXPRESSION "select service_name from pack_table where service_name in (select service_name from table_2 where id_number in (select id_number from table_3 WHERE id_user=2 and (id_date between to_char(to_date(:id_date,'YYYYMMDD')-1,'YYYYMMDD') and :id_date) and number_user= :number_user))",
  bill_response ,
  joined CONSTANT 0
)

Oracle say me that the maximum length of a expression is 258 =(

I can not change the name of the colums of the tables.

My idea was use other expression BOUNDFILLER, but it not works for me ='(

OPTIONS (PARALLEL=TRUE, SILENT=(HEADER,FEEDBACK), ERRORS=1000000)
LOAD DATA
INFILE '_INFILE_'
--BADFILE '_INFILE_.bad'
APPEND INTO TABLE table1
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
  id_user CONSTANT _MNO_,
  number_user ,
  FULL_TIMESTAMP  date "YYYY-MM-DD HH24:MI:SS",
  id_date ,
  id_time ,
  pn BOUNDFILLER,
  ic "select id_number from (select id_number from table_3 WHERE id_user=2 and (id_date between to_char(to_date(:id_date,'YYYYMMDD')-1,'YYYYMMDD') and :id_date) and number_user= :number_user order by id_date asc) where rownum=1" BOUNDFILLER,
  service_name EXPRESSION "select service_name from pack_table where service_name in (select service_name from table_2 where id_number = :ic)",
  bill_response ,
  joined CONSTANT 0
)

i don't know what i can to do.

Who can help me?

Thanks

Upvotes: 0

Views: 286

Answers (1)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

move the select statement to a function and use select myfunction(params) from dual instead

Upvotes: 1

Related Questions