?
Solved

%Type declaration from another schema's table

Posted on 2005-03-21
13
Medium Priority
?
1,760 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:ryeandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13595371
I think all you need is select rights on the table from the other schema.
0
 
LVL 1

Author Comment

by:ryeandi
ID: 13595504
i have all rights on all tables in the schema.  from sql*plus i can:
SELECT LastName
FROM schema2.tblNames;

just fine.  
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13595592
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:ryeandi
ID: 13595682
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
 
LVL 1

Author Comment

by:ryeandi
ID: 13595797
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13595804
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
 
LVL 1

Author Comment

by:ryeandi
ID: 13595856
we're on 9.2
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 total points
ID: 13595866
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13595889
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
 
LVL 1

Author Comment

by:ryeandi
ID: 13595991
that was the problem.  thanks for your help slightwv.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13596006
thx.   Some times it takes me a while to get where we need to be.
0
 
LVL 1

Author Comment

by:ryeandi
ID: 13596144
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13596165
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question