Re: select into from a function

Well, that sure seems to be the case- I see everything with the declare statement, but nothing in my function. My view includes access on the all_cons_columns.

So what is the preferred method for doing something like this? I guess I might be tempted to just change the roles, but there must be a secure way to perform this function.

Thanks a lot for you time!

dn

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9on9el0mip_at_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 - 08:55:21 CDT