PLSQL - Store a select query result in variable throw error

Question

I want to store a select query result in a variable in PLSQL.

SQL>var v_storedate VARCHAR2(19);
SQL>exec :v_storedate := 'select cdate from rprt where cdate between  cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002'';

As

SQL>select cdate from rprt where cdate between  cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002';

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

But it throws error:

 ERROR at line 1: ORA-06550: line 1, column 121: PLS-00103: Encountered
 the symbol "YYYY" when expecting one of the following:
 * & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between ||
 multiset member SUBMULTISET_ The symbol "*" was substituted for "YYYY"
 to continue. ORA-06550: line 1, column 148: PLS-00103: Encountered the
 symbol ") and ryg=" when expecting one of the following: . ( * @ % & =
 - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between

Answer 1

If you want to store the result of the query then you need to use a select ... into; at the moment you're trying to store the text of the actual query, not its result. If you wanted to do that you would need to escape the single-quote characters as the other answers have pointed out, and increase the variable size.

var v_storedate VARCHAR2(19);
exec select cdate into :v_storedate from rprt where cdate between cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002';
print v_storedate

Which would be easier to deal with using a normal anonymous block rather than SQL*Plus' execute shorthand. You should also give an explicit date format mask when converting it to a string:

begin
    select to_char(cdate, 'YYYY/MM/DD-HH24:MI:SS')
    into :v_storedate
    from rprt
    where cdate between cdate AND TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS')
    and ryg='R' and cnum='C002';
end;
/

If you want the fractional seconds then you need to make your variable bigger, as 19 chars will only take you to the seconds.

Either way though you're risking getting either multiple results (which will give ORA-02112) or no results (which will give ORA-01403). As your where clause doesn't make much sense and the table contents aren't known I don't know which is more likely. As be here now pointed out your cdate comparison is always going to be true, plus you're doing an implicit date conversion in there which will break at some point. There isn't enough information to fix that for you.

---

You can't get fractional seconds from a date value anyway, only from a timestamp; which cdate seems to be. But even then the format element for that is FF[0-9]. SSSSSS is the number of seconds since midnight. But as the whole to_char() bit looks wrong that's somewhat moot. Also, if you really do need a comparison with the current time, you should probably be comparing with systimestamp rather than sysdate to be consistent - and then not doing any conversion of that.

---

If you only want the date part:

var v_storedate VARCHAR2(10);
begin
    select to_char(cdate, 'YYYY/MM/DD')
    into :v_storedate
    ...

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

var v_storedate VARCHAR2(10);
exec select to_char(cdate, 'YYYY/MM/DD') into :v_storedate from ... where ... ;