Home » SQL & PL/SQL » SQL & PL/SQL » Problem: The moment snapshot is get refreshed a message should be printed (Oracle9.1.0.2)
Problem: The moment snapshot is get refreshed a message should be printed [message #326073] Tue, 10 June 2008 02:10 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Hi,

I am a learner ( a beginner) in pl/sql.Have little knowledge in PL/SQL.

The problem is ..I using a snapshot of a table using materialized view. The moment snapshot is get refreshed it should print a message like "The data has been refreshed!".
How can I do that?

Thanks & Regards,
Oli

[Updated on: Tue, 10 June 2008 02:11]

Report message to a moderator

Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326083 is a reply to message #326073] Tue, 10 June 2008 02:35 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Can anyone please help me out with an example?
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326088 is a reply to message #326083] Tue, 10 June 2008 02:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That has to be a record - no reply in 25 minutes and you're already asking why no-ones' replying.

The ony way to do thi is to write a procedure to refresh the snapshots manually and add some code to this procedure to display the message that you want.
Then call this procedure from DBMS_JOB/DBMS_SCHEDULER to refresh the snapshot on a regular cycle.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326089 is a reply to message #326088] Tue, 10 June 2008 02:54 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Got a reply at last Smile thanks!!!
I have already created a materialized view (mv_snapshot_A) for making an snapshot of the updated table A using Fast refresh clause and setting the interval there as 20 minutes.
Everything is going fine with me.Upto here its ok.
My question is how to print a message like "The data has been refreshed" the moment materialized view is got refreshed.
Can you please guide me with an example? Need your help!
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326090 is a reply to message #326089] Tue, 10 June 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no internal to get this; how could Oracle knows where to print such a message???

Do as JRowbottom said, create your job to refresh the MV.

Regards
Michel
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326093 is a reply to message #326089] Tue, 10 June 2008 03:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Print a message where? On a printer in the basement? On every logged on users screen?

I suspect you have the requirement backwards. I think someone at some point needs to know when the view was last refreshed.

That you can see that by querying the ALL_MVIEW_REFRESH_TIMES or USER_MVIEW_REFRESH_TIMES data dictionary views.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326097 is a reply to message #326089] Tue, 10 June 2008 03:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Where is this message going to be displayed?
You can't use DBMS_OUTPUT to display it, as the session that does the refreshing is an internal oracle process.

You could create a statement level trigger on the snapshot that fires for Inserts, Updates and Deletes, but I wouldn't guarantee that the trigger would fire if there were no changes to the table.
It would fire if you were doing a full refresh, as that truncates and re-inserts, but I doubt it will fire for a Fast refresh.

If you're going to refresh the trigger on a time basis, you'd be better off following the advice from my previous post, and doing the refresh manually - that way you can add whatever 'message' code you need.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326098 is a reply to message #326093] Tue, 10 June 2008 03:28 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks to you all for your response.

@ThomasG
Quote:


I suspect you have the requirement backwards. I think someone at some point needs to know when the view was last refreshed.




Yes, you are correct. The moment the materialised view (I am using FAST REFRESH CLAUSE and Time Interval here, which have alreday created.) got refreshed I want to fire a trigger (or something like) print a message "The data has been refreshed" through web page.

@Michel
Quote:


Do as JRowbottom said, create your job to refresh the MV.




I am using the following code

CREATE MATERIALIZED VIEW mv_snapshot_A
REFRESH FAST START WITH SYSDATE 
	NEXT  SYSDATE + 20/1440
	WITH PRIMARY KEY 
	AS SELECT * FROM A;

Thanks Michel for the reply.But here do I need to create DBMS_JOB for this? I dont think so as I ahve already mentioned the interval and using FAST REFRESH Clause.Please do let me know if I am wrong here.Now Materialized view has been created.My problem is the moment it got refreshed I want to print a message in my web page like "The data has been refreshed!".
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326101 is a reply to message #326098] Tue, 10 June 2008 03:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So why don't you just put a "The view was last refreshed on Tue, 10 June 2008 10:28"-like message on the web page using the data from USER_MVIEW_REFRESH_TIMES?

That way the user knows when it was last refreshed and can even estimate when it will be refreshed next when the interval is constant.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326102 is a reply to message #326098] Tue, 10 June 2008 03:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Unless you've got some quite fancy middleware, there is no way at all that a process running on the server can display a message on some web-page running on a browser somewhere.

Would it not make more sense to simply have the web page display the LAST_REFRESH from USER/ALL_SNAPSHOTS. When the web page refreshes, it will display the most recent refresh time.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326105 is a reply to message #326097] Tue, 10 June 2008 03:54 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Tue, 10 June 2008 03:22
Where is this message going to be displayed?
You can't use DBMS_OUTPUT to display it, as the session that does the refreshing is an internal oracle process.

You could create a statement level trigger on the snapshot that fires for Inserts, Updates and Deletes, but I wouldn't guarantee that the trigger would fire if there were no changes to the table.
It would fire if you were doing a full refresh, as that truncates and re-inserts, but I doubt it will fire for a Fast refresh.

If you're going to refresh the trigger on a time basis, you'd be better off following the advice from my previous post, and doing the refresh manually - that way you can add whatever 'message' code you need.

@JRowbottom
Thanks for your resposnse! A question of concern. Whats the difference between FAST REFRESH and COMPLETE REFRESH? DOES IN COMPLETE REFRESH TRUNCATES and REINSERTS? Then ISNT THERE A POSSIBILITY OF GETTING WRONG RECORDS AT A SINGLE POINT OF TIME?
Need to know...

Ok, no problem I think I can use statement level trigger ? But why not rowlevel trigger??
How can I do that? Can you please demonstrate with an example?
I wont mind if trigger wont fired if there is no changes to master.

Thanks in adavance!

Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326106 is a reply to message #326101] Tue, 10 June 2008 03:56 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
ThomasG wrote on Tue, 10 June 2008 03:47
So why don't you just put a "The view was last refreshed on Tue, 10 June 2008 10:28"-like message on the web page using the data from USER_MVIEW_REFRESH_TIMES?

That way the user knows when it was last refreshed and can even estimate when it will be refreshed next when the interval is constant.




Can you please show me with an example code? I will be thankful to you.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326108 is a reply to message #326106] Tue, 10 June 2008 04:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Can you please show me with an example code?

Not really, since I we don't know what you use to generate your web pages.

Select the LAST_REFRESH from USER_MVIEW_REFRESH_TIMES or USER_SNAPSHOTS, format it with to_char() the way you like it, and put it on the web page.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326127 is a reply to message #326108] Tue, 10 June 2008 04:55 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
ThomasG wrote on Tue, 10 June 2008 04:05
Quote:
Can you please show me with an example code?

Not really, since I we don't know what you use to generate your web pages.

Select the LAST_REFRESH from USER_MVIEW_REFRESH_TIMES or USER_SNAPSHOTS, format it with to_char() the way you like it, and put it on the web page.


@ThomasG
Thanks for your help!

Thanks to ThomasG,JRowbottom and Michel for giving your valuable time.

I Still need to know..
Whats the difference between FAST REFRESH and COMPLETE REFRESH? DOES IN COMPLETE REFRESH TRUNCATES and REINSERTS? Then "ISNT THERE A POSSIBILITY OF GETTING WRONG RECORDS AT A SINGLE POINT OF TIME"? Thanks in advance.

Regards,
Oli



Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326129 is a reply to message #326127] Tue, 10 June 2008 05:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A refresh COMPLETE will truncate and rebuild the snapshot. The final version of the snapshot will contain all of the data in the master table at the point of rebuild - what exactly would the 'wrong records' be?

A FAST refresh would be more accurately called an INCREMENTAL refresh - it will use inserts and deletes (and possibly updates) to load the changes to the master table into the snapshot.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326134 is a reply to message #326129] Tue, 10 June 2008 05:14 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Tue, 10 June 2008 05:02
A refresh COMPLETE will truncate and rebuild the snapshot. The final version of the snapshot will contain all of the data in the master table at the point of rebuild - what exactly would the 'wrong records' be?

A FAST refresh would be more accurately called an INCREMENTAL refresh - it will use inserts and deletes (and possibly updates) to load the changes to the master table into the snapshot.





If I fetch the records from snapshot in my web page, in case of complete refresh wont I get wrong record count at a single point of time?? Did you get what I mean?

Thanks,
Oli
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326139 is a reply to message #326134] Tue, 10 June 2008 05:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't understand what you mean.

You will get the records that are currently in the snapshot. These may or may not be the same as the records currently in the master table.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326143 is a reply to message #326139] Tue, 10 June 2008 05:31 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Tue, 10 June 2008 05:22
I don't understand what you mean.

You will get the records that are currently in the snapshot. These may or may not be the same as the records currently in the master table.



Quote:

A refresh COMPLETE will truncate and rebuild the snapshot



My web page is using the snapshot. You said, "A refresh COMPLETE will truncate and rebuild the snapshot".Now my question is if I am using COMPLETE REFRESH instaed of FAST REFRESH wont the
snapshot have zero records at a certain point of time as its got truncated and rebuilded!
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326149 is a reply to message #326143] Tue, 10 June 2008 06:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I see what you mean - Yes it will.

That's not the case in 10g, where the refresh logic uses a delete/insert process, which takes longer.
Re: Problem: The moment snapshot is get refreshed a message should be printed [message #326168 is a reply to message #326149] Tue, 10 June 2008 07:15 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the reply..
The reason why I would opt for REFRESH FAST .....


Regards,
Oli
Previous Topic: Read text file with delimiter besides UTL & SQL* Loader
Next Topic: Pl/sql query -- grant
Goto Forum:
  


Current Time: Fri Dec 09 19:15:00 CST 2016

Total time taken to generate the page: 0.04639 seconds