These schema's make me want to screama!

July 3, 2006 9:32 AM

Calling all SQL / DBA / code monkey brainiac's! Please help me understand what is happening in this mess ....

Here is the issue I am having....

Currently, I have developed a package in PL/SQL. This package resides under both schema A and schema B. The package creates temporary tables under whichever schema it is being called. The only difference between the two copies of the packages is that each one looks at the ALL_TAB_COLS table to identify the temporary tables that have been created under the schema from which the package was ran. Additionally, this package queries tables from schema B, no matter which schema it is being called from.

The issue that I am having is that this package runs fine from schema A, but I am encountering issues when trying to run it from schema B. The error I am getting is a "table or view" does not exist error. This occurs when I try to run a create table statement (with a SELECT) using EXECUTE IMMEDIATE. All of the tables in the create table statement exist and user B (owner of schema B) has read permissions on them. I am confused as to why this issue is occuring from schema B when schema B owns all of the tables in the create table statement, yet there are no issues when running from schema A, which only has read access to the tables in the create table statement.

posted by AllesKlar to computers & internet (8 comments total)

brainaic's schema's; brainaics schemas ... cursed gratuitous apostrophes.
posted by AllesKlar at 9:35 AM on July 3, 2006

sounds like the user needs some GRANTs given to it to access one or more tables in your SELECT statement. Are those tables owned by user A?
posted by lowlife at 9:50 AM on July 3, 2006

Who owns the PL/SQL package? PL/SQL packages can be either 'definer's right' or 'owner's right'. You could be tripping over that.
posted by of strange foe at 10:56 AM on July 3, 2006

Bleh, I meant 'definer's right' vs. 'invoker's right' (with AUTHID CURRENT_USER clause). But please ignore - it's probably not the cause of your problem anyway.
posted by of strange foe at 11:08 AM on July 3, 2006

The tables in the SELECT statement are owned by schema B (the schema I'm having trouble running the package from). I take the SELECT statement that is trying to be executed by the package and I run it directly from an SQL editor. The query runs fine there. Could there be something with the EXECUTE IMMEDIATE statement?

The DBA at the client site has created the package for me in this test environment (under schema B). Do DBA's usually set those up as 'definer's rights'?

Thanks for all of the responses!
posted by AllesKlar at 12:23 PM on July 3, 2006

The immediate thought is to run the CREATE TABLE AS SELECT statement from an SQL editor as B and see what happens. (You said you only ran the query.)
PL/SQL packages are created as 'definer's right' by default, I believe, but I'll stop pretending to be a DBA now.
posted by of strange foe at 1:17 PM on July 3, 2006

ok DBA here...reading your post. Why on earth are you creating temporary tables, rather than using Oracle's global temporary tables?
posted by neilkod at 2:06 PM on July 3, 2006

By default PL/SQL is defined with definers(authors) rights. You could just have one copy of the package and give it invokers rights. That makes maintaining your code a lot easier.

If you'd like, email me your code (check my profile) and I'll be happy to get you up and running.
posted by neilkod at 2:35 PM on July 3, 2006