Simple Select and Loop in PL/SQL

Simple example of using PL/SQL to select and loop through a cursor.
Note: Results are output to the output window.





CREATE OR REPLACE PROCEDURE test(p_table_name in user_tables.table_name%type) IS

TYPE ref_cursor IS REF CURSOR;
l_cursor ref_cursor;

v_query varchar2(5000);
v_name varchar2(64);

BEGIN
v_query := 'select T120F005_COST_CATEGORY_CODE from ' || p_table_name;
OPEN l_cursor FOR v_query;
LOOP
FETCH l_cursor INTO v_name;
EXIT WHEN l_cursor%NOTFOUND;
dbms_output.put_line('Category Code: ' || v_name);
END LOOP;
CLOSE l_cursor;
END;
/

-- Test.
EXEC test('T120_COSTING_CATEGORY');

0 comments: