BULK COLLECT with Custom SQL Statement
- Rumesh Aponso (RMAX)

- Nov 21, 2024
- 1 min read
DECLARE
rec_limit_ NUMBER := 100;
fetch_limit_ CONSTANT PLS_INTEGER DEFAULT 9;
sql_stmt_ CLOB;
CURSOR get_co_info IS
SELECT t.order_no,
t.contract,
t.customer_no,
t.currency_code
FROM customer_order_tab t;
TYPE customer_order_list_tab IS TABLE OF get_co_info%ROWTYPE INDEX BY BINARY_INTEGER;
customer_order_list customer_order_list_tab;
PROCEDURE Print_Info (
rec_ IN get_co_info%ROWTYPE )
IS
BEGIN
Dbms_Output.Put_Line(rec_.order_no || ' - ' ||
rec_.contract || ' - ' ||
rec_.customer_no || ' - ' ||
rec_.currency_code);
END Print_Info;
BEGIN
sql_stmt_ := 'SELECT t.order_no, ' ||
' t.contract, ' ||
' t.customer_no, ' ||
' t.currency_code ' ||
'FROM customer_order_tab t ' ||
'WHERE ROWNUM <= 10';
EXECUTE IMMEDIATE sql_stmt_ BULK COLLECT INTO customer_order_list;
FOR i_ IN 1 .. customer_order_list.COUNT LOOP
Print_Info(customer_order_list(i_));
END LOOP;
END;



Comments