cannot insert data in oracle table

abyss · Post by **abyss** » Wed Aug 20, 2014 7:11 pm

hi
i am a newbie for DS.
i am running a simple practice parallel job on data stage data is read from CFF then it go to remove duplicate stage and finally insert into the oracle table.
when i run the job, all stages can run successfully except the last stage, i can't insert data into oracle table. the insert statement i used is really simple:
INSERT INTO customer_dim (CUSTOMER_ID) VALUES (ORCHESTRATE.CUSTOMER_ID) but ds says the sql statement failed The OCI function OCIStmtExecute returned status -1. Error code: 24,381

any idea?
thanks

chulett · Post by **chulett** » Wed Aug 20, 2014 9:13 pm

That error doesn't tell us much. As noted in another post with the same errors you'll need to provide us with details on exactly what you are doing in the target stage before anyone can really help much.

abhilashnair · Post by **abhilashnair** » Thu Aug 21, 2014 4:16 am

You may need to check if any Primary/Unique Key constraint violations are happening

abyss · Post by **abyss** » Sun Aug 24, 2014 10:21 pm

craig, is it possible to upload image on this form?

chulett · Post by **chulett** » Mon Aug 25, 2014 12:08 am

Sure. You can upload an image to any of the free File Sharing sites out there (I typically use Imgur but there are plenty of others) and then link to them in a post. That's what the "Img" or Image tags are for in a php bulletin board like this.

abyss · Post by **abyss** » Mon Aug 25, 2014 12:41 am

hi i pasted some screen shot, hopefully it helps. it is really simple job:
1. the whole job is get data from CFF then data go to transform stage and then go to remove duplicate stage, eventually insert into oracle table.

this is the column mapping at remove duplicate stage, as you can see all the input columns map to the output

for the insert statement, i only insert the customer_in into the table, and i am using datastage syntax

from director, it shows the error message

the error message shows below:
orc_customer,1: The input schema column NAME is not used and the connector is configured to stop when unused columns are detected in the input schema. (CC_OraRecordDataSetConsumer::setDataSetDef, file CC_OraRecordDataSetConsumer.cpp, line 3,939)

this is customer dimension design, nothing to special
create table CUSTOMER_DIM
(
customer_dim_key INTEGER not null,
customer_id INTEGER,
name VARCHAR2(50),
home_phone VARCHAR2(12),
work_phone VARCHAR2(12),
work_address VARCHAR2(50),
work_city VARCHAR2(50),
work_state VARCHAR2(50),
work_zip VARCHAR2(15),
work_country VARCHAR2(50),
home_address VARCHAR2(50),
home_city VARCHAR2(50),
home_zip VARCHAR2(15),
home_state VARCHAR2(50),
home_country VARCHAR2(50),
membership_id INTEGER,
membership_expire_dt DATE,
membership_level VARCHAR2(1),
current_ind VARCHAR2(1) default 'Y',
effective_ts TIMESTAMP(6) default CURRENT_TIMESTAMP,
expiration_ts TIMESTAMP(6) default to_timestamp ('2999-12-31 23:23:59.000','yyyy-mm-dd hh24:mi:ss.ff')
)

ray.wurlod · Post by **ray.wurlod** » Mon Aug 25, 2014 2:03 am

Yes, read the message again carefully, then look at the INSERT statement (which fails to name the NAME column, and that's probably just the first column that it fails to name). You have configured the Connector to abort when this situation occurs.

Can you please look on the Outputs (Columns) tab of the remove_customer stage to determine whether or not Runtime Column Propagation (RCP) is enabled there?

abyss · Post by **abyss** » Mon Aug 25, 2014 10:27 pm

hi
I have checked the remove duplicate stage, the RCP is enabled as what it should be.

I think you right ray, I update the insert, change the statement to:
INSERT INTO customer_dim (CUSTOMER_ID,NAME) VALUES(ORCHESTRATE.CUSTOMER_ID,ORCHESTRATE.NAME) then the error message change to "The input schema column HOME_PHONE is not used " HOME_PHONE is the next column after NAME column, so it just keep going.

If i put all the columns in (auto generate sql statement) then the error message is:
orc_customer,0: The following SQL statement failed: INSERT INTO customer_dim (CUSTOMER_ID,NAME,MEMBERSHIP_ID,MEMBERSHIP_EXPIRE_DT,MEMBERSHIP_LEVEL,WORK_PHONE,HOME_PHONE,WORK_ADDRESS,WORK_CITY,WORK_STATE,WORK_ZIP,WORK_COUNTRY,HOME_ADDRESS,HOME_CITY,HOME_STATE,HOME_ZIP,HOME_COUNTRY) VALUES(:CUSTOMER_ID,:NAME,:MEMBERSHIP_ID,:MEMBERSHIP_EXPIRE_DT,:MEMBERSHIP_LEVEL,:WORK_PHONE,:HOME_PHONE,:WORK_ADDRESS,:WORK_CITY,:WORK_STATE,:WORK_ZIP,:WORK_COUNTRY,:HOME_ADDRESS,:HOME_CITY,:HOME_STATE,:HOME_ZIP,:HOME_COUNTRY).
orc_customer,1: The OCI function OCIStmtExecute returned status -1. Error code: 24,381, Error message: ORA-24381: error(s) in array DML. (CC_OraStatement::executeInsert, file CC_OraStatement.cpp, line 1,925)

if i set option 'fail on row option' to no, then job can be executed but no row can be insert into table.

abyss · Post by **abyss** » Mon Aug 25, 2014 11:08 pm

I enabled/disabled RCP, dropped necessary output columns and set Drop Unmatched fields to yea and no. none of them works.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 25, 2014 11:21 pm

Try removing all non-required columns from the output link of the Remove Duplicates stage and reverting to your original SQL statement.

abyss · Post by **abyss** » Tue Aug 26, 2014 6:55 pm

i did, this is on the remove duplicate stage:

abyss · Post by **abyss** » Tue Aug 26, 2014 6:56 pm

and the result is exactly the same.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 26, 2014 7:23 pm

and you STILL don't have NAME in your INSERT statement.

abyss · Post by **abyss** » Tue Aug 26, 2014 8:12 pm

yes, i do :s
database is down now, i will look at it later, thanks for your reply

abyss · Post by **abyss** » Tue Aug 26, 2014 11:37 pm

hi ray
I fixed problem, i dropped all the columns except customer_id and name under the column tab from BOTH INPUT and OUTPUT link, also disable the Runtime Column Propagation as it automatically populate all the columns in remove_customer stage. then the job just worked.
i still can't run the job with all the columns, i think there are some syntax error at transformer stage
thanks
abyss