CODEWITHSUNDEEP
sqlplsqloracle11g
Kalenda
Kalenda

Reputation: 1927

Why am i getting two different results from same query when executing it in a function vs anonymous block

I have the same logic to check if a given table exist in my database. On one hand I have put my logic inside a function, and on the other I have my logic inside an anonymous block. When I invoke the function, I get one answer, and when I run the anonymous bock, I get an other. Here is my function: 

CREATE OR REPLACE FUNCTION table_exists(in_table_name IN varchar2) RETURN int
is
   var_regex varchar2(30) := '(\w*)([^\.])';
   var_exists number(1); 
BEGIN

  SELECT  CASE WHEN EXISTS( SELECT 1 FROM all_tab_cols WHERE TABLE_NAME = UPPER( REGEXP_SUBSTR ( in_table_name, var_regex , 1, 2))) 
  THEN 1 ELSE 0 END INTO var_exists FROM dual;

  DBMS_OUTPUT.PUT_LINE('var_exists is: ' || var_exists);


  IF (var_exists = 1) 
  THEN 
    RETURN var_exists;
  ELSE
    RETURN var_exists;
  END IF; 
END;

This is how I invoke this function: 

select table_exists('test_schama.test_table') as table_exits from DUAL;

The function return zero, which mean the table doesn't exist. This is not true because 'test_schama.test_table' does exist.

My anonymous block is as follow: 

DECLARE 
    in_table_name varchar2(100) := 'test_schama.test_table';
    var_regex varchar2(30) := '(\w*)([^\.])';
    var_exists int;
BEGIN
   SELECT  CASE WHEN EXISTS( SELECT 1 FROM all_tab_cols WHERE TABLE_NAME = UPPER( REGEXP_SUBSTR ( in_table_name, var_regex , 1, 2))) 
   THEN 1 ELSE 0 END INTO var_exists FROM dual;
   DBMS_OUTPUT.PUT_LINE('var_exists is: ' || var_exists);
END;

Var_exists here has the value of 1 which mean that the table exist. Which is true. What I don't understand is why am I getting two different answer from the same exact query?

Upvotes: 0

Views: 115

Answers (1)

Justin Cave
Justin Cave

Reputation: 231761

You're querying all_tab_cols. That shows you all the tables that you have access to. That is not necessarily all the tables in the database. Inside of a definer's rights stored function (such as your table_exists), that restricts you to tables that the owner of the function has been granted access to directly not via a role. In an anonymous block, all_tab_cols will show data for any table that you have access to via a role that is enabled in the current session. Since the anonymous block works and the stored function does not, I would assume that the owner of the function does not have access granted on the table directly but that your current session does have an enabled role that has that access.

Most likely, you need to either

  • Use an invoker's rights stored function (in which case the rows in all_tab_cols would be based on the current session's privileges),
  • Use the dba_tab_cols data dictionary table instead, or
  • Grant the owner of the stored function at least select access on the table directly not via a role.

Upvotes: 4

Related Questions