Re: dynamis sql UPDATE statement with join

On 19 Jun., 15:56, ciapecki <ciape..._at_gmail.com> wrote:
> > "ORA-00942: table or view does not exist ORA-06512"
>
> > Grants through role perhaps?
>
> running the update statement with the same user runs fine, other
> dynamic updates run fine, but they have no join in the where clause.
> I think the problem might be somewhere because of this join with
> gcd_dw.lb_individuals_eu_vw b
>
> chris

It seems one needs to have a direct privilege not through the role:

I found this article useful :
http://www.adp-gmbh.ch/ora/err/ora_00942.html

Creating a procedure
For most people, it comes as a surprise that the user cannot select the table from within a procedure if he has not been granted the select right directly (as opposed to through the role)

create or replace procedure get_count as   v_cnt number;
begin
  select count(*) into v_cnt from table_owner.just_a_table;   dbms_output.put_line('The count is: ' || v_cnt); end;
/

If table_user tries to compile this procedure, he gets a ORA-00942 although this table certainly exists and he was granted the right to select this table. The problem is that procedures don't respect roles; only directly granted rights are respected. So, that means that table_owner has to regrant the right to select:

connect table_owner/table_owner

grant select on just_a_table to table_user;

connect table_user/table_user;

Now, it is also possible to access the table within a procedure.

create or replace procedure get_count as   v_cnt number;
begin
  select count(*) into v_cnt from table_owner.just_a_table;   dbms_output.put_line('The count is: ' || v_cnt); end;
/

However, if the procedure is created with authid current_user, the granted roles will be enabled when the procedure is called.

chris Received on Tue Jun 19 2007 - 09:22:05 CDT