Example
- Declare the cursor to scan a list of records
- Open it
- Fetch current record into variables (this increments position)
- Use
%notfoundto detect end of list - Don't forget to close the cursor to limit resources consumption in current context
--
DECLARE
CURSOR curCols IS -- select column name and type from a given table
SELECT column_name, data_type FROM all_tab_columns where table_name='MY_TABLE';
v_tab_column all_tab_columns.column_name%TYPE;
v_data_type all_tab_columns.data_type%TYPE;
v_ INTEGER := 1;
BEGIN
OPEN curCols;
LOOP
FETCH curCols INTO v_tab_column, v_data_type;
IF curCols%notfound OR v_ > 2000 THEN
EXIT;
END IF;
dbms_output.put_line(v_||':Column '||v_tab_column||' is of '|| v_data_type||' Type.');
v_:= v_ + 1;
END LOOP;
-- Close in any case
IF curCols%ISOPEN THEN
CLOSE curCols;
END IF;
END;
/
