Unable to Insert into Oracle Database

[madhusds]

Hello Everyone,

I am trying to insert a record into the oracle database by using the Oracle Enterprise Stage,

Options I am using in the stage:

Writing mode : Upsert
Upsert Mode: Userdefined update only
Update Sql: Insert Statement

It is rejecting. It is not even showing any warning while running.

Please let me know if you need more information


Thanks,
Madhu

[madhusds]

In the above mentioned issue:

The target table contains compound primary key with two columns I and C.

C is hardcoaded to 11536 and I is a Oracle sequence generator column(finapp_mig.socpartycontractrole.nextval).

can i use the above function in datastage?

I dont know how to handle this sequence in datastage for column I.

I tried by not using the I value in my design but the records are not getting insert.

I am trying to insert only one record now for testing, later i will insert more number of records

[SURA]

Few years before worked in oracle and use sequence for Batch id. I am not sure whether oracle will allow you to pass the value for that column.

You can try like, pass only the hardcoded value alone as the input and let see what is happening?

DS User

[qt_ky]

Try an insert statement similar to this syntax...

INSERT INTO table (col1, col2) VALUES (finapp_mig.socpartycontractrole.nextval, 'col2 value');

[kandyshandy]

Record is being rejected or ignored? Do you have a reject link which writes to a sequential file? If yes, that would give more information why the reecord is rejected. Make sure you enable a property within Enterprise stage to get the rejected record.

Anyways, do you have an Oracle sequence created to use?

[kandyshandy]

To understand the syntax that the stage is expecting, first select "Auto-generated update & insert" and you can see it.

You can copy the auto-generated one and modify as you need. Just add surrogate key column to the column list and add the value as Eric mentioned.

How many columns do you have in columns tab of OE stage? Nothing? Based on your explanation, there should be nothing & that makes me wonder. Surrogate key will not be defined in columns tab as you are using Oracle sequence. Another column is going to be hard coded.... So what is coming OE stage?

[madhusds]

Thanks every one for your response..

I used COALESCE function to generate the sequence..

[chulett]

Oracle's COALESCE? It doesn't generate anything, it returns the first non-NULL expression in a list of expressions.

[kandyshandy]

Oh... confused