Elaborating on that, you want to use a string CONSTANT (as seen by Oracle at least). You can use SQL*Plus textual substitution and the SQL*Plus COLUMN ... NEW_VALUE command (see the SQL*Plus Reference) SQL> column timenow heading "The Time" format a25 new_value timevar SQL> select ''''||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'''' as timenow from dual; The Time ------------------------- '2010-08-03 15:14:29' SQL> prompt &timevar '2010-08-03 15:14:29' SQL> now you can put &timevar whereever you wanted a string time constant. And you can of course collect multiple variables from your select (ie, time now, time 5 minutes ago, etc. SQL*Plus substituties the string into the SQL command BEFORE the command is parsed by Oracle... HTH Regards Nigel On 3 August 2010 15:04, <lyallbarbour@xxxxxxxxxxxxxxx> wrote: > > Can you store sysdate as a varchar string earlier in the program? a little > PL/SQL block in your SQL*Plus session? > > > > -----Original Message----- > From: Taylor, Chris David <ChrisDavid.Taylor@xxxxxxxxxxxxxxx> > To: 'oracle-l@xxxxxxxxxxxxx' <oracle-l@xxxxxxxxxxxxx> > Sent: Tue, Aug 3, 2010 9:59 am > Subject: Ergh..banging head against a wall here (sqlplus...recover..until > time...sysdate) > > Ok, I’m trying to setup an automated job to recover a database within > sqlplus. Everything works fine until I try to add an “UNTIL TIME” with > SYSDATE. Is there no way to configure UNTIL TIME with SYSDATE? > > > This works: > SQL> recover database using backup controlfile until time > ‘2010-02-08:08:54:00’; > > > Here’s what I’m *trying (and need) *to do: > > /* sysdate -5 minutes */ > SQL> recover database using backup controlfile until time > to_char(sysdate,’YYYY-DD-MM:HH24:MI:SS’)-5/1440; > ORA-00285: TIME not given as a string constant > > I’ve tried: > SQL> recover database using backup controlfile until time > ‘to_char(sysdate,’YYYY-DD-MM:HH24:MI:SS’)-5/1440’; > ORA-01841: (full) year must be between -4713 and +9999, and not be 0 > > SQL> recover database using backup controlfile until time > ‘’to_char(sysdate,’YYYY-DD-MM:HH24:MI:SS’)-5/1440’’; > ORA-01840: input value not long enough for date format > > SQL> recover database using backup controlfile until time > ‘’’to_char(sysdate,’YYYY-DD-MM:HH24:MI:SS’)-5/1440’’’; > ORA-01841: (full) year must be between -4713 and +9999, and not be 0 > > So, how can I get SYSDATE -5/1440 into this “UNTIL TIME” ? > > > *Chris Taylor* > *Sr. Oracle DBA* > Ingram Barge Company > Nashville, TN 37205 > Office: 615-517-3355 > Cell: 615-663-1673 > Email: chris.taylor@xxxxxxxxxxxxxxx > > *CONFIDENTIALITY NOTICE**: This e-mail and any attachments are > confidential and may also be privileged. If you are not the named recipient, > please notify the sender immediately and delete the contents of this message > without disclosing the contents to anyone, using them for any purpose, or > storing or copying the information on any medium.* > >