Re: Use of statspack in a pl/sql procedure

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.

Matt, I do not know if this is your problem but you show 6 input bind variables when you build your string but your using clause only has 5 variables.

But if you have the space to save the reports why not just keep the original statistics so you could produce any report of interest when you want it?

HTH -- Mark D Powell -- Received on Wed Jul 28 2004 - 08:42:18 CDT