Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

%Type declaration from another schema's table

Posted on 2005-03-21
13
Medium Priority
?
1,874 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
  • 7
  • 6
13 Comments
 
LVL 78

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 78

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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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 78

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 78

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 78

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 78

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 78

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

580 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