Re: EXECUTE IMMEDIATE statement FAILS

From: xylem <me_at_icq_at_yahoo.co.uk>
Date: Fri, 25 Apr 2008 04:03:42 -0700 (PDT)
Message-ID: <487bf5ea-8f9b-412a-ac89-113faac15bb6@a70g2000hsh.googlegroups.com>


On Apr 25, 12:18am, xylem <me_at_..._at_yahoo.co.uk> wrote:
> On Apr 24, 7:49pm, Ken Denny <k..._at_kendenny.com> wrote:
>
>
>
>
>
> > On Apr 24, 12:38pm, xylem <me_at_..._at_yahoo.co.uk> 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 PL/SQL: Statement ignored
> > > 35/50 PLS-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,
> > > objects_p varchar2,
> > > instn_p varchar2,
> > > object_p varchar2,
> > > Debug NUMBER DEFAULT 0) Is
>
> > > sql_stmt varchar2(20000);
> > > grant_revoke VARCHAR2(30);
> > > objects VARCHAR2(30);
> > > instn VARCHAR2(30);
> > > object VARCHAR2(30);
>
> > > Cursor c_gr2 Is
> > > select decode(upper('G'),'G','GRANT','R','REVOKE','***UNKNOWN
> > > OPTION***')||' '||VIEW_NAME,
> > > decode(upper('G'),'G','TO' ,'R','FROM' ,'***UNKNOWN
> > > OPTION***')||' '||
> > > USERNAME
> > > from USER_VIEWS, ALL_USERS
> > > where USERNAME like 'USER_L_%'
> > > and substr(USERNAME,-3) like translate('%','*','%')
> > > and upper('G') in ('G')
> > > and upper('A') in ('A')
> > > and VIEW_NAME like upper('%')
> > > order by 1;
> > > r_gr2 c_gr2%rowtype;
>
> > > Begin
> > > grant_revoke := UPPER(grant_revoke_p);
> > > objects := UPPER(objects_p);
> > > instn := UPPER(instn_p);
> > > object := UPPER(object_p);
>
> > > Open c_gr2;
> > > Loop
> > > Fetch c_gr2 INTO r_gr2;
> > > sql_stmt := grant_revoke||' SELECT ON '||r_gr2.VIEW_NAME||
> > > grant_revoke||r_gr2.USERNAME;
> > > IF Debug = 1 THEN
> > > DBMS_OUTPUT.Put_Line(sql_stmt);
> > > ELSE
> > > Execute Immediate sql_stmt;
> > > END IF;
> > > EXIT WHEN c_gr2%NOTFOUND;
> > > 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- 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;
> ...
> ...
> ...
>
> 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- Hide quoted text -
>
> - Show quoted text -

Hi,

I have solved the puzzle.

--
Mark
Received on Fri Apr 25 2008 - 06:03:42 CDT

Original text of this message