ORA-00942: table or view does not exist

[saurabhs.dsdev]

Hello All,
I have a server job that is trying to load an Oracle table. The job statistics shows that around 2.4 million records are inserted with out the job being aborted while the Director client says that the job has aborted with the following error: ORA-00942: table or view does not exist.
When I ran a simple count query in the SQL client, the table shows only 1.8 mil records.
So, here I am kind of confused, what is the problem. As, I have created the table with all the privileges and therefore, the job should run fine. Any inputs/suggestions would be highly appreciated.

Regards.

[ArndW]

Does the error message show straight away in the Director?
What method are you using to write to the database in your Oracle stage?
Does the table have any triggers?

[MrBlack]

This does seem strange. What time of day are you running your job, could either your source or destination tables be getting dropped due to a database backup or something?

Also the designer will show green lines to a table when inserting when actually the director will log any warnings such as missing required fields or other sql errors.

Again just double check that the destination table is there and nothing else is touching the table while your job is running.

[SURA]

ORA-00942: table or view does not exist.

Check the OSH (Correct me it if am wrong) and find what is the SQL code?

Try to use reject link in the target stage and find if there is any reject data.

1. This error could happen due to permission issue too.
2. Some of the data type could also cause this error! (whenever that specific record was in, these error could pop-up)

[chulett]

OSH? This is a Server job so no such thing here. Never seen data of any type cause this error, it's always either a lack of grants or the use of the wrong connection account that causes the table to not be 'seen'. And it should be an 'all or nothing' situation - either it sees the table and loads the records or it can't and doesn't.

I'd also be curious about the timing of the error as has already been asked - when does the error occur? Immediately? Part way through the load? Details please.

[SURA]

OSH? This is a Server job so no such thing here.

I always do this mistake! I haven't remembered when i replied for this question.

I will still stand with my other points in relates with the data type. I have seen this error before for permission reason. But when i search in the oracle forums i do noticed, data type is also one of the reason.

[ray.wurlod]

The link row count shows how many rows were sent to Oracle, not how many were inserted. Oracle may have rejected some of them.

It is possible to capture rejected rows in server jobs.

Code: Select all

                 +-----------+   out    +----------+
        ----->   |Transformer|  ----->  |  Oracle  |
                 +-----------+          +----------+
                       :
                       : rejects
                       :
                       V

Link variables from the "out" link can be used to capture the error code and error text; these can be used on the reject-handling link, which must be later in execution order than the "out" link.

[chulett]

I will still stand with my other points in relates with the data type. I have seen this error before for permission reason. But when i search in the oracle forums i do noticed, data type is also one of the reason.

Interesting... can you post a link to an example?

[SURA]

Here you go.

http://stackoverflow.com/questions/6561 ... serting-in

[chulett]

Thanks. Looks to be specific to Java and how you bind CLOBs into an insert which would explain why I'd never heard of / encountered it before.

[SURA]

Sorry, the link i given to you is a small search i did after you asked. The comment what i have given is based on what i have seen couple of years ago. so both are not same.

I can't remember where i saw that detail. anyhow i will keep an eye in to that and share you the proper link when i get!