"ORA-00942: table or view does exist" but it does.
Moderators: chulett, rschirm, roy
"ORA-00942: table or view does exist" but it does.
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
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.
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#
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.
Another option would be synonyms but we've chosen to not take that path.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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?
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?
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.iq_etl wrote:Qualifying the table name with the schema, connects us as an owner?
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
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).
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.