Re: Querying stored procedures

"David Gibson" <davidg_70_at_hotmail.com> wrote in message news:pan.2004.05.20.19.56.27.161458_at_hotmail.com...
> Thanks.
>
> Sorry to be clueless, but would you use the user_export function of
> DBMS_JOB?
>
> David
>
> On Thu, 20 May 2004
> 19:07:53 +0000, Jim Kennedy wrote:
>
> >
> > "David Gibson" <davidg_70_at_hotmail.com> wrote in message
> > news:pan.2004.05.20.17.35.19.428714_at_hotmail.com...
> >> Hello,
> >>
> >> I'm tyring to verify that stored procedures haven't changed. Ideally,
I'd
> >> like to automate a query that lists stored procedures and stores them
in a
> >> file, that I can check with diff and/or a hash value. Would anyone know
of
> >> the syntax for such an animal?
> >>
> >> Also, I know I can store the syntax for a query like this, and schedule
> >> the query from cron, etc. It's been a while though, so I've forgotten
the
> >> syntax for executing a stored query without going into the sql app- it
> >> was something like:
> >>
> >> isql [database and user parameters] < query.sql
> >>
> >> Is that right?
> >>
> >> Any help is greatly appreciated. Thanks!
> >>
> >> David
> >
> > You can do it all within the database using dbms_job. Then if the OS
> > changes you are golden.
> > Jim
>

I would write a stored procedure to query the source of the stored process and maybe do a hash on each one and store the name of the stored proc, the date, and the hash value. Then I would schedule that to run automatically 1 per day to run that stored procedure. I would do that by calling dbms_job.submit(.....) . Then the job would run when you specified automatically.
You will need to look at the docs or to asktom.oracle.com and do a search for dbms_job.(for examples)
Jim Received on Thu May 20 2004 - 19:29:11 CDT