Oracle insert statement with ORACLE sequence.

[pimmit22043]

Hi

I have 3 columns that i'm trying to insert into table.

here is the insert

insert into table_dbt (A,B,C) VALUES (dt_seq.nextval,:1,:2,:3).

is this statemt is right ?

my job is like below.
seq stage ===trsfrmer===> orcl stage.

anybody came across can you help us.

[Minhajuddin]

insert into table_dbt (A,B,C) VALUES (dt_seq.nextval,:1,:2,:3).

It's not right, And it has got nothing to do with Datastage. You are trying to insert three fields with *four* values. So, anytime you want to put a query in Datastage, you need to first test it in "SQL Plus".

[chulett]

It would help to state the actual problem you are having or errors you are getting. That should work 'fine' as long as there are only three columns defined in the stage and you fix the problem Minhajuddin noted.

[ray.wurlod]

Your job must not deliver the key column to the OCI stage. Then you only have the two non-key columns, so you require precisely two parameter markers in your INSERT statement.

Code: Select all

insert into table_dbt (A,B,C) VALUES (dt_seq.nextval,:1,:2)

[paddu]

Hi

I have 3 columns that i'm trying to insert into table.

here is the insert

insert into table_dbt (A,B,C) VALUES (dt_seq.nextval,:1,:2,:3).

is this statemt is right ?

my job is like below.
seq stage ===trsfrmer===> orcl stage.

anybody came across can you help us.

if you want datastage to pass only A,B,C columns but while runtime oracle to generate a sequence number in the table then you can acheive this by creating a trigger on your target table to insert the sequence number. You need to have 4 columns in your target table one for the sequence number .



Not sure if this helps you.

[pimmit22043]

i have changed the insert stament blow

insert into DT_TBL(B,C) VALUES (DT_SEQ.NEXTVAL,:1,:2)

here is the error i'm getting.ORA-01036: illegal variable name/number
above sequnce is the key column in the table so i don't include in the columns list.

[chulett]

How many columns are defined in your stage? I'm guessing the answer isn't "two".

[pimmit22043]

i have 2 columns from TRFM and oracle 3 columns.

[chulett]

Ah... missed the fact that you only have two column names but three values in your sql again:

Code: Select all

insert into DT_TBL(A,B,C) VALUES (DT_SEQ.NEXTVAL,:1,:2)

[Minhajuddin]

i have changed the insert stament blow

insert into DT_TBL(B,C) VALUES (DT_SEQ.NEXTVAL,:1,:2)

here is the error i'm getting.ORA-01036: illegal variable name/number
above sequnce is the key column in the table so i don't include in the columns list.

Were you able to run this in SQL plus or Toad?

[pimmit22043]

as chulett said i have tried with that it did not work eather i have create simple oracle lookup reading like select a as key ,dt_keq.nextval from dual , from trfer 'a' as key join on key column .it is also giving same error message as.

Oracle_OCI_8i_12: ORA-01036: illegal variable name/number.

it is woking from command line.

thank you chulett ,Minhajuddin

[ray.wurlod]

To work from a lookup you need a WHERE clause that mentions the Key column(s) bound to parameters.

Code: Select all

SELECT whatever FROM wherever WHERE KeyCol = :1

You could have reference to a sequence in the SELECT clause I suppose, though I struggle to think of a good reason when you can add one to a variable in the Transformer stage or even just use an auto-incrementing system variable such as @INROWNUM or @OUTROWNUM.

[paddu]

pimmit22043,

Do you really need to use custom insert?

As Ray suggested you could acheive sequence numbers using a @OUTROWNUM or you can even use Keymanagement functions.

OR Else if this scenario is only to this job and you really need to use oracle sequence DT_SEQ only then why not create a trigger on the Target table DT_TBL.

here goes the trigger

Create trigger DT_trigger
before insert for each row begin
select DT_SEQ.NEXTVAL into:new.A from dual ;
end; (Where A is one of the columns in the table)


create this trigger through TOAD or SqlPlus .

Your ETL job just maps column B and column C to the target oracle table with action ( insert rows only ..) .Your oracle sequence is taken care by the trigger which at run time popluates the sequence numbers into column A.

[Minhajuddin]

Can you post all these details.

Which Database stage?
Columns and datatypes in the Database *stage*:
Columns and datatypes in the actual database table:
Your generated/user-defined query when you are trying to insert:

This should be a very simple problem once all these details are given.

[chulett]

This isn't difficult and really shouldn't have generated this many replies.

Sure, a trigger is possible but I've seen way too many places that are 'trigger shy' for lack of a better term. Sure, you can use a lookup to select the nextval from dual so it just becomes another data value in the job, but it's the least efficient methodology. And yes, you do need to use custom sql if you want to embed the sequence usage into the insert statement itself, as the OP does.

As noted earlier, you mention all of the target columns in the last stage except for the one that will hold the sequence value. Then what I do is let the stage generate the sql, switch to user-fined and modify it slightly to include the sequence column. So, again:

Code: Select all

insert into DT_TBL(A,B,C) VALUES (DT_SEQ.NEXTVAL,:1,:2)

Will work fine as long a 'A' is the sequence column and only 'B' and 'C' are defined in the stage.