-
syntax error for execute immediate!!
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 ???
-
Code:
OPEN cv FOR
'select index_name from dba_indexes where table_name = ''' || table_name ||
'''and status = ''UNUSABLE''';
LOOP
All single quotes in the above
-
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>
-
use this sql instead.
OPEN cv FOR
'select index_name from dba_indexes where table_name = ''' || table_name ||
''' and status = ''UNUSABLE''';
-
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;
____________________
Pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|