Home » SQL & PL/SQL » SQL & PL/SQL » array value in single quote
array value in single quote [message #574362] Wed, 09 January 2013 08:31 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

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 ;



I wrote below procedure

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;


how do I store v_strg values so that the dates are included in single quotes

begin
p_testq('12-DEC-2012');
end;

v_strg: 06-Nov-2012,09-Nov-2012


I want values to be '06-Nov-2012',''09-Nov-2012'
Re: array value in single quote [message #574368 is a reply to message #574362] Wed, 09 January 2013 09:20 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
>I want values to be '06-Nov-2012',''09-Nov-2012'
Really?

What is to the left of 09-Nov-2012?
Is it two single quote marks or one double quote mark?
Re: array value in single quote [message #574371 is a reply to message #574362] Wed, 09 January 2013 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select ''''||to_char(sysdate,'DD/MM/YYYY')||'''' res from dual;
RES
------------
'09/01/2013'

Regards
Michel
Re: array value in single quote [message #574485 is a reply to message #574362] Thu, 10 January 2013 15:31 Go to previous message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

You need to format date with to_char, eg:
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'

Some additions:
You need to avoid implicite type conversions. Also your code is dependent from nls parameters. Try to avoid this.

I changed your code a bit:
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;


Best regards,
Sayan Malakshinov
Previous Topic: Multiple Values in Column
Next Topic: Error "ORA-00904" while using subquery.
Goto Forum:
  


Current Time: Tue Oct 21 08:14:24 CDT 2014

Total time taken to generate the page: 0.13630 seconds