Re: Use of statspack in a pl/sql procedure

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1091079046.326109_at_yasure>...
> sybrandb_at_yahoo.com wrote:
>
> > matthieu.atkinson_at_eu.altria.com (Matt) wrote in message news:<f683f010.0407280157.61f40dfe_at_posting.google.com>...
> >
> >>Hi all!
> >>I'm trying to run spreport within a pl/sql procedure so that instead
> >>of having a text file with my statistics, I insert those values in
> >>tables so that I can have some kind of history. Everything is fine
> >>apart from the call to statspack.stat_changes. Here is what I do:
> >>sql_query := 'CALL STATSPACK.STAT_CHANGES@'||v_linkname||'(:b1,:b2,:b3,:b4,:b5,:b6..all
> >>my bind variables.....:b86)';
> >>EXECUTE IMMEDIATE sql_query USING IN bid, eid, dbid, inst_num, para,
> >>OUT all my bind varibles....;
> >>
> >>And this the error I get:
> >>ERROR at line 1:
> >>ORA-06536: IN bind variable bound to an OUT position
> >>ORA-06512: at "CHECKSPACE.PERFSTAT_CHECK", line 193
> >>ORA-06512: at line 1
> >>
> >>I know that there are several versions of this package and I have
> >>checked that I have the correct number of parameters, but it still
> >>fails. Did anyone manage to do a similar thing and actually managed to
> >>get it to work? Any help GREATLY appreciated!!! Thanks, Matt.
> >
> >
> >
> > You are aware statspack maintains history in the perfstat schema?
> > What are you trying to accomplish? Are you sure you are not trying to
> > reinvent the wheel?
> >
>
> And if you are on 10g why use stats pack anymore?
> Take a look at: http://www.psoug.org/reference/awr.html

Just for the record AWR is a separate purchase (for use with OEM) and is not included in the base product though the packages, v$, and dba_ views are included in the installation. See your Oracle license.

HTH -- Mark D Powell -- Received on Fri Jul 30 2004 - 09:15:52 CDT