Rem Rem $Header: sdo/admin/sdosemutlh.sql /st_sdo_11.2.0/6 2011/04/29 12:19:52 sdas Exp $ Rem Rem sdosemutlh.sql Rem Rem Copyright (c) 2006, 2011, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdosemutlh.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem matperry 03/09/11 - add number list to in list method Rem matperry 02/15/11 - add isSafeFilter Rem matperry 01/25/11 - bug 11661028: remove unnecessary privileges on Rem ordmd_sem_libs and remove sem_hash_internal Rem alwu 12/16/10 - add local named graph inference Rem sdas 11/22/10 - XbranchMerge sdas_ng_11.2.0.2.0_11.2.0.3.0 from Rem st_sdo_11.2.0 Rem sdas 11/22/10 - XbranchMerge sdas_ng_11.2.0.2.0 from Rem matperry 10/13/10 - add constants for default namespaces and prefixes Rem vkolovsk 09/14/10 - change get_value_type sig Rem alwu 08/11/10 - update with 11202 validation transaction Rem vkolovsk 07/29/10 - XbranchMerge Rem vkolovsk_rdf_ols_set_triple_label_11.2.0.2.0 from Rem st_sdo_11.2.0 Rem vkolovsk 07/28/10 - XbranchMerge vkolovsk_bug-9724396 from Rem st_sdo_11.2.0 Rem vkolovsk 07/13/10 - add get_value_type util wrapper Rem alwu 07/23/10 - add logic to get parallel dml dop Rem vkolovsk 05/27/10 - remove set_sub_hint Rem alwu 05/10/10 - XbranchMerge alwu_bug-9694148 from main Rem alwu 05/10/10 - add validation rules for OWL2 RL Rem vkolovsk 05/05/10 - add run_dml_stmt function Rem vkolovsk 11/11/09 - add parallel param to run_dml_stmt Rem matperry 07/13/09 - add helper function for xsd:time values Rem vkolovsk 06/09/09 - safe rowcount util, in cases when it returns a Rem negative number Rem alwu 06/01/09 - add an option to allow columnar compression for Rem merge_models Rem alwu 05/14/09 - add NegativePropertyAssertion checking based on Rem the recent W3C OWL 2 profile change Rem alwu 05/02/09 - add wrap_num_col2raw Rem alwu 03/27/09 - add more IDs for SKOS integrity conditions Rem matperry 02/18/09 - add numberListContains Rem vkolovsk 02/18/09 - simplify run_dml_stmt Rem alwu 02/12/09 - change merge_models_create_tmp_tab to function to Rem make sqlinjection test happy Rem vkolovsk 02/11/09 - simplifying merge_models_update_linktab param list Rem alwu 02/10/09 - factor out mapping, get_uri functions from Rem sdoseminf to this file Rem alwu 11/19/08 - add intersection support Rem alwu 10/20/08 - add getting session setting function Rem vkolovsk 09/06/08 - sdosemutlb.sql Rem vkolovsk 09/03/08 - clique-related code Rem vkolovsk 08/28/08 - remove unnecessary functions Rem vkolovsk 08/07/08 - adding sameas-related utility procedures Rem alwu 03/29/07 - add collect stats Rem alwu 01/22/07 - move checkUserRules here as invoker may not have Rem permission to be RDF_RULE tab Rem alwu 12/12/06 - try different views for dml/query perf stats Rem alwu 09/07/06 - sync up 11g document and proposed APIs Rem alwu 09/06/06 - introduce sub hint Rem mannamal 07/19/06 - Add anc boolean variable Rem mannamal 07/05/06 - Add hash function Rem alwu 06/29/06 - change to definer Rem alwu 06/28/06 - factor out get/set proof from sdo_sem_infi Rem package Rem alwu 06/01/06 - move GET_STATS from sdoseminfb.sql Rem alwu 05/26/06 - start Rem alwu 05/26/06 - start Rem alwu 05/26/06 - Created Rem -- -- This is a schema private utlility package. -- create or replace package sdo_sem_utl as -- Constants for default namespaces and aliases RDF_ALIAS VARCHAR2(3) := 'rdf'; RDFS_ALIAS VARCHAR2(4) := 'rdfs'; XSD_ALIAS VARCHAR2(3) := 'xsd'; OWL_ALIAS VARCHAR2(3) := 'owl'; DC_ALIAS VARCHAR2(2) := 'dc'; DCTERMS_ALIAS VARCHAR2(7) := 'dcterms'; ORARDF_ALIAS VARCHAR2(6) := 'orardf'; RDF_NS VARCHAR2(43) := 'http://www.w3.org/1999/02/22-rdf-syntax-ns#'; RDFS_NS VARCHAR2(37) := 'http://www.w3.org/2000/01/rdf-schema#'; XSD_NS VARCHAR2(33) := 'http://www.w3.org/2001/XMLSchema#'; OWL_NS VARCHAR2(30) := 'http://www.w3.org/2002/07/owl#'; DC_NS VARCHAR2(32) := 'http://purl.org/dc/elements/1.1/'; DCTERMS_NS VARCHAR2(25) := 'http://purl.org/dc/terms/'; ORARDF_NS VARCHAR2(28) := 'http://xmlns.oracle.com/rdf/'; -- SYS.DBMS_DEBUG_VC2COLL TABLE OF VARCHAR2(1000) m_vaStatsNames SYS.DBMS_DEBUG_VC2COLL; m_naStatsValues MDSYS.SDO_NUMTAB; m_iIdSco int := 0; m_iIdType int := 0; m_iIdComp int := 0; m_iIdDisj int := 0; m_iIdEquc int := 0; m_iIdEqup int := 0; m_iIdIo int := 0; m_iIdSam int := 0; m_iIdDif int := 0; m_iIdDom int := 0; m_iIdRan int := 0; m_iIdSpo int := 0; m_iIdTP int := 0; -- owl:TransitiveProperty m_iIdFP int := 0; -- owl:FunctionalProperty m_iIdSymP int := 0; -- owl:SymmetricProperty m_iIdIFP int := 0; -- owl:InverseFunctionalProperty m_iIdCMP int := 0; -- rdfs:ContainerMembershipProperty m_iIdMbr int := 0; -- rdfs:member m_iIdPropDisj int := 0; -- owl:propertyDisjointWith OWL 2 feature m_iIdSkosxlLabel int := 0; -- skosxl:Label m_iIdSkosxlLiteralForm int := 0; -- skosxl:literalForm m_iIdNPA int := 0; -- owl:NegativePropertyAssertion m_iIdSrcInd int := 0; -- owl:sourceIndividual m_iIdAssPro int := 0; -- owl:assertionProperty m_iIdTgtInd int := 0; -- owl:targetIndividual m_iIdTgtVal int := 0; -- owl:targetValue m_iIdMaxQCard int := 0; -- owl:maxQualifiedCardinality m_iIdMaxCard int := 0; -- owl:maxCardinality m_iId0Int int := 0; -- "0"^^xsd:nonNegativeInteger m_iIdIrreflexiveProperty int := 0; -- m_iIdAsymmetricProperty int := 0; -- m_iIdOP int := 0; -- owl:onProperty m_iIdOC int := 0; -- owl:onClass m_iIdThing int := 0; -- owl:Thing m_iIdNothing int := 0; -- owl:Nothing m_vcInferTmpTabName varchar2(100) := null; /** * Converts a number list into a VARCHAR2 string for an * IN LIST containing the numbers. IN keyword is not included * only a list of numbers and enclosing parenthesis. */ function numToInList(numList SYS.ODCINumberList) return varchar2; /** * Returns true if filter f is safe w.r.t. SQL injection */ function isSafeFilter(f varchar2) return boolean; /** * Adjusts a timestamp value based on the given prefix * '+' add 1 day * '-' subtract 1 day * '@' no adjustment */ function adjTsForPrefix(ts timestamp, prefix varchar2) return timestamp deterministic; pragma restrict_references (adjTsForPrefix,WNDS,RNDS,WNPS,RNPS); /** * Adjusts a timestamp with time zone value based on the given prefix * '+' add 1 day * '-' subtract 1 day * '@' no adjustment */ function adjTsTzForPrefix(ts timestamp with time zone, prefix varchar2) return timestamp with time zone deterministic; pragma restrict_references (adjTsTzForPrefix,WNDS,RNDS,WNPS,RNPS); /** * Returns true if numList contains num, false otherwise * NOTE: it is ok for numList to be null */ function numberListContains(numList SYS.ODCINumberList, num number) return boolean; function get_sub_hint return varchar2; procedure set_sub_hint_aj; procedure set_sub_hint_empty; procedure set_sub_hint_aj_no_push_pred; function init_already return boolean; function get_all_property_id return sys.odciNumberList; /** * If returns NULL, it means that MDSYS has no privilege to read */ function get_session_parallel_query_dop return number; /** * If returns NULL, it means that MDSYS has no privilege to read */ function get_session_parallel_dml_dop return number; /** * This method returns TRUE if there is at least one user * defined rule in one of the rulebase specified by the list * of input rulebase IDs. * * NOTE: it is ok for @param nlRulbaseIDs to be NULL. */ function checkUserRules(nlRulbaseIDs sys.ODCINumberList) return boolean; /** * This method returns TRUE if vcPtnName partition exists in table * vcTableName. It is done here since we need to check against * MDSYS' user_tab_partitions. */ function check_partition_exists(vcTableName in varchar2, vcPtnName in varchar2) return boolean; function check_table_exists(vcTableName in varchar2) return boolean; PROCEDURE enable_all_triggers( user_name IN varchar2, apptab_name IN varchar2); PROCEDURE run_dml_stmt_merge(model_to_id number, stmt_name varchar2, dump_plan boolean default false, parallel int default 1); PROCEDURE disable_all_triggers(user_name IN varchar2, apptab_name IN varchar2); function get_parameter(vcOptions in varchar2, vcMark in varchar2) return varchar2; function get_string_parameter(vcOptions in varchar2, vcMark in varchar2) return varchar2; function get_int_parameter(vcOptions in varchar2, vcMark in varchar2) return int; -- function build_models_union_clause(models mdsys.rdf_models, -- bJustModels boolean, -- bProof boolean, -- vcRuleIdxPartView varchar2, -- bForceUsePCSMIdx boolean) return varchar2; /* MERGE MODELS HELPER FUNCTIONS - these need to be ran as MDSYS */ PROCEDURE merge_models_swap_tmp_merge_to(tbs_name varchar2, model_to_id number, tmp_tab_name varchar2, parallel number); FUNCTION merge_models_create_tmp_tab(tbs_name varchar2, model_from_id number, model_to_id number, merge_to_count number, merge_from_count number, user_name varchar2, tmp_tab_count IN OUT number, parallel number, options IN varchar2, NLTH number) RETURN varchar2; PROCEDURE merge_models_update_linktab(tbs_name IN varchar2, merge_from_model IN varchar2, merge_to_model IN varchar2, model_to_id IN number, merge_to_count IN number, tmp_tab_Name IN varchar2, tmp_tab_count IN number, parallel IN number, ITH IN number ); /* END MERGE MODELS HELPER FUNCTIONS */ GET_STATS constant varchar2(1000) := ' select name, value from ( select ''stat.. '' || n.name name, t.value value from v$mystat t join v$statname n on t.statistic# = n.statistic# where n.name in (''redo size'', ''db block gets'', ''consistent gets'', ''physical reads'',''sorts (memory)'',''sorts (disk)'', ''recursive calls'',''redo write time'', ''physical writes'',''rows processed'', ''bytes sent via SQL*Net to client'', ''bytes received via SQL*Net from client'', ''SQL*Net roundtrips to/from client'') or (n.name like ''%ga %'') or (n.name like ''%direct temp%'') union all select ''latch. '' || n.name name, n.gets value from v$latch n where n.name in (''library cache pin'', ''library cache pin allocation'', ''library cache'', ''shared pool'') ) order by name '; -- v$sesstat does not help much GET_STATS_NEW constant varchar2(1000) := ' select name, value from ( select ''stat.. '' || n.name name, t.value value from v$sesstat t join v$statname n on t.statistic# = n.statistic# where t.sid = sys_context(''userenv'',''sid'') and (n.name in (''redo size'', ''db block gets'', ''consistent gets'', ''physical reads'',''sorts (memory)'',''sorts (disk)'', ''recursive calls'',''redo write time'', ''physical writes'',''rows processed'', ''bytes sent via SQL*Net to client'', ''bytes received via SQL*Net from client'', ''SQL*Net roundtrips to/from client'') or (n.name like ''%ga %'') or (n.name like ''%direct temp%'')) union all select ''latch. '' || n.name name, n.gets value from v$latch n where n.name in (''library cache pin'', ''library cache pin allocation'', ''library cache'', ''shared pool'') ) order by name '; /** * Will collect statistics for table m_vcInferTmpTabName. */ procedure collect_stats; procedure exe_plan_prepare; procedure exe_plan_dump; function get_elapsed_time_in_sec(nStart number) return varchar2; function bool_to_str(b in boolean) return varchar2; procedure set_proof(b boolean); procedure set_anc(b boolean); function get_proof return boolean; function get_anc return boolean; /** * Note only model owner (or sys dba) is allowed to perform this action. * All information in the existing application table will be lost. * 'Triple' column will be reconstructed. */ PROCEDURE remove_duplicates(model_name in VARCHAR2, model_id in NUMBER, apptab_name in VARCHAR2, tbs_name in VARCHAR2, col_name in VARCHAR2, user_name in VARCHAR2, phase in INTEGER, owner_id in out NUMBER, table_id in out NUMBER); /** * Get the value of the iPos'th number out from * the given string that is delimited by a single space ' ' * * @param iPos MUST be a positive integer. */ function get_number_in_pos(vcVal in varchar2, iPos in int ) return number; function get_uri(vid number) return varchar2; function convert_mapped2raw(i int) return varchar2; function wrap_raw_col2num(vcRawColName varchar2) return varchar2; function wrap_num_col2raw(vcNumColName varchar2) return varchar2; function use_raw return boolean; FUNCTION get_value_type (value IN OUT varchar2) return varchar2; function get_safe_rowcount(cnt int) return int; procedure set_use_prh(b boolean); procedure set_use_raw8(b boolean); function extract_marker(vcStmt varchar2) return varchar2; -- function run_ddl_stmt(iRnd int, -- vcStmt varchar2, -- bPerfTuning boolean default false) return int; procedure set_start_time(n number); function get_columnar_compress_syn return varchar2; end; / show errors; -- C libraries --create or replace library ordmd_sem_libs trusted as static; --/ --grant execute on ordmd_sem_libs to public; --/ --create or replace package sem_hash_internal --authid current_user as -- function semhash ( -- k varchar2, -- initval binary_integer) -- return double precision -- deterministic -- as language C -- name "md_hash" -- library ordmd_sem_libs -- parameters( -- k string, -- k length, -- initval); --end sem_hash_internal; --/ --show errors; -- grant execute on sem_hash_internal to public;