On Apr 24, 12:38=A0pm, xylem <me_at_...@[EMAIL PROTECTED]
> wrote:
> Hi all,
>
> My procedure below is failing to provide the desired result and I've
> failed to track down what is the cause. The procedure is supposed to
> dynamically grant roll on objects to users. I've tested the cursor SQL
> and it's working fine. However, sql_stmt is failing. When I run the
> procedure I get the following error:
>
> Warning: Procedure created with compilation errors.
>
> SQL> sho err
> Errors for PROCEDURE G_R:
>
> LINE/COL ERROR
> -------- ----------------------------------------------------
> 35/3 =A0 =A0 PL/SQL: Statement ignored
> 35/50 =A0 =A0PLS-00302: component 'VIEW_NAME' must be declared
> SQL>
>
> Any help will be appreciated.
>
> Kindly,
> Mark
>
> Create or Replace Procedure g_r(grant_revoke_p varchar2,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
objects_p =
varchar2,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 instn_p
va=
rchar2,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 object_p
v=
archar2,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Debug
NUMB=
ER DEFAULT 0) Is
>
> sql_stmt varchar2(20000);
> grant_revoke =A0VARCHAR2(30);
> objects =A0 VARCHAR2(30);
> instn =A0VARCHAR2(30);
> object =A0VARCHAR2(30);
>
> Cursor c_gr2 Is
> =A0 =A0 =A0 =A0 select
decode(upper('G'),'G','GRANT','R','REVOKE','***UNKN=
OWN
> OPTION***')||' '||VIEW_NAME,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0decode(upper('G'),'G','TO' =A0
,'R','FROM' =
=A0,'***UNKNOWN
> OPTION***')||' '||
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0USERNAME
> =A0 =A0 =A0 =A0 =A0 from USER_VIEWS, ALL_USERS
> =A0 =A0 =A0 =A0 =A0where USERNAME like 'USER_L_%'
> =A0 =A0 =A0 =A0 =A0 =A0and substr(USERNAME,-3) like
translate('%','*','%')=
> =A0 =A0 =A0 =A0 =A0 =A0and upper('G') in ('G')
> =A0 =A0 =A0 =A0 =A0 =A0and upper('A') =A0 =A0 in ('A')
> =A0 =A0 =A0 =A0 =A0 =A0and VIEW_NAME like upper('%')
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0order by 1;
> r_gr2 c_gr2%rowtype;
>
> Begin
> grant_revoke :=3D UPPER(grant_revoke_p);
> objects :=3D UPPER(objects_p);
> instn :=3D UPPER(instn_p);
> object :=3D UPPER(object_p);
>
> Open c_gr2;
> =A0 Loop
> =A0 Fetch c_gr2 INTO r_gr2;
> =A0 sql_stmt :=3D grant_revoke||' SELECT ON '||r_gr2.VIEW_NAME||
> grant_revoke||r_gr2.USERNAME;
> =A0 IF Debug =3D 1 THEN
> =A0 =A0 =A0 =A0DBMS_OUTPUT.Put_Line(sql_stmt);
> =A0 ELSE
> =A0 =A0 =A0 =A0Execute Immediate sql_stmt;
> =A0 =A0END IF;
> =A0 =A0 EXIT WHEN c_gr2%NOTFOUND;
> =A0 End Loop;
> Close c_gr2;
> End;
> /
You need to alias the column names in your cursor. r_gr does not have
columns named VIEW_NAME or USERNAME. The columns are
"decode(upper('G'),'G','GRANT','R','REVOKE','***UNKNOWN OPTION***')||'
'||VIEW_NAME" and
"decode(upper('G'),'G','TO' ,'R','FROM' ,'***UNKNOWN OPTION***')||'
'|| USERNAME
|
