PL/SQL procedures

[dasniper]

I want to make Oracle execute the DBMS_REFRESH package, without having to expose my users to SQL*Plus.

Is there a way I can either package the DBMS_REFRESH command into a stored procedure, or some other packaging, like an executable? Ideally, I'd like to make it so users can just click on a button on a webpage and have the Oracle refresh it's snapshots.

 

[karluk]

I'm not a programmer, so maybe I'm missing something. However I don't see why you have to put dbms_refresh into a procedure. Why not simply execute dbms_refresh.refresh directly when your users click the web page?

 

[dasniper]

I wouldn't know how to do that.

How do you get to essentially the Oracle commandline from a webserver? Perl procedure maybe?

I did see somewhere on Oracle's site something about making a PSP page, but that seems to require Oracle WebDB gateway, which I'm sure 1)we don't have and 2)we don't want.

 

[sem]

You may use servlets if having some servlet engine or Perl, but in this case your users have to log in every time they need to execute a new command (CGI :-().

 

[MikeLacey]

Perl procedure is quite doable.

Use PPM (you are on Winxx aren't you?) to download the DBD::Oracle package, what you want to do will be quite quick to implement, there are some good examples in the documentation.

Before you do that though, on UNIX there is a utility that refreshes your snapshots as soon as they need it. isn't there one on Winxx? Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[dasniper]

Both the Master and slave Oracle databases are on Solaris.

What's the utility that updates a snapshot as soon as it's needed. I wouldn't need that functionality, because I don't want to propogate changes immediately (because we're propogating changes from a QA environment to Production), but something that would be easy for the users would be great.