Oracle 9i plug in: Problem in using 'User defined query'

yaminids · Post by **yaminids** » Mon Feb 19, 2007 4:51 pm

Hello friends,

I have a unique problem.
I have a job which transfers data from a table in db A to a table in db B.
While populating the target table the job gets the maximum of primary key and then appends 1 to it for every input record

I was trying to change the job to use an Oracle sequence instead of getting MAX key at the beginning. The problem is I am getting 'ORA-00942: table or view does not exist' after modifying User Defined SQL in the target stage (stgmaxkey.nextval)

Job layout
3 stages
1) Source db (ODBC stage)
2) transformer stage
3) Target db (ORA 9i stage)
Its straight forward move without any transformations

Am I doing something wrong
Any help would be highly appreciated
Thanks
Yamini

kumar_s · Post by **kumar_s** » Mon Feb 19, 2007 5:03 pm

"ORA-00942: table or view does not exist"
Check at which stage you are getting this error? The table that you mentioned may not be avaialble in that database. May be mis spelt, or lack or permission, or wrong schema name give by mistake...

chulett · Post by **chulett** » Mon Feb 19, 2007 5:33 pm

Where are you doing this 'use an Oracle Sequence' in your job design? Are you still planning on doing this once and then incrementing it by 1 in your job?

ray.wurlod · Post by **ray.wurlod** » Mon Feb 19, 2007 7:05 pm

Check your spelling.
Check that you have the correct schema name if you're not the table owner.
Check that you are connected to the Oracle instance that you believe the table to reside in.

yaminids · Post by **yaminids** » Mon Feb 19, 2007 11:24 pm

friends,

I am getting the error in the 'Transformer' stage. Also, I have double checked the table name and the permissions and everything looks fine. I am using the sequence in 'User Define Sql' portion so that the stage can use the sequence to insert records to the table.
Whats interesting is even after getting the error, if I click viewdata in the stage its displaying the data

Any thoughts?

Thanks a lot in advance
Yamini

SQL query in ORA 9i stage
Insert into Table123(A,B,C)
VALUES (stgkeyseq.nextval,:1,:2)

chulett · Post by **chulett** » Mon Feb 19, 2007 11:35 pm

You don't get the error 'in' the transformer stage. The transformer is the active stage, so that's why it reports the error. View Data just does a select based on the metadata, so the fact that that works doesn't mean much as it doesn't use your sql to do it.

How many columns do you have defined in your database stage? Two would be the correct answer. Also verify that your sequence 'stgkeyseq' exists as spelled and is either in the same schema as your connected user or a synonym exists for it. I'm going to assume your table information is correct for the moment since View Data works.

kumar_s · Post by **kumar_s** » Tue Feb 20, 2007 12:40 am

Check if different values are passed to parameters while at run time, if parameterized.
You might have got the view data from source, check the same sanity check in target (Insert statement).

yaminids · Post by **yaminids** » Tue Feb 20, 2007 9:57 pm

Craig,

The sequence is in another schema but a synonym has been defined. I have defined 2 columns in the stage and double checked the sequence name
I could see the insert statement in the logs (DataStage Director). The following are the statements from the logs

1) Insert into Table123(A,B,C) VALUES (stgkeyseq.nextval,:1,:2)
2) Insert into Table123(24,25)
3) Table or View does not exist

As you may notice in 2) 'Sequence' is missing from the insert statment
Am I doing anything wrong?

Yamini

chulett · Post by **chulett** » Tue Feb 20, 2007 10:20 pm

How did we now get to two sql statements?

You're pretty much on your own to figure this out. All we can do is point you in the right direction, but since we don't have access to your database...

If the sequence were the issue, it should be throwing a different error - ORA-02289 sequence does not exist to be precise. So we're back to your query and something with the table. You need to check all the 'normal' reasons why you would get that error - without specifying an owner, it either needs to be in the connected user's schema or you'll need a synonym (public or private) to get there. Plus you'll need specific select privledges on the table if it is owned by someone else. Have you verified all this?

Post your complete 'user-defined sql' from the stage. Copy and paste it - do not hand type it. I would like to see exactly what is in the stage, real names and all.

kejuste · Post by **kejuste** » Sun Feb 28, 2010 5:41 pm

Hi yaminids,

Can you re-check the Synonym on Target table and sequence. and permissions on Schema batch user.

Regards,

chulett · Post by **chulett** » Sun Feb 28, 2010 8:15 pm

Three years later?