Dynamic SQL Using OPEN FOR in Oracle PL/SQL

This example illustrates how you can create and use dynamic cursor in Oracle PL/SQL. The example is pretty simple, but I hope you can get the idea and apply it to more complicated cases.

DECLARE
  t_cursor IS REF CURSOR;
  my_cursor t_cursor;
  v_customer RECORD (
     customer_id NUMBER(18),
	 amount NUMBER(22, 2)
  );
BEGIN
  OPEN my_cursor FOR SELECT customer_id, 
                            amount 
                       FROM monthly_sales;
  LOOP
    FETCH my_cursor INTO v_customer;
	EXIT WHEN my_cursor%NOTFOUND;
 
	dbms_output.put_line(v_customer.amount);
  END LOOP;
END;