"ORA-00942: table or view does exist" but it does.

iq_etl · Post by **iq_etl** » Sun Feb 02, 2014 9:03 am

I'm in the process of converting a job from 8.7 to 9.1 and getting an odd error.

In my Sequence, I've got a job that with SQL collects the initial row count of the given table. This job is bombing out with the "ORA-00942: table or view does exist" error.

However, the table does exist in the appropriate environment as I can access it in SQL Developer, _and_ in the 9.1 sever job, when I go into the Oracle Connector stage, for the job that loads the table, when I select 'view data' I see the columns and data. Also, the table definition for this table is in the correct Table Definition folder for this project.

How is the Oracle Connector stage showing the data, but the SQL statement that collects the initial row count not finding the table? Doesn't seem like it can be a permissions issue. The table name is referenced the same in both instances.

iq_etl · Post by **iq_etl** » Sun Feb 02, 2014 12:51 pm

I'm unclear why this was moved folders. We are using 9.1 Parallel Edition, but the job in question is a server job.

This issue does not pertain to Server Edition, only a server job.

ray.wurlod · Post by **ray.wurlod** » Sun Feb 02, 2014 3:55 pm

Check the user name used to connect to Oracle from your DataStage job. Maybe THIS user can't "see" the table or view.

chulett · Post by **chulett** » Sun Feb 02, 2014 8:38 pm

Because this is where Server jobs are discussed regardless of "edition". Don't take the elderly forum names too literally.

ray.wurlod · Post by **ray.wurlod** » Sun Feb 02, 2014 10:14 pm

In fact there's no such thing as "Parallel Edition" in version 9.1.

iq_etl · Post by **iq_etl** » Mon Feb 03, 2014 9:32 am

Ok, I've got all jobs in the sequence working.

In the pre-row count job, I'm now qualifying the table name with the schema. example below:

Code: Select all

SELECT COUNT(*) BeforeRowCount FROM [SCHEMA].#TABLE_NAME#

What has thrown me off is that in the initial project we built (we have about 4 projects, one for each schema), the above select statement works fine without the schema qualification. I'm going to go in and add it to be safe.

chulett · Post by **chulett** » Mon Feb 03, 2014 1:05 pm

Which means your connection user at runtime is not the schema / owner of that table.

iq_etl · Post by **iq_etl** » Mon Feb 03, 2014 2:10 pm

So, what's the best practice? As we've got it now, or having the scheduler pass in the schema qualification along with the table name to the sequence (which passes the\ose along to the DS jobs)?

chulett · Post by **chulett** » Mon Feb 03, 2014 2:35 pm

Depends on your environment and standards, I suppose. We never connect as a table owner but use a functional id that has the appropriate grants on all tables it needs to access. Which means all of our table names are 'fully qualified'. Your choice regarding the need to parameterize any of that.

Another option would be synonyms but we've chosen to not take that path.

iq_etl · Post by **iq_etl** » Mon Feb 03, 2014 4:12 pm

Can you break this down just a bit more?

Qualifying the table name with the schema, connects us as an owner? I thought those auths were in the user/server/password permissions (that, we do parameterize).

If I understand correctly, where we've got the schema name, you'd put that in a parameterized ID?

chulett · Post by **chulett** » Mon Feb 03, 2014 4:26 pm

iq_etl wrote:Qualifying the table name with the schema, connects us as an owner?

No, they are two separate things. What I meant was, if you connect as the owner of the table you technically don't need to qualify the table name. Meaning, if you have a schema of TEST with a table called FRED, you can connect as TEST and SELECT * FROM FRED and it will work since you 'own' the table. Now, if you connect as someone else like DSUSER, then you would have to SELECT * FROM TEST.FRED in order for it to know what table you meant.

If you fully qualify your table names then Oracle knows exactly where to go. If you don't, it has to try various steps in order to see if it can find it:

1. First check the current schema
2. Look for a private synonym
3. Look for a public synonym
4. Table or View does not exist

And then it also depends on having the appropriate grants, regardless of how you got there.

And we have no need to parameterize the owner as it doesn't change from environment to environment. As noted, that's more of a you call to say if it needs to be parameterized or not.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 03, 2014 7:43 pm

My practice is always to use fully-qualified table/view names, with each component in the name being a parameter (typically from a Parameter Set).

The value of using Parameter Set is that one may have a different values file (containing its own set of "default" values) for each environment (DEV, SIT, UAT, PROD).