SQL Loader invalidates indexes?

[Mr.Hanky]

We are having this problem in one environment. (HP/UX 11, Oracle 9.2.0.5.0)
Load data, indexes go invalid and need to be rebuilt.

Weird thing is that using the same scripts in another development instance causes no problems. I compared the init.ora files and there is no difference to speak of.

[Mr.Hanky]

This is the error log...


SQL*Loader-961: Error calling once/load finishing for table Hanky.your_momma
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-961: Error calling once/load finishing for table Hanky.your_momma
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-961: Error calling once/load finishing for table Hanky.your_momma
ORA-03113: end-of-file on communication channel
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-925: Error while uldlgs: OCIStmtExecute (ptc_hp)
ORA-03114: not connected to ORACLE
SQL*Loader-925: Error while uldlgs: OCIStmtFetch (ptc_hp)
ORA-24338: statement handle not executed

[gandolf989]

Did you check to see what ora-01001 is?

ORA-01001 invalid cursor

Cause: Either a host language program call specified an invalid cursor or the value of the MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA) must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF.

Action: Check the erroneous call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may be necessary to increase the MAXOPENCURSORS option value before precompiling.

Do you have any insert triggers on your_mamma?

[Mr.Hanky]

Actually I found the fix on Metastink.

The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.5
This problem can occur on any platform.

Errors
ORA-24338 statement handle not executed
ORA 3113 "end-of-file on communication channel"
SQL*LOADER-2026
SQL*LOADER-961

Symptoms
Error during direct path load with SQLLOADER
Specific to 9.2.0.5

SQL*Loader-961: Error calling once/load finishing for table TEST_OFFERTE
ORA-3113: end-of-file on communication channel
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-925: Error while uldlgs: OCIStmtExecute (ptc_hp)
ORA-3114: not connected to ORACLE
SQL*Loader-925: Error while uldlgs: OCIStmtFetch (ptc_hp)
ORA-24338: statement handle not executed
Changes
After upgrading to 9.2.0.5
Cause
Reference Bug:3555220 , Bug:3531336

After upgrading to 9.2.0.5,
Direct path load fails with SQL Loader

Fix
Run the script catcio.sql from $ORACLE_HOME/rdbms/admin directory.
Run this in sys schema.
And then load the data with sqlloader.
References
- Psrc: Sqlldr Direct Path Fails With Ora-00600[Kssrmp1] Or Hangs If Idx On Table

[gandolf989]

I'm glad to hear that you are once again able to insert into your_mama!!!

[slimdave]

Originally posted by gandolf989
I'm glad to hear that you are once again able to insert into your_mama!!!

Nothing conventional about that insert.