%Type declaration from another schema's table

hi experts,
i'm working on a pl/sql stored procedure that moves data around between schemas.  i am doing the development of the procedure in schema1 but interacting with data in schema2.  when i try and declare a variable as %Type of a column from a schema2 table, i get an pls-00201 "identifier x must be declared" error.  example:

v_employement               schema1.tblEmployment.Employer%Type;
v_name                           schema2.tblNames.Lastname%Type;

when compiling, oracle only complains about the second of the 2 declarations.  it gives the error: pls-00201 "identifier'Lastname' must be declared".  is there a way to declare variables using column data types from other schemas? note: i do have full rights to all tables in both schemas.

thanks,

rye
LVL 1
ryeandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I think all you need is select rights on the table from the other schema.
0
ryeandiAuthor Commented:
i have all rights on all tables in the schema.  from sql*plus i can:
SELECT LastName
FROM schema2.tblNames;

just fine.  
0
slightwv (䄆 Netminder) Commented:
What version are you on?

Prior to posting, I created a testcase on 10g w/o problem:

create user userB identified by userBpass;
grant create session to userB

conn usera/pass
create table tab1(
col1 char(1)
);

grant select on tab1 to userB;

conn userB/pass

declare
  myCol1 userA.tab1.col1%type;
begin
   null;
end;
/

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ryeandiAuthor Commented:
i'm on version 9i.  i have select rights on the table as i can log in as schema1 and
SELECT LastName
FROM schema2.tblNames;
so i obviously have select rights on the table in the other schema.
0
ryeandiAuthor Commented:
i tried running the following code:

this works:
DECLARE
  v_name                           schema2.tblNames.Lastname%Type;
BEGIN
  NULL;
END;

this doesn't:
CREATE OR REPLACE PROCEDURE myProc AS
  v_name                           schema2.tblNames.Lastname%Type;
BEGIN
  NULL;
END;

why?                                                                                                       
0
slightwv (䄆 Netminder) Commented:
9i really consists of 2 major releases.  9.0 and 9.2.  If you are on 9.0, I could believe anything can happen.  It is full of issues.

Give this a try (taking it to the minimal needed to test) and post results:

as schemaA:

declare
  v_name                           schema2.tblNames.Lastname%Type;
begin
  null;
end;
/
0
ryeandiAuthor Commented:
we're on 9.2
0
slightwv (䄆 Netminder) Commented:
OK.....  Now I think we're getting somewhere.

There's a feature of the PL/SQL engine that doesn't like some privledges (I forget the exact reasons behind this).    There are times that you must explicitly grant the permissions for use in PL/SQL.

As schemaB explicitly grant select on the table:
grant select on tblNames to schemaA;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Here's a brief description of what I was talking about (it is discussed in EE quite a bit):

excerpt from:
http://www.experts-exchange.com/Databases/Oracle/Q_20842031.html


Roles are ignored inside named PL/SQL blocks (stored procedure,
function, or trigger) that are executed with definer rights.  Prior to 8i all
named PL/SQL blocks are executed with definer rights.  When using definer
rights, roles are not used for privilege checking and roles cannot be set
within a named PL/SQL block.
0
ryeandiAuthor Commented:
that was the problem.  thanks for your help slightwv.
0
slightwv (䄆 Netminder) Commented:
thx.   Some times it takes me a while to get where we need to be.
0
ryeandiAuthor Commented:
so the authid mentioned in the above post is supposed to allow roles to work?  if this is the case, after adding the 'authid current_user', why is it that when i have a second table (addresses) in schema2 i still have to grant select on it to be able to create a variable of schema2.addresses.city%type when schema1 has full rights on this table as well?
0
slightwv (䄆 Netminder) Commented:
Not sure.  I just found the first post that talked about the privs in the PL/SQL engine.  Haven't messed with the authid piece.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.