Re: What wrong in the function(suggest me alternative also if any)

vishal_lavti_at_yahoo.com (Vishal Lavti) wrote in message news:<bfd4391d.0109202202.22113bb5_at_posting.google.com>...
> Hey please check out whats wrong in this function?
> what iwant is to check whetehr a table exists or not.I'm connected as
> user EXPLAIN goven DBA role.I want to fine that a table exitse in user
> ECALS or not???
> It is working when i'm connected as ECALS.but when i connect as
> EXPLAIN it does not work.
> help me out.
>
>
>
> create or replace function check_for_table(t_name varchar2) return
> integer is
> tname varchar2(20);
> n integer;
> cursor c is select table_name from sys.all_tables where
> table_name=t_name and owner='ECALS';
> begin
> n:=0;
> dbms_output.put_line(t_name);
> open c;
> loop
> dbms_output.put_line('hi1');
> fetch c into tname;
> exit when c%notfound;
> n:=n+1;
> dbms_output.put_line(tname);
> end loop;
> dbms_output.put_line('hi');
> return(n);
> exception
> when others then
> return(0);
> end;

Roles are ignored during compilation of pl/sql and as user explain you have privilege through a role not directly. You have 3 solutions:
 1 as you have hardcoded the table owner you could as well create a function ecals.check_table and grant execute on this function to 'explain' (which I wouldn't use as username, because it is a reserved word)
2 in 7.3 and 8.0 : grant privilege on all_tables to explain directly 3 in 8i : create the function with invokers rights.

Apart from that I have 2 other remarks.
1 Could you please always include the version you are using. I don't think anyone responding keeps track of the versions being used 2 This question has been asked very often, and I don't think a search for it at groups.google.com will turn up a blank.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Fri Sep 21 2001 - 12:08:52 CEST