Rem
Rem $Header: chronos_analyze_pkgdef.sql 01-jun-2005.10:06:40 eporter Exp $
Rem
Rem chronos_analyze_pkgdef.sql
Rem
Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_analyze_pkgdef.sql - package definition file for analyze feature in chronos
Rem
Rem    DESCRIPTION
Rem     package definition for analyze feature in chronos 
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    eporter     05/20/05 - Bug 4358373: SQL injection on Advanced Analyze 
Rem    eporter     02/14/05 - Change Anvanced Analyze implementation 
Rem    eporter     02/02/05 - Change Advanced Analyze parameters 
Rem    ratadiko    02/02/05 - Adding a wrapper over CHRONOS_GET_ANALYZE_DATA 
Rem    eporter     01/07/05 - Allow custom time ranges 
Rem    eporter     09/28/04 - Add advanced analyze function 
Rem    jsamal      07/29/04 - 
Rem    rpinnama    06/12/04 - Remove set ECHO 
Rem    rmarripa    09/26/03 - return dates for collection timestamp 
Rem    rmarripa    09/18/03 - fix analyze max rows 
Rem    snakhoda    09/17/03 - limit rows returned by analyze query 
Rem    snakhoda    09/17/03 - add search capability 
Rem    snakhoda    08/20/03 - snakhoda_bug-3081195_main 
Rem    snakhoda    08/08/03 - add constant for filtering
Rem    mashukla    08/07/03 - Created
Rem

CREATE OR REPLACE Package EMD_CHRONOS_ANALYZE
AS
TYPE p_cursor_type IS REF CURSOR;

TYPE p_num_list_type IS
  TABLE OF NUMBER
  INDEX BY BINARY_INTEGER;

TYPE p_date_list_type IS
  TABLE OF DATE
  INDEX BY BINARY_INTEGER;

TYPE p_vc2_1024_list_type IS
  TABLE OF VARCHAR2(1024)
  INDEX BY BINARY_INTEGER;

success_code PLS_INTEGER := 1;
failure_code PLS_INTEGER := 0;
latency_filter PLS_INTEGER := 60000;
max_rows PLS_INTEGER := 1000;

err_maxlen CONSTANT INTEGER := 2048;

TYPE MGMT_CHRONOS_ANALYZE_DATA_OBJ IS RECORD (
    key_val           p_vc2_1024_list_type,
    sample_count      p_num_list_type,
    avg_val           p_num_list_type,
    min_val           p_num_list_type,
    max_val           p_num_list_type,
    stddev_val        p_num_list_type,
    svr_avg_val       p_num_list_type,
    svr_min_val       p_num_list_type,
    svr_max_val       p_num_list_type,
    svr_stddev_val    p_num_list_type
);

PROCEDURE GET_ANALYZE_DATA_BY_URL(
              target_name_in             IN  VARCHAR2,
              target_type_in             IN  VARCHAR2,
              fixed_url_axis_in          IN  VARCHAR2,
              filter_axis_in             IN  VARCHAR2,
              max_coll_time_in           IN  DATE,
              min_coll_time_in           IN  DATE,
              filter_data_out            OUT p_cursor_type);

PROCEDURE CHRONOS_GET_ANALYZE_DATA(
              target_name_in             IN  VARCHAR2,
              target_type_in             IN  VARCHAR2,
              fixed_domain_axis_in       IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_webcache_axis_in     IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_region_axis_in       IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_url_axis_in          IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_visitor_axis_in      IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              filter_axis_in             IN  VARCHAR2,
	      filter_axis_contains       IN  VARCHAR2,
              analyze_sample             IN  VARCHAR2,
              max_coll_time_in           IN  DATE,
              min_coll_time_in           IN  DATE,
              max_rows_out               OUT INTEGER,
              max_coll_time_out          OUT DATE,
              min_coll_time_out          OUT DATE, 
              filter_data_out            OUT p_cursor_type,
              status_code                OUT INTEGER, 
              err_msg                    OUT VARCHAR2);

TYPE p_target_guid_list_type IS
  TABLE OF RAW(16)
  INDEX BY BINARY_INTEGER;

PROCEDURE CHRONOS_GET_ADV_ANALYZE_DATA(
              target_name_in             IN  VARCHAR2,
              target_type_in             IN  VARCHAR2,
              analyze_type_in            IN  VARCHAR2,
              filter_axis_in             IN  VARCHAR2,
              fixed_url_axis_in          IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_visitor_axis_in      IN  MGMT_CHRONOS_ANALYZE_ARRAY, 
              fixed_domain_axis_in       IN  MGMT_CHRONOS_ANALYZE_ARRAY, 
              fixed_region_axis_in       IN  MGMT_CHRONOS_ANALYZE_ARRAY,  
              fixed_webserver_axis_in    IN  MGMT_CHRONOS_ANALYZE_ARRAY, 
              fixed_browser_axis_in      IN  MGMT_CHRONOS_ANALYZE_ARRAY,
              fixed_os_axis_in           IN  MGMT_CHRONOS_ANALYZE_ARRAY, 
              search_str_in              IN  VARCHAR2, 
              min_coll_time_in           IN  DATE,
              max_coll_time_in           IN  DATE, 
              max_rows_out               OUT INTEGER,
              max_coll_time_out          OUT DATE,
              min_coll_time_out          OUT DATE,
              filter_data_out            OUT p_cursor_type,
              status_code                OUT INTEGER, 
              err_msg                    OUT VARCHAR2);

END EMD_CHRONOS_ANALYZE;
/
show errors;