Home » SQL & PL/SQL » SQL & PL/SQL » all_objects behaving differently when called from a pl/sql function (merged 3) (Oracle 11g)
all_objects behaving differently when called from a pl/sql function (merged 3) [message #607823] Wed, 12 February 2014 06:05 Go to next message
veeraiahmannem
Messages: 7
Registered: September 2013
Location: Bangalore
Junior Member
I am getting an issue when i use the all_objects view in database function.


both the below functions are same,only the difference is the count(1) in CHECK_TABLE_NAME2
and object_name in CHECK_TABLE_NAME in the select list


function CHECK_TABLE_NAME2 is retuns value as 1 where as CHECK_TABLE_NAME is returning null value for the same table name of another schema.


CREATE OR REPLACE Function CHECK_TABLE_NAME2
   (  P_TABLE_NAME in Varchar2 )
   RETURN varchar2
IS
   V_OBJECT_NAME varchar2(30);

   cursor CUR_TABLE_NAME is
   SELECT count(1)
     FROM all_objects
     WHERE  OBJECT_NAME=P_TABLE_NAME;

BEGIN

   open CUR_TABLE_NAME;
   fetch CUR_TABLE_NAME into V_OBJECT_NAME;

   if CUR_TABLE_NAME%found then
      V_OBJECT_NAME :=1 ;
   end if;

   close CUR_TABLE_NAME;

RETURN V_OBJECT_NAME;

end;
/

CREATE OR REPLACE Function GFL_COMMON_DATA.CHECK_TABLE_NAME
   (  P_TABLE_NAME in Varchar2 )
   RETURN varchar2
IS
   V_OBJECT_NAME varchar2(30);

   cursor CUR_TABLE_NAME is
   SELECT object_name
     FROM all_objects
     WHERE  OBJECT_NAME=P_TABLE_NAME;

BEGIN

   open CUR_TABLE_NAME;
   fetch CUR_TABLE_NAME into V_OBJECT_NAME;

   if CUR_TABLE_NAME%found then
      V_OBJECT_NAME :=1 ;
   end if;

   close CUR_TABLE_NAME;

RETURN V_OBJECT_NAME;

end;
/



please suggest

Thanks,
Veeru
Re: all_objects behaving differently when called from a pl/sql function [message #607824 is a reply to message #607823] Wed, 12 February 2014 06:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
AskTom on roles and procedures
strange issue in a pl/sql function (merged 2) [message #608017 is a reply to message #607823] Fri, 14 February 2014 05:41 Go to previous messageGo to next message
veeraiahmannem
Messages: 7
Registered: September 2013
Location: Bangalore
Junior Member
i am getting a strange issue when i trying to get the object name from all_objects from the other schema.

it is giving result as expected when i give the input table from the schema where the function exists, but when i pass the table from other schema that not does not select on the schema it is not giving the result.

when i give the select access to the table to schema where function resides it is giving the result.
please help.


CREATE OR REPLACE function check_table_name1 (P_TABLE_NAME in VARCHAR2 )
RETURN VARCHAR2
IS

V_OBJECT_NAME VARCHAR2(30);
V_SQL VARCHAR2(300);

BEGIN

select OBJECT_NAME into V_OBJECT_NAME
FROM all_objects
WHERE OBJECT_NAME = P_TABLE_NAME ;


return V_OBJECT_NAME;

DBMS_OUTPUT.PUT_LINE(V_OBJECT_NAME);



END check_table_name1;
/
Re: strange issue in a pl/sql function (merged 2) [message #608022 is a reply to message #608017] Fri, 14 February 2014 06:17 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
veeraiahmannem wrote on Fri, 14 February 2014 12:41
when i give the select access to the table to schema where function resides it is giving the result.
/

I don't really understand the purpose of your function (or maybe I misunderstood your requirement) but your function works for me (Oracle 12c 12.1.0.1.0) if the other schema has the select privilege on the given table.

Here is the test that I did
SQL> connect / as sysdba
Connected.
SQL> 
SQL> show user
USER is "SYS"

SQL> 
SQL> create user testuser identified by testuser;

User created.

SQL> grant create session to testuser;

Grant succeeded.

SQL> alter user testuser quota unlimited on users;

User altered.

SQL> grant create table to testuser;

Grant succeeded.

SQL> connect testuser/testuser
Connected.
SQL> show user
USER is "TESTUSER"

SQL> create table tmptab as (select rownum rn from all_objects where rownum <= 10);

Table created.

SQL> select * from tmptab;

	RN
----------
	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	10

10 rows selected.

SQL> -- So now connecting with another schema which HAS NOT select privilege
SQL> -- on that table
SQL> connect vmtraining
Enter password:
Connected.
SQL> show user;
USER is "VMTRAINING"

SQL> create or replace function check_table_name1(p_table_name in varchar2)
  2  return varchar2
  3  is
  4    v_object_name varchar2(30);
  5  begin
  6    select
  7      t1.object_name
  8    into
  9      v_object_name
 10    from
 11      all_objects t1
 12    where
 13      t1.object_name = p_table_name;
 14  --
 15    return v_object_name;
 16  end check_table_name1;
 17  /

Function created.

SQL> show errors;
No errors.
SQL> 
SQL> select check_table_name1(p_table_name=>'TMPTAB') from "PUBLIC".dual;

CHECK_TABLE_NAME1(P_TABLE_NAME=>'TMPTAB')
--------------------------------------------------------------------------------

SQL> connect / as sysdba
Connected.
SQL> 
SQL> grant select on testuser.tmptab to vmtraining

Grant succeeded.

SQL> connect vmtraining;
Enter password:
Connected.
SQL> 
SQL> 
SQL> select check_table_name1(p_table_name=>'TMPTAB') from "PUBLIC".dual;

CHECK_TABLE_NAME1(P_TABLE_NAME=>'TMPTAB')
--------------------------------------------------------------------------------
TMPTAB

SQL> 





However there is something that I didn't understand in your code
. . .
  return V_OBJECT_NAME;

  DBMS_OUTPUT.PUT_LINE(V_OBJECT_NAME);

END check_table_name1;
/

What does a sys.dbms_output.put_line statement after a return statement suppose to mean?

[Updated on: Fri, 14 February 2014 07:49]

Report message to a moderator

Re: strange issue in a pl/sql function (merged 2) [message #608043 is a reply to message #608017] Fri, 14 February 2014 08:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
veeraiahmannem wrote on Fri, 14 February 2014 06:41
i am getting a strange issue /


By default stored objects are created with definer rights which means they run under stored object schema and ignores role based privileges. So it doesn't matter who runs your function - current user will be function owner not function caller. And ALL_OBJECTS describes all objects accessible to the current user, function will always see same list of objects - public objects, objects owned by function owner and objects function owner has privileges granted directly. So, if you want function to look at objects owned by function caller and honor role based privileges, you need to create function with invoker rights (AUTHID CURRENT_USER), not definer rights.

SY.
Re: strange issue in a pl/sql function (merged 2) [message #608056 is a reply to message #608017] Fri, 14 February 2014 09:31 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You already asked this yesterday and have a perfect answer from cookiemonster.
Why do you repost this today? Or are you too lazy to read the link we provide you?

Previous Topic: Total Time.
Next Topic: Calling PL/SQL function dynamically (with IN and OUT parameters
Goto Forum:
  


Current Time: Thu Mar 28 03:34:12 CDT 2024