Home » Server Options » Replication » Daily refresh at particular hour/minute (Oracle 10g)
Daily refresh at particular hour/minute [message #400444] Tue, 28 April 2009 04:21 Go to next message
summoner
Messages: 44
Registered: March 2009
Member
I have a refresh group that targets to refresh on 4am each day.
However, I need to refresh the group manually sometimes. Therefore, I cannot set the interval as sysdate+1.

I have tried setting the interval as follows. However, they are not correct

Quote:
trunc(sysdate+1) +4/24

The next interval will show 9:25:20pm.

Quote:
trunc(sysdate+1) + interval '4' hour

Incorrect syntax


Thank you for your help
Re: Daily refresh at particular hour/minute [message #400455 is a reply to message #400444] Tue, 28 April 2009 04:56 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Try

interval => 'trunc(SYSDATE +1) + 4/24'


Babu
Re: Daily refresh at particular hour/minute [message #400466 is a reply to message #400455] Tue, 28 April 2009 05:22 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
gentlebabu wrote on Tue, 28 April 2009 04:56

Try

interval => 'trunc(SYSDATE +1) + 4/24'


Babu


If shows error as follow:

Quote:

Failed to commit: ORA-06550: line 5, column 20: PLS-00103: Encountered the symbol "TRUNC" 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 "TRUNC" to continue. ORA-06550: line 5, column 44: PLS-00103: Encountered the symbol "" when expecting one of the following: ) , * & | = - + < / > at in is mod remainder not rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || member S

Re: Daily refresh at particular hour/minute [message #400470 is a reply to message #400466] Tue, 28 April 2009 05:40 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
ORA-06550: line string, column string: string
Cause: Usually a PL/SQL compilation error.

Action: none


Babu
Re: Daily refresh at particular hour/minute [message #400471 is a reply to message #400470] Tue, 28 April 2009 05:40 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Post your jobs
Re: Daily refresh at particular hour/minute [message #400473 is a reply to message #400471] Tue, 28 April 2009 05:49 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Or try this

  1  declare
  2  jobno int;
  3  begin
  4   dbms_job.submit ( jobno,
  5   what => 'BEGIN tst_pro; END;',
  6   next_date => SYSDATE,
  7  interval => 'trunc(SYSDATE +1) + 4/24';
  8  commit;
  9*  end;
 10  /
interval => 'trunc(SYSDATE +1) + 4/24';
                                      *
ERROR at line 7:
ORA-06550: line 7, column 39:
PLS-00103: Encountered the symbol ";" 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 ";" to continue.

SQL>
SQL> VARIABLE JOBNO NUMBER
SQL> ED
Wrote file afiedt.buf

  1  declare
  2  jobno int;
  3  begin
  4   dbms_job.submit ( :JOBNO,
  5   what => 'BEGIN tst_pro; END;',
  6   next_date => SYSDATE,
  7  interval => 'trunc(SYSDATE +1) + 4/24');
  8  commit;
  9*  end;
SQL> /

PL/SQL procedure successfully completed.
Re: Daily refresh at particular hour/minute [message #440395 is a reply to message #400466] Sat, 23 January 2010 12:18 Go to previous messageGo to next message
jezzicaz789
Messages: 1
Registered: January 2010
Junior Member
summoner wrote on Tue, 28 April 2009 05:22
gentlebabu wrote on Tue, 28 April 2009 04:56

Try

interval => 'trunc(SYSDATE +1) + 4/24'


Babu


If shows error as follow:

Quote:

Failed to commit: ORA-06550: line 5, column 20: PLS-00103: Encountered the symbol "TRUNC" 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 "TRUNC" to continue. ORA-06550: line 5, column 44: PLS-00103: Encountered the symbol "" when expecting one of the following: ) , * & | = - + < / > at in is mod remainder not rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || member S



Thanks you for the post.
Hi guys, Im a newbie. Nice to join this forum.
Re: Daily refresh at particular hour/minute [message #440522 is a reply to message #440395] Mon, 25 January 2010 06:27 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hi welcome to the Forum.

Please read this next time.Use community Hangout forum Not the Others Thread
http://www.orafaq.com/forum/t/88153/0/

sriram Smile

[Updated on: Mon, 25 January 2010 06:29]

Report message to a moderator

Previous Topic: Complete Refresh of Refresh group
Next Topic: HOW to check the refresh rate of existing MView
Goto Forum:
  


Current Time: Fri Mar 29 02:08:38 CDT 2024