Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: select into from a function
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;
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;
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 CorpReceived on Mon Sep 24 2001 - 07:37:41 CDT