Re: select into from a function

From: Douglas Nichols <d.nichols_at_publitec.vnu.com>
Date: Tue, 25 Sep 2001 13:32:50 +0200
Message-ID: <3bb0a0c5$0$220$4d4ebb8e@news.nl.uu.net>

I do not think this is the problem after all. Since this example doesn't work either:
LOGIN AS SYSTEM
SELECT TABLE_NAME FROM USER_TABLES;
....., HELP..... SQL> declare

  2    table_p all_tab_columns.table_name%type :='HELP';
  3    owner_p all_tab_columns.owner%type :='SYSTEM';
  4    column_name_l user_tab_columns.column_name%type;
  5    cursor column_list_cur is
  6      select column_name from all_tab_columns
  7       where table_name = table_p and owner = owner_p;

  8 begin
  9 open column_list_cur;
 10 loop <<column_list_loop>>

 11      fetch column_list_cur into column_name_l;
 12      exit when column_list_cur%notfound or column_list_cur%notfound is

null;

 13 dbms_output.put_line(column_name_l);  14 end loop column_list_loop;
 15 end;
 16
 17 /
TOPIC
SEQ
INFO PL/SQL procedure successfully completed.

-----------------------Now try this
SQL> create or replace procedure get_column_list(
  2         owner_p varchar2,
  3         table_p varchar2

  4 ) is
  5 column_name_l user_tab_columns.column_name%type;   6 cursor column_list_cur is

  7      select column_name from dba_tab_columns
  8       where table_name = table_p and owner = owner_p;

  9 begin
 10 open column_list_cur;
 11 loop <<column_list_loop>>

 12      fetch column_list_cur into column_name_l;
 13      exit when column_list_cur%notfound or column_list_cur%notfound is

null;

 14 dbms_output.put_line(column_name_l);  15 end loop column_list_loop;
 16 end;
 17
 18 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE GET_COLUMN_LIST:

LINE/COL ERROR

-------- -----------------------------------------------------------------
7/5      PL/SQL: SQL Statement ignored
7/12     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

7/29     PLS-00201: identifier 'SYS.DBA_TAB_COLUMNS' must be declared
12/5     PL/SQL: SQL Statement ignored

So is this the same error as we are talking about?

thanks
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 - 06:32:50 CDT