create table test_g(x date);
 
insert into test_g values (to_date('01-NOV-2001','DD-MON-YYYY'));
 
insert into test_g values (to_date('02-NOV-2011','DD-MON-YYYY'));
 
insert into test_g values (to_date('03-DEC-2012','DD-MON-YYYY'));
 
insert into test_g values (to_date('12-DEC-2012','DD-MON-YYYY'));
 
insert into test_g values (to_date('31-DEC-2012','DD-MON-YYYY'));
 
insert into test_g values (to_date('03-NOV-2012','DD-MON-YYYY'));
 
insert into test_g values (to_date('06-NOV-2012','DD-MON-YYYY'));
 
insert into test_g values (to_date('09-NOV-2012','DD-MON-YYYY'));
 
 
COMMIT ;

create or replace procedure p_testq(p_in_date in date)
is

v_comp date;
v_strg varchar2(200);

i number:=1;

type t_trc is ref cursor;
trc t_trc;

v_sql varchar2(2000);

-- record to which data goes into
type t_prec is record(x date);
prec t_prec;


-- plsql table to store data
type t_frec is table of t_prec index by binary_integer;
frec t_frec;

-- flow of data, is from v_sql --> plsql record --> plsql table

begin

 dbms_output.put_line(' month of paramter  '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
     
  select min(x)
  into v_comp
from test_g
where x <= P_IN_DATE
AND TO_CHAR(x,'MON') = to_char(P_IN_DATE,'MON');
  
  dbms_output.put_line('v_comp: '||v_comp);

 
  v_sql :=   ' select x
     from (select x,
                   row_number() over (order by x desc) rn
             from test_g
             where x <= to_date('''||p_in_date||''',''dd-mon-yyyy'')
               AND TO_CHAR(x,''MON'') = to_char(add_months(to_date('''||p_in_date||''',''dd-mon-yyyy''),-1),''MON''))
     where rn < 3
   order by x';
  
  dbms_output.put_line(v_sql);
  open trc for v_sql; -- SQL STMT TO ref cursor
  
   LOOP
                    FETCH trc  INTO prec ; -- ref cursor to plsql record
                exit when trc%notfound;
     dbms_output.put_line('i :'||i);
     frec(i).x := prec.x; -- plsql record to plsql table
     dbms_output.put_line('frec(i).x: '||frec(i).x);
     if i =1 then
      v_strg := frec(i).x;
      else
      v_strg := v_strg ||','||frec(i).x;
     end if;
     i:= i+1;
  END LOOP;
  
   dbms_output.put_line(frec.count);
   
   dbms_output.put_line('v_strg: '||v_strg);
   
  
  
 
end;

SQL> select ''''||to_char(sysdate,'DD/MM/YYYY')||'''' res from dual;
RES
------------
'09/01/2013'

SQL> select to_char(sysdate,q'['DD-MON-YYYY']') dt from dual;

DT
----------------------
'11-JAN-2013'

SQL> begin 
  2    dbms_output.put_line(
  3       to_char(
  4           date'2013-01-01'
  5         , q'['DD-MON-YYYY']' 
  6       )
  7    ); 
  8  end;
  9  /

'01-JAN-2013'

create or replace procedure p_testq(p_in_date in date)
is
  v_comp date;
  v_strg varchar2(200);

  i number:=1;

  type t_trc is ref cursor;
  trc t_trc;

  v_sql varchar2(2000);

  -- record to which data goes into
  type t_prec is record(x date);
  prec t_prec;


  -- plsql table to store data
  type t_frec is table of t_prec index by binary_integer;
  frec t_frec;

  -- flow of data, is from v_sql --> plsql record --> plsql table

begin

  dbms_output.put_line(' month of paramter  '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
     
  select min(x)
    into v_comp
  from test_g
  where x <= P_IN_DATE
  AND trunc(x,'MM') = trunc(P_IN_DATE,'MM'); -- 1. "trunc" instead of "to_char(...,'MON')"
  
  dbms_output.put_line('v_comp: '||v_comp);

  -- with bind variables:
  v_sql :=   q'[
   select x
     from (select x,
                  row_number() over (order by x desc) rn
           from test_g
           where x <= :p_in_date
             AND trunc(x,'MM') = trunc(add_months(:p_in_date,-1),'MM')
          )
     where rn < 3
   order by x
   ]';
  
  dbms_output.put_line(v_sql);
  open trc for v_sql using p_in_date,p_in_date; -- SQL STMT TO ref cursor
  
  LOOP
    --- maybe would be better to use bulk fetch there:
    FETCH trc  INTO prec ; -- ref cursor to plsql record
    exit when trc%notfound;
    dbms_output.put_line('i :'||i);
    frec(i).x := prec.x; -- plsql record to plsql table
    dbms_output.put_line( 'frec(i).x: ' || to_char( frec(i).x, q'['DD-MON-YYYY']' ));
    if i = 1 then
      v_strg := to_char(frec(i).x, q'['DD-MON-YYYY']');
    else
      v_strg := v_strg ||','||to_char(frec(i).x, q'['DD-MON-YYYY']');
    end if;
    i:= i+1;
  END LOOP;
  
  dbms_output.put_line(frec.count);
  dbms_output.put_line('v_strg: '||v_strg);
   
end;