Hello
I'm trying to use an oracle sequnce on a ORAOCI9 stage, but i get this fatal:
ORA-01036: illegal variable name/number.
Some can help me?
Ty
__SS__
DS and Oracle Sequences
Moderators: chulett, rschirm, roy
Re: DS and Oracle Sequences
What are you trying to do, what's the SQL query being executed, ...?__SS__ wrote:Hello
I'm trying to use an oracle sequnce on a ORAOCI9 stage, but i get this fatal:
ORA-01036: illegal variable name/number.
Some can help me?
Ty
__SS__
Ogmios
Define one field in your lookup OCI stage and make sure it is not marked as a key. Call the column anything and put "sequence_name.nextval" in the Derivation column. Choose "Column Generated" as the sql type, click on "Build" and for Table Name put "dual". The stage will write the query for you.
In your transform, simply link the return result from the Lookup to your field needing the Sequence. Note there is no key field to be populated as the query needs nothing in order to select the next value for you.
You could also do it with Custom SQL, but I only use that for things that truly require it. I also avoid the Fully Generated option... bleeh, more trouble than it's worth.
In your transform, simply link the return result from the Lookup to your field needing the Sequence. Note there is no key field to be populated as the query needs nothing in order to select the next value for you.
You could also do it with Custom SQL, but I only use that for things that truly require it. I also avoid the Fully Generated option... bleeh, more trouble than it's worth.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Are you trying to INSERT or SELECT?
In either case, specify sequencename.NEXTVAL (as indicated earlier)
If it's a SELECT (your ORAOCI stage is extracting rows from Oracle) you can specify it in the derivation column. If it's an INSERT, the easiest way is to write your own SQL, adapting the generated SQL. Remove the column in question from the Columns grid (so that it doesn't generate a parameter marker in the SQL), generate the INSERT statement, then adapt it by including the column name in the columnlist part, and the reference to sequencename.NEXTVAL in the VALUES clause.
In either case, specify sequencename.NEXTVAL (as indicated earlier)
If it's a SELECT (your ORAOCI stage is extracting rows from Oracle) you can specify it in the derivation column. If it's an INSERT, the easiest way is to write your own SQL, adapting the generated SQL. Remove the column in question from the Columns grid (so that it doesn't generate a parameter marker in the SQL), generate the INSERT statement, then adapt it by including the column name in the columnlist part, and the reference to sequencename.NEXTVAL in the VALUES clause.
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.
There's no specific 'Lookup' stage in Server like there is in the Mainframe/390 product. Simply use the normal OCI stage, but drag the link from the OCI stage to the Transformer (instead of the other-way-round) to create the Reference Link.
BTW, Ray posted a more thorough explaination for you - I assumed you were trying to get the Sequence via a Lookup. As he noted, you can also embed it inside your insert statement so that Oracle automagically gets and uses it for you - hence the need for custom sql. His way is better as it removes the round-trips to the server for the sequence fetch, but you'll need to do it in a lookup if you want to use it in other parts (dependant tables, for example) later in the same job stream.
And then there's the whole idea of not even using Oracle sequences at all, but that's a story for another day. :D
BTW, Ray posted a more thorough explaination for you - I assumed you were trying to get the Sequence via a Lookup. As he noted, you can also embed it inside your insert statement so that Oracle automagically gets and uses it for you - hence the need for custom sql. His way is better as it removes the round-trips to the server for the sequence fetch, but you'll need to do it in a lookup if you want to use it in other parts (dependant tables, for example) later in the same job stream.
And then there's the whole idea of not even using Oracle sequences at all, but that's a story for another day. :D
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers