Rem Copyright (c) 2000, 2011, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem dbmsxidx.sql - DBMS XMLIndex index support routines
Rem
Rem DESCRIPTION
Rem Defines the XMLIndex index creation routines using the extensibility
Rem mechanism
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem sipatel 05/02/11 - Backport sipatel_xmlindex_proc_replication from
Rem main
Rem - #(11067798)-change supplemental logging pragma
Rem sipatel 04/28/11 - Backport sipatel_bug-12360609 from main
Rem - #(12360609)-add pending_row_count out param for
Rem process_pending
Rem sipatel 01/04/11 - #(11686104)-add process_pending
Rem thbaby 06/02/10 - revoke 'with grant option' from public
Rem attran 03/08/10 - 9398943: default NULL schema for SyncIndex
Rem NumberIndex, DateIndex
Rem badeoti 03/19/09 - cleanup for 11.2 packages: remove noderef-related
Rem procs/funcs NodeRefGetRef, NodeRefGetValue, NodeRefGetParentRef,
Rem NodeRefGetName, NodeRefGetNamespace
Rem ajadams 11/07/08 - add with_commit to supplemental_log pragma
Rem bhammers 08/25/08 - add getparameter
Rem thbaby 06/24/08 - revoke grant privilege on stragg
Rem hxzhang 04/15/08 - add dropParameter
Rem hxzhang 11/14/07 - Index Unification Project
Rem atabar 11/09/07 - add reindex parameter to SyncIndex
Rem attran 10/01/07 - Partitioning + SyncIndex
Rem thbaby 10/24/07 - add column name to
Rem createnumberindex/createdateindex
Rem thbaby 06/21/07 - documentation for SyncIndex
Rem preilly 04/23/07 - Fix bug 6003399 - gather_table_stats in logical
Rem standby
Rem thbaby 02/15/07 - implement stragg as an internal aggregate
Rem thbaby 02/14/07 - return aggregated length in stragg terminate
Rem thbaby 02/08/07 - add stragg user defined operator
Rem thbaby 01/30/07 - remove NodeRefGetPosPath, NodeRefGetNamePath
Rem attran 01/16/07 - bug-5736555: export_clob
Rem qiwang 12/14/06 - add Logmnr PLSQL pragam for dbms_xmlindex
Rem thbaby 11/28/06 - new CreateNumberIndex that accepts xmltype name
Rem thbaby 11/02/06 - move dbms_xmlindex package body out
Rem thbaby 08/14/06 - rename *_xml_indexes column paths to parameters
Rem thbaby 07/27/06 - add dbms_xmlindex.NodeRefGetRef
Rem attran 08/01/06 - add gather_table/delete_stats
Rem ataracha 06/08/06 - add export support
Rem rmurthy 04/28/05 - add dbms_xnid for node id operations
Rem attran 01/04/06 - ALTER SESSION privilege -> C routines
Rem attran 02/04/05 - bug4148624: SQLInjection
Rem sichandr 11/22/04 - remove set echo statements
Rem sichandr 08/11/04 - utility package for XMLIndex
Rem mkrishna 09/06/01 - remove existsnode/extract
Rem mkrishna 06/29/00 - Created
Rem
/*-----------------------------------------------------------------------*/
/* LIBRARY */
/*-----------------------------------------------------------------------*/
create or replace library XDB.XMLIndex_lib trusted as static;
/
show errors;
CREATE OR REPLACE PACKAGE xdb.dbms_xmlindex AUTHID CURRENT_USER AS
----------------------------------------------------------------------------
-- PROCEDURE - CreateNumberIndex
-- Creates an index for number values in the XMLIndex. The index
-- is created on the VALUE column of the XMLIndex path table on the
-- expression TO_BINARY_DOUBLE(VALUE).
-- PARAMETERS -
-- xml_index_schema
-- Schema of the XMLIndex: default is current user schema
-- xml_index_name
-- Name of the XMLIndex
-- num_index_name: default is system-generated
-- Name of the number index to create
-- num_index_clause
-- Storage clause for the number index. This would simply be appended
-- to the CREATE INDEX statement.
-- xmltypename
-- Xml type name corresponding to the number - one of the following:
-- float
-- double
-- decimal
-- integer
-- nonPositiveInteger
-- negativeInteger
-- long
-- int
-- short
-- byte
-- nonNegativeInteger
-- unsignedLong
-- unsignedInt
-- unsignedShort
-- unsignedByte
-- positiveInteger
-- column_name
-- Name of the path table column on which to create the number index.
----------------------------------------------------------------------------
PROCEDURE CreateNumberIndex(xml_index_schema IN VARCHAR2 := USER,
xml_index_name IN VARCHAR2,
num_index_name IN VARCHAR2 := NULL,
num_index_clause IN VARCHAR2 := NULL,
xmltypename IN VARCHAR2 := NULL,
column_name IN VARCHAR2 := NULL);
PRAGMA SUPPLEMENTAL_LOG_DATA(CreateNumberIndex, AUTO_WITH_COMMIT);
----------------------------------------------------------------------------
-- PROCEDURE - CreateDateIndex
-- Creates an index for date values in the XMLIndex. The user specifies
-- the XML type name (date, dateTime etc.) and the index is created
-- on SYS_XMLCONV(VALUE) which would always return a TIMESTAMP datatype.
-- PARAMETERS -
-- xml_index_schema
-- Schema of the XMLIndex: default is current user schema
-- xml_index_name
-- Name of the XMLIndex
-- date_index_name: default is system generated
-- Name of the date index to be created
-- xmltypename
-- XML type name - one of the following
-- dateTime
-- time
-- date
-- gDay
-- gMonth
-- gYear
-- gYearMonth
-- gMonthDay
-- date_index_clause
-- Storage clause for the date index. This would simply be appended
-- to the CREATE INDEX statement.
-- column_name
-- Name of the path table column on which to create the date index.
----------------------------------------------------------------------------
PROCEDURE CreateDateIndex(xml_index_schema IN VARCHAR2 := USER,
xml_index_name IN VARCHAR2,
date_index_name IN VARCHAR2 := NULL,
xmltypename IN VARCHAR2 := NULL,
date_index_clause IN VARCHAR2 := NULL,
column_name IN VARCHAR2 := NULL);
PRAGMA SUPPLEMENTAL_LOG_DATA(CreateDateIndex, AUTO_WITH_COMMIT);
----------------------------------------------------------------------------
-- PROCEDURE SyncIndex(xml_index_schema IN VARCHAR2,
-- xml_index_name IN VARCHAR2,
-- partition_name IN VARCHAR2,
-- reindex IN BOOLEAN);
-- This procedure synchronizes an asynchronously maintained xmlindex.
-- It applies to the xmlindex changes that are logged in the pending
-- table, and brings the path table up-to-date with the base xmltype
-- column.
--
-- PARAMETERS
-- (a) xml_index_schema - Name of the owner of the XMLIndex.
-- (b) xml_index_name - Name of the XMLIndex.
-- (c) partition_name - Optional name of the partition to be synced.
-- (d) reindex - If true drops and recreates secondary indexes
-- on path table. Default is false.
----------------------------------------------------------------------------
PROCEDURE SyncIndex(xml_index_schema IN VARCHAR2 default USER,
xml_index_name IN VARCHAR2,
partition_name IN VARCHAR2 default NULL,
reindex IN BOOLEAN default FALSE);
PRAGMA SUPPLEMENTAL_LOG_DATA(SyncIndex, AUTO_WITH_COMMIT);
PROCEDURE gather_table_stats(ownname IN VARCHAR2,
tabname IN VARCHAR2,
partname IN VARCHAR2 default NULL,
estimate_percent IN NUMBER default 0,
block_sample IN NUMBER default 0,
granularity IN VARCHAR2 default 'AUTO');
PRAGMA SUPPLEMENTAL_LOG_DATA(gather_table_stats, MANUAL);
PROCEDURE delete_table_stats(ownname IN VARCHAR2,
tabname IN VARCHAR2,
partname IN VARCHAR2 default NULL,
cascade_parts IN NUMBER default 1);
PRAGMA SUPPLEMENTAL_LOG_DATA(delete_table_stats, UNSUPPORTED_WITH_COMMIT);
PROCEDURE registerparameter(paramname IN VARCHAR2,
paramstr IN CLOB);
PRAGMA SUPPLEMENTAL_LOG_DATA(registerparameter, UNSUPPORTED_WITH_COMMIT);
PROCEDURE modifyparameter(paramname IN VARCHAR2,
paramstr IN CLOB);
PRAGMA SUPPLEMENTAL_LOG_DATA(modifyparameter, UNSUPPORTED_WITH_COMMIT);
PROCEDURE dropparameter(paramname IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(dropparameter, UNSUPPORTED_WITH_COMMIT);
FUNCTION getparameter(paramname IN VARCHAR2) RETURN VARCHAR2;
----------------------------------------------------------------------------
-- PROCEDURE PROCESS_PENDING(xml_index_schema IN VARCHAR2,
-- xml_index_name IN VARCHAR2,
-- pending_row_count OUT BINARY_INTEGER);
-- This procedure executes DMLs required to complete a NONBLOCKING
-- alter index add_group/add_column operation.
--
-- PARAMETERS
-- (a) xml_index_schema - Name of the owner of the XMLIndex.
-- (b) xml_index_name - Name of the XMLIndex.
-- (c) pending_row_count - RETURNs number of rows that still have
-- to be processed/indexed.
----------------------------------------------------------------------------
PROCEDURE process_pending(xml_index_schema IN VARCHAR2,
xml_index_name IN VARCHAR2,
pending_row_count OUT BINARY_INTEGER);
PRAGMA SUPPLEMENTAL_LOG_DATA(process_pending, AUTO_WITH_COMMIT);
end dbms_xmlindex;
/
show errors;
grant execute on xdb.dbms_xmlindex to public;
create or replace public synonym dbms_xmlindex for xdb.dbms_xmlindex;
create or replace type string_agg_type
-- OID '00000000000000000000000000020101'
as object
(
key raw(8),
static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type, outopn IN RAW,
inpopn IN RAW)
return pls_integer
as language c
library xmltype_lib
name "STRAGG_INITIALIZE"
with context
parameters (
context,
sctx, sctx INDICATOR STRUCT, sctx DURATION OCIDuration,
outopn OCIRaw, inpopn OCIRaw,
return int
),
member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return pls_integer
as language c
library xmltype_lib
name "STRAGG_ITERATE"
with context
parameters (
context,
self, self INDICATOR STRUCT, self DURATION OCIDuration,
value, value INDICATOR, value LENGTH,
return int
),
member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return pls_integer
as language c
library xmltype_lib
name "STRAGG_TERMINATE"
with context
parameters (
context,
self, self INDICATOR STRUCT,
returnValue, returnValue INDICATOR, returnValue LENGTH,
flags, flags INDICATOR,
return int
),
member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return pls_integer
as language c
library xmltype_lib
name "STRAGG_MERGE"
with context
parameters (
context,
self, self INDICATOR STRUCT, self DURATION OCIDuration,
ctx2, ctx2 INDICATOR STRUCT,
return int
)
);
/
show errors;
/
/* stragg cannot be parallel-enabled unless order by is supported in
* parallel mode for user defined aggregates
*/
CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
AGGREGATE USING string_agg_type;
/
GRANT EXECUTE on stragg to PUBLIC;
REVOKE EXECUTE on stragg from PUBLIC;
GRANT EXECUTE on stragg to PUBLIC;