Hi 8)
I encountered a problem with the introduction of special characters.
In my database there are indexes with special characters in the title. I want to execute a PLSQL block in which I transfer the received parameters from SELECT to dbms_stats.set_index_stac. When the query is performed, the compiler does not want to understand these names and the request ends with an error.
The name of the indexes without special characters is accepted by the compiler and the request is executed, until the name of the index with a special character is encountered, after which an error occurs.
How to correctly set the name of the index with a special characters so that the compiler begins to understand the with special characters.
set SERVEROUTPUT ON
DECLARE
BEGIN
FOR R IN (SELECT i.owner, i.index_name, t.blocks, i.clustering_factor FROM dba_tables t, dba_indexes i WHERE t.table_name = i.table_name AND t.owner=i.owner and i.owner='TEST_USER' and NOT i.index_type='LOB') LOOP
DBMS_OUTPUT.enable;
DBMS_OUTPUT.PUT_LINE('Index '||R.index_name||' is have clustering_factor '||R.clustering_factor||'. Now clustering_factor from index '||R.index_name||' update to size index block = '||R.blocks||'.');
dbms_stats.set_index_stats(ownname=>R.owner, indname=>R.index_name,clstfct => R.blocks);
END LOOP;
END;
single function execution OK ! -- Special characters in Index names :
exec dbms_stats.set_index_stats(ownname=>'TEST_USER', indname=>'"TEST_INDEX~1"',clstfct => 777);
PLSQL_BLOCK error :
set SERVEROUTPUT ON
DECLARE
BEGIN
FOR R IN (SELECT i.owner, i.index_name, t.blocks, i.clustering_factor FROM dba_tables t, dba_indexes i WHERE t.table_name = i.table_name AND t.owner=i.owner and i.owner='TEST_USER' and NOT i.index_type='LOB') LOOP
DBMS_OUTPUT.enable;
DBMS_OUTPUT.PUT_LINE('Index '||R.index_name||' is have clustering_factor '||R.clustering_factor||'. Now clustering_factor from index '||R.index_name||' update to size index block = '||R.blocks||'.');
dbms_stats.set_index_stats(ownname=>R.owner, indname=>R.index_name,clstfct => R.blocks);
END LOOP;
END;
Index TEST_INDEX1 is have clustering_factor 185. Now clustering_factor from index SYS_C0040830 update to size index block = 62.
Index TEST_INDEX2 is have clustering_factor 963. Now clustering_factor from index SYS_C0040735 update to size index block = 4.
Index TEST_INDEX3 is have clustering_factor 256. Now clustering_factor from index SYS_C0040831 update to size index block = 4.
Index TEST_INDEX4 is have clustering_factor 436. Now clustering_factor from index SYS_C0040832 update to size index block = 4.
Index TEST_INDEX5 is have clustering_factor 967. Now clustering_factor from index SYS_C0040736 update to size index block = 4.
Index TEST_INDEX6 is have clustering_factor 1152. Now clustering_factor from index SYS_C0040833 update to size index block = 4.
Index TEST_INDEX7 is have clustering_factor 362. Now clustering_factor from index SYS_C0040834 update to size index block = 4.
Index TEST_INDEX~8 is have clustering_factor 1. Now clustering_factor from index YS_GROUP~1 update to size index block = 4.
Error starting at line : 2 in command -
DECLARE
I VARCHAR2(100);
BEGIN
FOR R IN (SELECT i.owner, i.index_name, t.blocks, i.clustering_factor FROM dba_tables t, dba_indexes i WHERE t.table_name = i.table_name AND t.owner=i.owner and i.owner='TEST_USER' and NOT i.index_type='LOB') LOOP
DBMS_OUTPUT.enable;
I:= R.index_name;
DBMS_OUTPUT.PUT_LINE('Index '||R.index_name||' is have clustering_factor '||R.clustering_factor||'. Now clustering_factor from index '||R.index_name||' update to size index block = '||R.blocks||'.');
dbms_stats.set_index_stats(ownname=>R.owner, indname=>I,clstfct => R.blocks);
END LOOP;
END;
Error report -
ORA-20001: TEST_INDEX~8 is an invalid identifier
ORA-06512: na "SYS.DBMS_STATS", line 15629
ORA-06512: na "SYS.DBMS_STATS_INTERNAL", line 19475
ORA-06512: na "SYS.DBMS_STATS_INTERNAL", line 19460
ORA-06512: na "SYS.DBMS_STATS", line 3457
ORA-06512: na "SYS.DBMS_STATS", line 15490
ORA-06512: na line 8
ORA-06512: na line 8