EXECUTE IMMEDIATE statement FAILS

From: xylem <me_at_icq_at_yahoo.co.uk>
Date: Thu, 24 Apr 2008 09:38:13 -0700 (PDT)
Message-ID: <1267b6e2-1b7a-4802-b44b-b1b8be39887b@a22g2000hsc.googlegroups.com>


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;
/ Received on Thu Apr 24 2008 - 11:38:13 CDT

Original text of this message