Re: Ergh..banging head against a wall here (sqlplus...recover..until time...sysdate)

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Tue, 3 Aug 2010 15:17:58 +0100
Message-ID: <AANLkTim+_87_R3-4LHvu0DypPYMokaOihTMX-uAz41pX_at_mail.gmail.com>

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_at_sanfranmail.com> 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_at_ingrambarge.com>
> To: 'oracle-l_at_freelists.org' <oracle-l_at_freelists.org>
> 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_at_ingrambarge.com
>
> *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.*
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 03 2010 - 09:17:58 CDT