Wednesday, September 17, 2008

Cursor with bind variable in PL/SQL

DECLARE
TYPE TabCurTyp IS REF CURSOR;
ind_cursor TabCurTyp;
TABLE_NAME VARCHAR2(30);
INDEX_NAME VARCHAR2(30);
sql_stmt VARCHAR2(200);
BEGIN
TABLE_NAME:=UPPER('&TABLE_NAME');
sql_stmt := 'SELECT index_name FROM USER_INDEXES WHERE INDEX_TYPE <> ''LOB'' AND TABLE_NAME = :j';
DBMS_OUTPUT.PUT_LINE('ANALYZE TABLE '||TABLE_NAME||' COMPUTE STATISTICS;');
OPEN ind_cursor FOR sql_stmt USING table_name;
LOOP
FETCH ind_cursor INTO index_name;
EXIT WHEN ind_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ALTER INDEX '||index_name||' REBUILD;');
end loop;
END;
/

No comments:

search engine

Custom Search