Re: Error while trying direct load in sql loader
From: joel garry <joel-garry_at_home.com>
Date: Wed, 21 Sep 2011 09:21:22 -0700 (PDT)
Message-ID: <c1f4383e-36d2-40fb-bb08-59d22459143b_at_y7g2000yqh.googlegroups.com>
On Sep 21, 1:56 am, Sandy80 <svarshneym..._at_gmail.com> wrote:
> Hi,
>
> I have a table which I load through the sql loader using the following
> command:
>
> ${ORACLE_HOME}/bin/sqlldr ${DATABASE_USER}/${USER_PASS}_at_${DATABASE}
> control=${LOAD_ROOT}/ctrl/${TABLE_NAME}.ctrl data=${DATA_DIR}/$
> {TABLE_NAME}.dat log=${LOAD_ROOT}/log/${TABLE_NAME}.log bad=$
> {LOAD_ROOT}/bad/${TABLE_NAME}.bad discard=${LOAD_ROOT}/discard/$
> {TABLE_NAME}.dsd errors=100000 rows=10000 readsize=10000000
> bindsize=10000000
>
> Since this table gets more than 2.7m I wanted to improve the load
> performance and therefore tried the "direct" load on it. I try the
> direct load using the following command:
>
> ${ORACLE_HOME}/bin/sqlldr ${DATABASE_USER}/${USER_PASS}_at_${DATABASE}
> control=${LOAD_ROOT}/ctrl/${TABLE_NAME}.ctrl data=${DATA_DIR}/$
> {TABLE_NAME}.dat log=${LOAD_ROOT}/log/${TABLE_NAME}.log bad=$
> {LOAD_ROOT}/bad/${TABLE_NAME}.bad discard=${LOAD_ROOT}/discard/$
> {TABLE_NAME}.dsd errors=100000 rows=10000 readsize=10000000
> bindsize=10000000 \multithreading=true direct=true load=2000000 &
>
> As soon as I try this direct load command it gives me the following
> error for each record and rejects all of them:
>
> Record 5: Rejected - Error on table XXXXXXXXXXX.
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01861: literal does not match format string
>
> Let me know if any of you have faced a similar issue and can provide
> an idea on what could be the issue.
> Any help would be appreciated. Thanks!
Date: Wed, 21 Sep 2011 09:21:22 -0700 (PDT)
Message-ID: <c1f4383e-36d2-40fb-bb08-59d22459143b_at_y7g2000yqh.googlegroups.com>
On Sep 21, 1:56 am, Sandy80 <svarshneym..._at_gmail.com> wrote:
> Hi,
>
> I have a table which I load through the sql loader using the following
> command:
>
> ${ORACLE_HOME}/bin/sqlldr ${DATABASE_USER}/${USER_PASS}_at_${DATABASE}
> control=${LOAD_ROOT}/ctrl/${TABLE_NAME}.ctrl data=${DATA_DIR}/$
> {TABLE_NAME}.dat log=${LOAD_ROOT}/log/${TABLE_NAME}.log bad=$
> {LOAD_ROOT}/bad/${TABLE_NAME}.bad discard=${LOAD_ROOT}/discard/$
> {TABLE_NAME}.dsd errors=100000 rows=10000 readsize=10000000
> bindsize=10000000
>
> Since this table gets more than 2.7m I wanted to improve the load
> performance and therefore tried the "direct" load on it. I try the
> direct load using the following command:
>
> ${ORACLE_HOME}/bin/sqlldr ${DATABASE_USER}/${USER_PASS}_at_${DATABASE}
> control=${LOAD_ROOT}/ctrl/${TABLE_NAME}.ctrl data=${DATA_DIR}/$
> {TABLE_NAME}.dat log=${LOAD_ROOT}/log/${TABLE_NAME}.log bad=$
> {LOAD_ROOT}/bad/${TABLE_NAME}.bad discard=${LOAD_ROOT}/discard/$
> {TABLE_NAME}.dsd errors=100000 rows=10000 readsize=10000000
> bindsize=10000000 \multithreading=true direct=true load=2000000 &
>
> As soon as I try this direct load command it gives me the following
> error for each record and rejects all of them:
>
> Record 5: Rejected - Error on table XXXXXXXXXXX.
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01861: literal does not match format string
>
> Let me know if any of you have faced a similar issue and can provide
> an idea on what could be the issue.
> Any help would be appreciated. Thanks!
I haven't, but see MOS Sqlloader Fails With ORA-604 And ORA-1861 When Loading Date Fields And Using Direct=Y [ID 780525.1]
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2011/sep/20/10000-city-grant-went-to-praise-fest/Received on Wed Sep 21 2011 - 11:21:22 CDT