Re: dynamis sql UPDATE statement with join

On 19 jun, 16:22, ciapecki <ciape..._at_gmail.com> wrote:
> 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

You are welcome ;-)

Cheers.

Carlos. Received on Tue Jun 19 2007 - 09:38:38 CDT