Oracle insert statement with ORACLE sequence.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 38
- Joined: Thu Jul 26, 2007 6:11 am
Oracle insert statement with ORACLE sequence.
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.
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.
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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".insert into table_dbt (A,B,C) VALUES (dt_seq.nextval,:1,:2,:3).
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Oracle insert statement with ORACLE sequence.
pimmit22043 wrote: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.
-
- Participant
- Posts: 38
- Joined: Thu Jul 26, 2007 6:11 am
ORA-01036: illegal variable name/number
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.
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.
-
- Participant
- Posts: 38
- Joined: Thu Jul 26, 2007 6:11 am
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)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
Re: ORA-01036: illegal variable name/number
Were you able to run this in SQL plus or Toad?pimmit22043 wrote: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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 38
- Joined: Thu Jul 26, 2007 6:11 am
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
Oracle_OCI_8i_12: ORA-01036: illegal variable name/number.
it is woking from command line.
thank you chulett ,Minhajuddin
-RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To work from a lookup you need a WHERE clause that mentions the Key column(s) bound to parameters.
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.
Code: Select all
SELECT whatever FROM wherever WHERE KeyCol = :1
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.
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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
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:
Will work fine as long a 'A' is the sequence column and only 'B' and 'C' are defined in the stage.
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)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers