Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Use of statspack in a pl/sql procedure

Re: Use of statspack in a pl/sql procedure

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 01 Aug 2004 22:42:21 -0700
Message-ID: <1091425375.401421@yasure>


Mark D Powell wrote:

> 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 --

You are correct if one purchases a vanilla EE license. For me it is free and included in the base license as it is in many large enterprises.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Mon Aug 02 2004 - 00:42:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US