I have this PL/SQL query:
DECLARE
v_table_owner VARCHAR2(30) := 'TEST_USER';
v_table_name VARCHAR2(30) := 'TEST_TABLE';
v_view_name VARCHAR2(30) := 'PARTITION_TABLE_VIEW';
BEGIN
-- Create a view to associate partitions with tables
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || v_view_name || ' AS
SELECT aip.index_owner, aip.index_name, aip.partition_name, ait.table_name
FROM all_ind_partitions aip
JOIN all_tab_partitions ait ON aip.index_owner = ait.table_owner AND aip.index_name = ait.table_name
WHERE aip.status = ''UNUSABLE''';
-- Rebuild unusable indexes for the specified table
FOR rec_index IN (SELECT
owner,
index_name
FROM
all_indexes
WHERE
table_name = ' || v_table_name || '
AND status = ''UNUSABLE'')
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';
END LOOP;
-- Rebuild unusable partitions for the specified table using the view
FOR rec_partition IN (SELECT index_owner,index_name,partition_name
FROM
' || v_view_name || '
WHERE
table_name = ' || v_table_name || ')
LOOP
DECLARE
v_sql VARCHAR2(1000);
BEGIN v_sql := 'ALTER INDEX ' || rec_partition.index_owner || '.' || rec_partition.index_name || ' REBUILD PARTITION ' || rec_partition.partition_name;
EXECUTE IMMEDIATE v_sql USING v_table_name; -- Bind the variable
EXCEPTION WHEN OTHERS THEN -- Handle exceptions, e.g., log or ignore
NULL;
END;
END LOOP;
END;
The problem is that when I run the query receive this error:
Error report -
ORA-06550: line 25, column 24:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 18, column 23:
PL/SQL: SQL Statement ignored
ORA-06550: line 36, column 9:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 31, column 27:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I think it is because of using wrong quotes, I know to escape a single quote within the string, must use two single quotes (''). I used that but it didn't work either.
Would you please guide me how to solve the problem?
Any help is really appreciated.
FOR rec_index IN (SELECT
owner,
index_name
FROM
all_indexes
WHERE
table_name = ' || v_table_name || '
AND status = ''UNUSABLE'')
Does not use a string literal for the SQL statement so ' || v_table_name || ' is a single string literal and not a concatenation and ''UNUSABLE'' is invalid syntax.
You want:
FOR rec_index IN (
SELECT owner,
index_name
FROM all_indexes
WHERE table_name = v_table_name
AND status = 'UNUSABLE'
)
Similarly:
FOR rec_partition IN (SELECT index_owner,index_name,partition_name
FROM
' || v_view_name || '
WHERE
table_name = ' || v_table_name || ')
Tries to dynamically use v_view_name and you can see from the syntax mark-up above that it is treating ' || v_view_name || ' and ' || v_table_name || ' as string literals and not concatenation, this should be:
DECLARE
v_cur SYS_REFCURSOR;
v_owner ALL_IND_PARTITIONS.INDEX_OWNER%TYPE;
v_name ALL_IND_PARTITIONS.INDEX_NAME%TYPE;
v_pname ALL_IND_PARTITIONS.PARTITION_NAME%TYPE;
BEGIN
OPEN v_cur FOR 'SELECT index_owner,
index_name,
partition_name
FROM ' || v_view_name || '
WHERE table_name = ''' || v_table_name || '''';
LOOP
FETCH v_cur INTO v_owner, v_name, v_pname;
EXIT WHEN v_cur%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX ' || v_owner || '.' || v_name || ' REBUILD PARTITION ' || v_pname;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
END LOOP;
END;
Note: You do not appear to declare a bind variable in the final EXECUTE IMMEDIATE statement so it is not clear why you think you need a USING clause.
This gives the code:
DECLARE
v_table_owner VARCHAR2(30) := 'TEST_USER';
v_table_name VARCHAR2(30) := 'TEST_TABLE';
v_view_name VARCHAR2(30) := 'PARTITION_TABLE_VIEW';
BEGIN
-- Create a view to associate partitions with tables
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || v_view_name || ' AS
SELECT aip.index_owner, aip.index_name, aip.partition_name, ait.table_name
FROM all_ind_partitions aip
JOIN all_tab_partitions ait
ON aip.index_owner = ait.table_owner
AND aip.index_name = ait.table_name
WHERE aip.status = ''UNUSABLE''';
-- Rebuild unusable indexes for the specified table
FOR rec_index IN (
SELECT owner,
index_name
FROM all_indexes
WHERE table_name = v_table_name
AND status = 'UNUSABLE'
)
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';
END LOOP;
-- Rebuild unusable partitions for the specified table using the view
DECLARE
v_cur SYS_REFCURSOR;
v_owner ALL_IND_PARTITIONS.INDEX_OWNER%TYPE;
v_name ALL_IND_PARTITIONS.INDEX_NAME%TYPE;
v_pname ALL_IND_PARTITIONS.PARTITION_NAME%TYPE;
BEGIN
OPEN v_cur FOR 'SELECT index_owner,
index_name,
partition_name
FROM ' || v_view_name || '
WHERE table_name = ''' || v_table_name || '''';
LOOP
FETCH v_cur INTO v_owner, v_name, v_pname;
EXIT WHEN v_cur%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX ' || v_owner || '.' || v_name || ' REBUILD PARTITION ' || v_pname;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
END LOOP;
END;
END;
/
Note: If any of your dynamic identifiers are in mixed- or lower-case then your query will fail. In this case, you would need to use quoted identifiers.
You can also simplify it and get rid of the dynamic view:
DECLARE
v_table_owner VARCHAR2(30) := 'TEST_USER';
v_table_name VARCHAR2(30) := 'TEST_TABLE';
BEGIN
-- Rebuild unusable indexes for the specified table
FOR rec_index IN (
SELECT owner,
index_name
FROM all_indexes
WHERE table_name = v_table_name
AND status = 'UNUSABLE'
)
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';
END LOOP;
-- Rebuild unusable partitions for the specified table using the view
FOR rec_partition IN (
SELECT aip.index_owner,
aip.index_name,
aip.partition_name
FROM all_ind_partitions aip
JOIN all_tab_partitions ait
ON aip.index_owner = ait.table_owner
AND aip.index_name = ait.table_name
WHERE aip.status = 'UNUSABLE'
AND table_name = v_table_name
)
LOOP
BEGIN
EXECUTE IMMEDIATE
'ALTER INDEX ' || rec_partition.index_owner || '.' || rec_partition.index_name
|| ' REBUILD PARTITION ' || rec_partition.partition_name;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
END LOOP;
END;
/