Error Loading the Oracle Enterprise stage

[lakshman_gk]

I an working on Datastage PX(v-7.5). When I am trying to load the data into the Oracle Enterprise stage with Write method=Load, I am getting errors like

SQL*Loader-951: Error calling once/load initialization
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges

But its working fine when I am using the write method=Upsert.

can any one help me out with this??

[ray.wurlod]

A useful tool to know about is the oerr command, which can be used from the operating system prompt to determine the cause, and often a remedy, for a given error code.

Code: Select all

$ oerr ORA 604
00604, 00000, "error occurred at recursive SQL level %s"
// *Cause:  An error occurred while processing a recursive SQL statement
//         (a statement applying to internal dictionary tables).
// *Action: If the situation described in the next error on the stack
//         can be corrected, do so; otherwise contact Oracle Support.

$ oerr ORA 1031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.
// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.

$

Sounds like you need a chat with your Oracle DBA!

[mandyli]

Hi ,

added ray stmt please check have login privileges to Oracle using a valid Oracle user name and corresponding password. These must be recognized by Oracle before you attempt to access it.

Have SELECT privilege for following tables:
* DBA_EXTENTS
* DBA_DATA_FILES
* DBA_TAB_PARTITONS
* DBA_OBJECTS
* ALL_PART_INDEXES
* ALL_PART_TABLES
* ALL_INDEXES

We suggest that you create a role that has the appropriate SELECT privileges,
as follows: Ascential DataStage Install and Upgrade Guide
CREATE ROLE DSXE;
GRANT SELECT on sys.dba_extents to DSXE;
GRANT SELECT on sys.dba_data_files to DSXE;
GRANT SELECT on sys.dba_tab_partitions to DSXE;
GRANT SELECT on sys.dba_objects to DSXE;
GRANT SELECT on sys.all_part_indexes to DSXE;
GRANT SELECT on sys.all_part_tables to DSXE;
GRANT SELECT on sys.all_indexes to DSXE;
Once the role is created, grant it to users who will run DataStage jobs, as
follows
GRANT DSXE to <oracle userid>


please inform to your DBA and check.

Thanks
Man