Re: select into from a function

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 24 Sep 2001 05:37:41 -0700
Message-ID: <9on9el0mip@drn.newsguy.com>

In article <3baecbfa$0$212$4d4ebb8e_at_news.nl.uu.net>, "Douglas says...
>
>Sorry if the is just too stupid, but can someone tell me the difference
>between these two statements? The first one, I make a function to read a
>table, then call the function and it returns nothing! The second one returns
>exactly what I think it should! So what is wrong with way 1?
>Thanks for you assist!
>--

is remote_table_names_v a view on the data dictionary? If so, you are seeing the effect (perhaps) that in a procedure roles are not enabled by default (behaviour can be changed with invokers rights in 8i and up but that changes ALOT of things about procedures).

For example:

ops$tkyte_at_ORA8I.WORLD> create or replace procedure p   2 as
  3 begin

  4          for x in ( select count(*) cnt from all_objects )
  5          loop
6                  dbms_output.put_line( 'There are ' || x.cnt || ' visible
objects...' );
  7          end loop;

  8 end;
  9 /

Procedure created.

ops$tkyte_at_ORA8I.WORLD> exec p
There are 29929 visible objects...

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> begin

  2          for x in ( select count(*) cnt from all_objects )
  3          loop
4                  dbms_output.put_line( 'There are ' || x.cnt || ' visible
objects...' );
  5          end loop;

  6 end;
  7 /
There are 37961 visible objects...

PL/SQL procedure successfully completed.

The procedure can see 29,929 objects, the anonymous block (not in a procedure) can see 37,961 things. The data dictionary restricts what you can see to what you are allowed to see.

To test if this is what is happening, simply:

SQL> set role none
SQL> declare
 r_table_name_loc varchar2(30);
 MYX VARCHAR2(30) := 'SALES_NOTES_PK';
begin
  select distinct r_table_name into r_table_name_loc from remote_table_names_v

   where r_constraint_name = MYX;
  dbms_output.put_line(r_table_name_loc); end;
/

and see what happens.

See also
http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

>/

>-- ############### Way 1
>--
>create or replace function get_remote_table_name(r_constraint_name_pass in
>varchar2)
>return varchar2 is
> r_table_name_loc remote_table_names_v.r_table_name%type;
>begin
> select distinct r_table_name into r_table_name_loc
> from remote_table_names_v
> where r_constraint_name = r_constraint_name_pass;
>-- select user into r_table_name_loc from dual;
> return r_table_name_loc;
> exception
> when NO_DATA_FOUND then
> dbms_output.put_line('No data found for '||r_constraint_name_pass);
> return '';
> when others then
> dbms_output.put_line('Error with view remote_table_names_v');
> return '';
>end;
>
>-- test for above
>set serveroutput on size 1000000
>
>declare
> remote_table varchar2(30);
>begin
> remote_table := get_remote_table_name('SALES_NOTES_PK');
> dbms_output.put_line(remote_table);
>end;
>
>/
>
>No data found for SALES_NOTES_PK
>
>PL/SQL procedure successfully completed.
>
>##################Way 2
>declare
> r_table_name_loc varchar2(30);
> MYX VARCHAR2(30) := 'SALES_NOTES_PK';
>begin
> select distinct r_table_name into r_table_name_loc from
>remote_table_names_v
> where r_constraint_name = MYX;
> dbms_output.put_line(r_table_name_loc);
>end;
>
>/
>
>SALES_NOTES
>
>PL/SQL procedure successfully completed.
>
>???
>Thanks Again for the help!
>
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Sep 24 2001 - 07:37:41 CDT