On Apr 24, 7:49=A0pm, Ken Denny <k...@[EMAIL PROTECTED]
> wrote:
> 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 =
varchar2,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
object_p=
varchar2,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Debug
NU=
MBER 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','***UN=
KNOWN
> > 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' =A0 ,'R','FROM' =A0,'***UNKNOWN
OPTION***')||'=
> '|| USERNAME- Hide quoted text -
>
> - Show quoted text -
Thanks Ken!
I created the aliases for the two DB columns as you suggested, thus:
select
decode(upper(grant_revoke),'G','GRANT','R','REVOKE','***UKJENT_OPSJON***')||=
'
'||VIEW_NAME as VIEW_NAME,
decode(upper(grant_revoke),'G','TO' ,'R','FROM'
,'***UKJENT_OPSJON***')||=
'
'||USERNAME as USERNAME
The procedure is compilling but something else is happening. When I
execute it. In SQL*PLUS, I get:
SQL> exec g_r('G','A','%','%',1)
R SELECT ON R
In Toad, when I execute the procedure in the Schema Browser, I get the
same result as in SQL*PLUS. When I execute it as a script (in Toad), I
get close to my desired result, i.e;
GRANT SELECT ON GRADES TO SUP_218;
GRANT SELECT ON GRADES TO SUP_219;
GRANT SELECT ON GRADES TO SUP_221;
=2E..
=2E..
=2E..
However, when I change the parameter value for grant_revoke to 'R' so
that it prints REVOKE and FROM respectively, I still get the same
result as above. Any suggestion/s to what I can do to solve thing
problem?
Thanks in advance.
Kindly,
Mark
|
