Re: select into from a function

Actually now that I think through this I am wondering what to do. My first thought was to just copy the records I need from the views/dictionaries prior to running the procedures, but I need to access things like v$parameter. I guess I need to change the roles around abit or set them in the procedure???

It seesm like the user privileges should be the default when running a procedure.....
ciao
dn

"Douglas Nichols" <d.nichols_at_publitec.vnu.com> wrote in message news:3baf3b38$0$219$4d4ebb8e_at_news.nl.uu.net...
> 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 Tue Sep 25 2001 - 02:14:36 CDT