sql - PLSQL - Store a select query result in variable throw error -

i want store select query result in variable in plsql.

sql>var v_storedate varchar2(19); sql>exec :v_storedate := 'select cdate rprt cdate between  cdate , to_char(sysdate, 'yyyy/mm/dd-hh24-mi-ss-sssss') , ryg='r' , cnum='c002''; 

as

sql>select cdate rprt cdate between  cdate , to_char(sysdate, 'yyyy/mm/dd-hh24-mi-ss-sssss') , ryg='r' , cnum='c002'; 

returns : 2013/04/27-10:06:26:794

but throws error:

 error @ line 1: ora-06550: line 1, column 121: pls-00103: encountered  symbol "yyyy" when expecting 1 of following:  * & = - + ; < / > @ in mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> , or like2_ like4_ likec_ between ||  multiset member submultiset_ symbol "*" substituted "yyyy"  continue. ora-06550: line 1, column 148: pls-00103: encountered  symbol ") , ryg=" when expecting 1 of following: . ( * @ % & =  - + ; < / > @ in mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> , or like2_ like4_ likec_ between 

if want store result of query need use select ... into; @ moment you're trying store text of actual query, not result. if wanted need escape single-quote characters other answers have pointed out, , increase variable size.

var v_storedate varchar2(19); exec select cdate :v_storedate rprt cdate between cdate , to_char(sysdate, 'yyyy/mm/dd-hh24-mi-ss-sssss') , ryg='r' , cnum='c002'; print v_storedate 

which easier deal using normal anonymous block rather sql*plus' execute shorthand. should give explicit date format mask when converting string:

begin     select to_char(cdate, 'yyyy/mm/dd-hh24:mi:ss')     :v_storedate     rprt     cdate between cdate , to_char(sysdate, 'yyyy/mm/dd-hh24-mi-ss-sssss')     , ryg='r' , cnum='c002'; end; / 

if want fractional seconds need make variable bigger, 19 chars take seconds.

either way though you're risking getting either multiple results (which give ora-02112) or no results (which give ora-01403). where clause doesn't make sense , table contents aren't known don't know more likely. here pointed out cdate comparison going true, plus you're doing implicit date conversion in there break @ point. there isn't enough information fix you.

---

if want date part:

var v_storedate varchar2(10); begin     select to_char(cdate, 'yyyy/mm/dd')     :v_storedate     ... 

you can still use exec if want to, it's less readable once statement gets longer terminal line length:

var v_storedate varchar2(10); exec select to_char(cdate, 'yyyy/mm/dd') :v_storedate ... ... ;