syntax error for execute immediate!!

[dbasupuser]

CREATE OR REPLACE PROCEDURE index_rebuilt (
table_name IN VARCHAR2)
IS
TYPE cv_type IS REF CURSOR;
cv cv_type;
val VARCHAR2(32767);
BEGIN
/* Construct the very dynamic query and open the cursor. */
OPEN cv FOR
'select index_name from dba_indexes where table_name = ' || table_name ||
'and status = "UNUSABLE" ';
LOOP
/* Fetch the next row, and stop if no more rows. */
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7';
END LOOP;
CLOSE cv;
END;
/

=======================================
procedure gets created but getting syntax error during execution for the select stmt.

The following
+++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE PROCEDURE index_rebuilt (
table_name IN VARCHAR2)
IS
TYPE cv_type IS REF CURSOR;
cv cv_type;
val VARCHAR2(32767);
BEGIN
/* Construct the very dynamic query and open the cursor. */
OPEN cv FOR
"select index_name from dba_indexes where table_name = ' || table_name ||
'and status = 'UNUSABLE' ";
LOOP
/* Fetch the next row, and stop if no more rows. */
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7';
END LOOP;
CLOSE cv;
END;
/
=============================================
Geting arning during creation of procedure.

How do I use the single coates in the select stmt like above ???

[slimdave]

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

All single quotes in the above

[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>

[pdh]

use this sql instead.

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

[rigatoni]

Using bind variables would clean this up by getting rid of all the escaping of quotes.

CREATE OR REPLACE PROCEDURE index_rebuilt (
pi_table IN VARCHAR2)
IS
TYPE cv_type IS REF CURSOR;
cv cv_type;
val varchar2(32767);
l_sql varchar2(32767);
BEGIN
/* Construct the very dynamic query and open the cursor. */
l_sql := 'select index_name from dba_indexes where table_name = :myTable ' ||
'and status = :myStatus';

OPEN cv FOR l_sql using pi_table, 'UNUSABLE';
LOOP
/* Fetch the next row, and stop if no more rows. */
FETCH cv INTO val;
EXIT WHEN cv%NOTFOUND;
EXECUTE IMMEDIATE 'ALTER INDEX MDM3.' || val || ' REBUILD ONLINE PARALLEL 7';
END LOOP;

CLOSE cv;
END;