Execute Immediate error !!

[dbasupuser]

SQL> CREATE OR REPLACE PROCEDURE index_rebuilt (
2 table_name IN VARCHAR2)
3 IS
4 TYPE cv_type IS REF CURSOR;
5 cv cv_type;
6 val VARCHAR2(32767);
7 BEGIN
8 /* Construct the very dynamic query and open the cursor. */
9 OPEN cv FOR
10 'select index_name from dba_indexes where table_name = ' || table_name ||
11 'and status = ''UNUSABLE''';
12 LOOP
13 /* Fetch the next row, and stop if no more rows. */
14 FETCH cv INTO val;
15 EXIT WHEN cv%NOTFOUND;
16 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7';
17 END LOOP;
18 CLOSE cv;
19 END;
20 /

Procedure created.

SQL>
SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "MDM3.INDEX_REBUILT", line 9
ORA-06512: at line 1


SQL>

[thomasp]

Re-write your sql stmt as

OPEN cv FOR
 'select index_name from dba_indexes where table_name = ''' || table_name ||
 ''' and status = ''UNUSABLE''';

[dbasupuser]

1 CREATE OR REPLACE PROCEDURE index_rebuilt (
2 table_name IN VARCHAR2)
3 IS
4 TYPE cv_type IS REF CURSOR;
5 cv cv_type;
6 val VARCHAR2(32767);
7 BEGIN
8 /* Construct the very dynamic query and open the cursor. */
9 OPEN cv FOR
10 'select index_name from dba_indexes where table_name = ''' || table_name ||
11 ''' and status = ''UNUSABLE''';
12 LOOP
13 /* Fetch the next row, and stop if no more rows. */
14 FETCH cv INTO val;
15 EXIT WHEN cv%NOTFOUND;
16 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7 ';
17 END LOOP;
18 CLOSE cv;
19* END;
SQL> /

Procedure created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "MDM3.INDEX_REBUILT", line 9
ORA-06512: at line 1


SQL>

[dbasupuser]

1 CREATE OR REPLACE PROCEDURE index_rebuilt (
2 table_name IN VARCHAR2)
3 IS
4 TYPE cv_type IS REF CURSOR;
5 cv cv_type;
6 val VARCHAR2(32767);
7 BEGIN
8 /* Construct the very dynamic query and open the cursor. */
9 OPEN cv FOR
10 'select index_name from dba_indexes where table_name = ''' || table_name ||
11 ''' and status = ''UNUSABLE''';
12 LOOP
13 /* Fetch the next row, and stop if no more rows. */
14 FETCH cv INTO val;
15 EXIT WHEN cv%NOTFOUND;
16 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7 ';
17 END LOOP;
18 CLOSE cv;
19* END;
SQL> /

Procedure created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "MDM3.INDEX_REBUILT", line 9
ORA-06512: at line 1


SQL>
SQL> select table_name from dba_indexes
2 where owner='MDM3'
3 and status='UNUSABLE';

TABLE_NAME
------------------------------
T_OLD_CMPN_HIST_XTRCT

SQL>

[pss]

Are you logged in as user MDM3?

[thomasp]

Any reason why you would want to use DBA_INDEXES rather than USER_INDEXES.
You will need to grant select privileges explicitly for this table if you want to use them within stored procedures.

[dbasupuser]

I am logged in as MDM3...


SQL> CREATE OR REPLACE PROCEDURE index_rebuilt (
2 table_name IN VARCHAR2)
3 IS
4 TYPE cv_type IS REF CURSOR;
5 cv cv_type;
6 val VARCHAR2(32767);
7 BEGIN
8 /* Construct the very dynamic query and open the cursor. */
9 OPEN cv FOR
10 'select index_name from dba_indexes where table_name = '''' || table_name ||
11 '''' and status = ''UNUSABLE''';
12 LOOP
13 /* Fetch the next row, and stop if no more rows. */
14 FETCH cv INTO val;
15 EXIT WHEN cv%NOTFOUND;
16 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7 ';
17 END LOOP;
18 CLOSE cv;
19 END;
20 /

Procedure created.

SQL>
SQL>
SQL>
SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "MDM3.INDEX_REBUILT", line 9
ORA-06512: at line 1


SQL>
SQL>
SQL> show user
USER is "MDM3"
SQL>

[davey23uk]

and do you have direct access to dba_tables (NOT through a role)

[pss]

Execute this:

select owner, index_name
from dba_indexes
where table_owner='MDM3'
and table_name = 'T_OLD_CMPN_HIST_XTRCT';

See if any indexes on this table are created in a different schema (other than MDM3).

[dbasupuser]

SQL> show user
USER is "MDM3"
SQL>
SQL>
SQL> select table_name from dba_indexes
2 where owner='MDM3'
3 and status='UNUSABLE';

TABLE_NAME
------------------------------
T_OLD_CMPN_HIST_XTRCT
SQL>
SQL> select owner, index_name
2 from dba_indexes
3 where table_owner='MDM3'
4 and table_name = 'T_OLD_CMPN_HIST_XTRCT';

OWNER INDEX_NAME
------------------------------ ------------------------------
MDM3 SYS_C007499

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE index_rebuilt (
2 table_name IN VARCHAR2)
3 IS
4 TYPE cv_type IS REF CURSOR;
5 cv cv_type;
6 val VARCHAR2(32767);
7 BEGIN
8 /* Construct the very dynamic query and open the cursor. */
9 OPEN cv FOR
10 'select index_name from dba_indexes where table_name = '''' || table_name ||
11 '''' and status = ''UNUSABLE''';
12 LOOP
13 /* Fetch the next row, and stop if no more rows. */
14 FETCH cv INTO val;
15 EXIT WHEN cv%NOTFOUND;
16 EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7 ';
17 END LOOP;
18 CLOSE cv;
19 END;
20 /

Procedure created.

SQL>
SQL>
SQL>
SQL> exec index_rebuilt('T_OLD_CMPN_HIST_XTRCT');
BEGIN index_rebuilt('T_OLD_CMPN_HIST_XTRCT'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "MDM3.INDEX_REBUILT", line 9
ORA-06512: at line 1


SQL>
SQL>