EXECUTE IMMEDIATE statement FAILS

#11

On Apr 24, 7:49*pm, Ken Denny <k... (AT) kendenny (DOT) com> wrote:

Quote:

On Apr 24, 12:38*pm, xylem <me_at_... (AT) yahoo (DOT) 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_pvarchar2,
* * * * * * * * * * * * * * * * 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','***UNK NOWN
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','***UN KNOWN 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

#12

On Apr 24, 7:49*pm, Ken Denny <k... (AT) kendenny (DOT) com> wrote:

Quote:

On Apr 24, 12:38*pm, xylem <me_at_... (AT) yahoo (DOT) 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_pvarchar2,
* * * * * * * * * * * * * * * * 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','***UNK NOWN
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','***UN KNOWN 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

#13

On Apr 24, 7:49*pm, Ken Denny <k... (AT) kendenny (DOT) com> wrote:

Quote:

On Apr 24, 12:38*pm, xylem <me_at_... (AT) yahoo (DOT) 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_pvarchar2,
* * * * * * * * * * * * * * * * 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','***UNK NOWN
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','***UN KNOWN 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

#14

On Apr 25, 12:18*am, xylem <me_at_... (AT) yahoo (DOT) co.uk> wrote:

Quote:

On Apr 24, 7:49*pm, Ken Denny <k... (AT) kendenny (DOT) com> wrote:

On Apr 24, 12:38*pm, xylem <me_at_... (AT) yahoo (DOT) 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','***UNK NOWN
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','***UN KNOWN 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

#15

On Apr 25, 12:18*am, xylem <me_at_... (AT) yahoo (DOT) co.uk> wrote:

Quote:

On Apr 24, 7:49*pm, Ken Denny <k... (AT) kendenny (DOT) com> wrote:

On Apr 24, 12:38*pm, xylem <me_at_... (AT) yahoo (DOT) 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','***UNK NOWN
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','***UN KNOWN 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

#16

On Apr 25, 12:18*am, xylem <me_at_... (AT) yahoo (DOT) co.uk> wrote:

Quote:

On Apr 24, 7:49*pm, Ken Denny <k... (AT) kendenny (DOT) com> wrote:

On Apr 24, 12:38*pm, xylem <me_at_... (AT) yahoo (DOT) 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','***UNK NOWN
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','***UN KNOWN 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

#17

On Apr 25, 12:18*am, xylem <me_at_... (AT) yahoo (DOT) co.uk> wrote:

Quote:

On Apr 24, 7:49*pm, Ken Denny <k... (AT) kendenny (DOT) com> wrote:

On Apr 24, 12:38*pm, xylem <me_at_... (AT) yahoo (DOT) 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','***UNK NOWN
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','***UN KNOWN 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

EXECUTE IMMEDIATE statement FAILS

comp.databases.oracle.misc comp.databases.oracle.misc

Re: EXECUTE IMMEDIATE statement FAILS - 04-24-2008 , 05:18 PM

Re: EXECUTE IMMEDIATE statement FAILS - 04-24-2008 , 05:18 PM

Re: EXECUTE IMMEDIATE statement FAILS - 04-24-2008 , 05:18 PM

Re: EXECUTE IMMEDIATE statement FAILS - 04-25-2008 , 06:03 AM

Re: EXECUTE IMMEDIATE statement FAILS - 04-25-2008 , 06:03 AM

Re: EXECUTE IMMEDIATE statement FAILS - 04-25-2008 , 06:03 AM

Re: EXECUTE IMMEDIATE statement FAILS - 04-25-2008 , 06:03 AM