Home » SQL & PL/SQL » SQL & PL/SQL » Problem while using AUTHID CURRENT_USER
Problem while using AUTHID CURRENT_USER [message #660165] Fri, 10 February 2017 08:04 Go to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Hi All,

I am facing one weired issue while working with AUTHID CURRENT_USER in one of the packages. Following are the DDLs of the objects

create or replace type schema1.t_type_row as object (id number,	 name VARCHAR2(10) );
										
create or replace type t_type_tab is table of schema1.t_type_row;									 

create or replace type schema2.t_type_row as object (id number,	 name VARCHAR2(10) );
											 
create or replace type t_type_tab is table of schema2.t_type_row;

create or repalce package schema2.pqr
authid current_user
as
procedure p_get_data(p_id number, p_tab t_type_tab );
end pqr;

				 
create or replace package body 	schema2.pqr
as	
procedure p_get_data(p_id number, p_tab t_type_tab )
is
begin
	forall i in p_tab.first .. p_tab.last
	insert into test values(p_tab(i).id,p_tab(i).name);
	
end p_get_data;					 
end;

grant execute on schema2.pqr to schema1;


create or replace package schema1.pkg_data
authid current_user
as
procedure p_abc(p_id number);
end pkg_data;

create or replace package body schema1.pkg_data
as
procedure p_abc(p_id number)
is
l_type_tab t_type_tab;
begin
	begin
		select * bulk collect into l_type_tab
		from (select t_type_row(1,'john') from dual);
	end;
	
	begin
		schema2.pqr.p_get_data(101,l_type_tab);
	end;
end p_abc;
end;

create or replace synonym schema1.pkg_mdy_rds2_util for schema2.pqr;

while calling the child procedure schema2.pqr.p_get_data in schema1.pkg_data.abc it gives me error

[Error] PLS-00306 (1342: 25): PLS-00306: wrong number or types of arguments in call to 'P_GET_DATA'

but when I drop the types and objects from schema1 and provide grant on the types and objects which are belonging to schema2 then it doesnt give any error and package gets compiled successfully.

Could you please share your views on this?

Thanks,
Amit

Re: Problem while using AUTHID CURRENT_USER [message #660167 is a reply to message #660165] Fri, 10 February 2017 08:35 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's because each type is it's own datatype.
So schema1.t_type_row is a different datatype to schema2.t_type_row. Oracle doesn't care that they have the same underlying definition.
If you have a parameter defined to a type then any variable passed to it must be based on the exact same type, not an equivalent type defined somewhere else.
This problem isn't actually anything to do with authid.
Re: Problem while using AUTHID CURRENT_USER [message #660373 is a reply to message #660167] Wed, 15 February 2017 09:17 Go to previous messageGo to next message
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
Member
Thank you cookiemaster for your reply.

You mean to say types will be always referred from definers schema of the child procedure. Am I right?
Re: Problem while using AUTHID CURRENT_USER [message #660376 is a reply to message #660373] Wed, 15 February 2017 09:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unless you specify the schema of the type, yes, this is the default.
You can make your example works using:
l_type_tab schema2.t_type_tab;
but then "schema1" must have the correct privileges on "schema2.t_type_tab".

[Edit: rephrasing]


[Updated on: Wed, 15 February 2017 09:27]

Report message to a moderator

Re: Problem while using AUTHID CURRENT_USER [message #660377 is a reply to message #660373] Wed, 15 February 2017 09:26 Go to previous message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
What I said doesn't imply that at all.
You can declare the procedure with parameters typed to a type in another schema as long as you have appropriate permissions.
Fundamentally when passing types around you need to base all the instances on a single declared type. It doesn't matter which schema it's declared in as long as all schemas involved have the appropriate privs on it.

What you should never do is have two identical types created in different schemas. You'll think they're the same thing but oracle won't.
Previous Topic: natural join change table
Next Topic: Global Temporary table storage
Goto Forum:
  


Current Time: Fri Feb 23 19:16:52 CST 2018

Total time taken to generate the page: 0.01845 seconds