Re: select into from a function

Douglas Nichols wrote:
>
> 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!
> --
> -- ############### 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!

   Show source of remote_table_names_v VIEW

   Do it have column named 'r_constraint_name_pass' ?

Valery Yourinsky

-- 
Oracle8 Certified DBA
Moscow, Russia