这篇文章介绍了一些Oracle未写入帮助文档的函数,其中有些函数在跟踪文件(trace文件)Oracle后台系统给出的
[@more@]10.1.0
|
9.2.0
|
SYS_OP_VECOR 位运算 or 对16进制的进行的运算 两个参数应为 BINARY |
10.1.0$lg@tone.lg.ok >select SYS_OP_VECOR('ff','10') from dual; SY -- FF |
|
SYS_OP_VECXOR 位运算 xor 对16进制的进行的运算 两个参数应为 BINARY |
10.1.0$lg@tone.lg.ok >select SYS_OP_VECXOR('ff','10') from dual; SY -- EF |
|
SYS_OP_VECAND 位运算 and 对16进制的进行的运算 两个参数应为 BINARY |
10.1.0$lg@tone.lg.ok >select SYS_OP_VECAND('ff','10') from dual; SY -- 10 |
|
SYS_OP_VECAND 位运算 看SYS_OP_VECBIT('f',4) 第n位是为 1 或 0 位数的其实值从0开始的 |
10.1.0$lg@tone.lg.ok >select SYS_OP_VECBIT('3',0),SYS_OP_VECBIT('3',1),SYS_OP_VECBIT('3',2) from dual; SYS_OP_VECBIT('3',0) SYS_OP_VECBIT('3',1) SYS_OP_VECBIT('3',2) -------------------- -------------------- -------------------- 1 1 0 |
|
SYS_OP_NUMTORAW |
10.1.0$lg@tone.lg.ok >select SYS_OP_NUMTORAW(10) from dual; SY -- 0A |
|
SYS_OP_RAWTONUM |
10.1.0$lg@tone.lg.ok >select SYS_OP_RAWTONUM('0A') from dual; SYS_OP_RAWTONUM('0A') --------------------- 10 |
SYS_OP_DISTINCT |
9.2.0$lg@ntwo.lg.ok >create table t(n number,n2 number); 表已创建。 9.2.0$lg@ntwo.lg.ok >insert into t values(1,1); 已创建 1 行。 9.2.0$lg@ntwo.lg.ok >insert into t values(1,12); 已创建 1 行。 9.2.0$lg@ntwo.lg.ok >commit; 提交完成。 9.2.0$lg@ntwo.lg.ok >select SYS_OP_DISTINCT(n,n2) from t; SYS_OP_DISTINCT(N,N2) --------------------- 0 1 |
|
SYS_OP_COUNTCHG the sql is from X$KQLFXPL |
10.1.0$lg@tone.lg.ok >select count(unique dbms_rowid.rowid_block_number(rowid)) from t; COUNT(UNIQUEDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) ------------------------------------------------- 15 10.1.0$lg@tone.lg.ok >select SYS_OP_COUNTCHG(SUBSTRB(ROWIDTOCHAR("T".ROWID),1,15),1) from t; SYS_OP_COUNTCHG(SUBSTRB(ROWIDTOCHAR("T".ROWID),1,15),1) ------------------------------------------------------- 15 |
|
ADJ_DATE |
10.1.0$lg@tone.lg.ok >create table t(d date); 表已创建。 10.1.0$lg@tone.lg.ok >insert into t values(sysdate); 已创建 1 行。 10.1.0$lg@tone.lg.ok >select ADJ_DATE(d),d from t; ADJ_DATE(D) D ------------------- ------------------- 2004-12-21 21:25:44 2004-12-21 21:25:44 10.1.0$lg@tone.lg.ok >select ADJ_DATE(sysdate) from dual; select ADJ_DATE(sysdate) from dual * 第 1 行出现错误: ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 DATE |
|
--> 8.1.7
SYS_OP_GUID 和 sys_guid 功能类似
10.1.0$lg@tone.lg.ok >select SYS_OP_GUID() from dual; SYS_OP_GUID() -------------------------------- BE3FD65CA3944407AE636C6C6A4378CC
SYS_OP_MAKEOID
10.1.0$lg@tone.lg.ok >create or replace type T_TYPE as object (n number,v varchar2(20)); 2 / 类型已创建。 10.1.0$lg@tone.lg.ok >create or replace type N_TYPE as table of T_TYPE; 2 / 类型已创建。 10.1.0$lg@tone.lg.ok >create table fortv (n number,v varchar2(20),id number); 表已创建。 10.1.0$lg@tone.lg.ok >create or replace type fort as object (id number,f t_type); 2 / 类型已创建。 10.1.0$lg@tone.lg.ok >create view tv of fort with object identifier(id) as select id,t_type(n,v) from fortv; 视图已创建。 10.1.0$lg@tone.lg.ok >insert into fortv values(1,'one',1); 已创建 1 行。 10.1.0$lg@tone.lg.ok >select SYS_OP_MAKEOID(tv,id) from tv; SYS_OP_MAKEOID(TV,ID) ------------------------------------------------------------------------------------------------------------------------ 87DA01AC97B84B0DA20E26244918A3A80000001426010001000100290000000000090600812A00078401FE0000000A02C10200000000000000000000 00000000000000000000
SYS_OP_MAP_NONNULL The workaround is to create a function based index on those MV columns using create index mv_fidx on mv ( SYS_OP_MAP_NONNULL(col1), SYS_OP_MAP_NONNULL(col2),...) 它的返回值是 BINARY
10.1.0$lg@tone.lg.ok >create table t(n number); 表已创建。 10.1.0$lg@tone.lg.ok >insert into t values(null); 已创建 1 行。 10.1.0$lg@tone.lg.ok >insert into t values(-1); 已创建 1 行。 10.1.0$lg@tone.lg.ok >select SYS_OP_MAP_NONNULL(n) from t; SYS_OP_MAP_NONNULL(N) ---------------------------------------------- FF 3E646600
CSCONVERT 字符集转换 第2个参数可以为: NCHAR_CS NCHAR_CS
10.1.0$lg@tone.lg.ok >create table a(v varchar2(20),n nvarchar2(20)); 表已创建。 10.1.0$lg@tone.lg.ok >insert into a values('??',n'??'); 已创建 1 行。 10.1.0$lg@tone.lg.ok >select dump(CSCONVERT(v,'NCHAR_CS'),16) a,dump(v,16) b,dump(n,16) c from a; A B C ------------------------------ ------------------------------ ------------------------------ Typ=1 Len=2: 92,94 Typ=1 Len=2: e3,8d Typ=1 Len=2: 92,94 10.1.0$lg@tone.lg.ok >select dump(CSCONVERT(v,'CHAR_CS'),16) a,dump(v,16) b,dump(n,16) c from a; A B C ------------------------------ ------------------------------ ------------------------------ Typ=1 Len=2: e3,8d Typ=1 Len=2: e3,8d Typ=1 Len=2: 92,94 10.1.0$lg@tone.lg.ok >select dump(CSCONVERT(n,'NCHAR_CS'),16) a,dump(v,16) b,dump(n,16) c from a; A B C ------------------------------ ------------------------------ ------------------------------ Typ=1 Len=2: 92,94 Typ=1 Len=2: e3,8d Typ=1 Len=2: 92,94 10.1.0$lg@tone.lg.ok >select dump(CSCONVERT(n,'CHAR_CS'),16) a,dump(v,16) b,dump(n,16) c from a; A B C ------------------------------ ------------------------------ ------------------------------ Typ=1 Len=2: e3,8d Typ=1 Len=2: e3,8d Typ=1 Len=2: 92,94
SYS_OP_DESCEND 对位进行取反
10.1.0$lg@tone.lg.ok >select SYS_OP_DESCEND('0A') from dual; SYS_OP ------ CFBEFF
SYS_OP_TOSETID
9.2.0~lg@FIVE.LG.OK> desc ty -- 是一个内嵌表 Name Null? Type ----------------------------------------------------------- -------- -------- ID NUMBER T N_TYPE 9.2.0~lg@FIVE.LG.OK> select SYS_OP_TOSETID(t) from ty; SYS_OP_TOSETID(T) ------------------------------- F7D04BEB4DD54CFE80F802A6217F963C 2A6BB46652914C85ACF26B0656003806
-->
有些函数引起 ORA-03113 可以看看trace 文件和metalink 10.1.0 from oracle.exe SYS_OP_ITR SYS_XMLCONTAINS SYS_OP_UNDESCEND SYS_OP_NII SYS_OP_C2C CURRENTV ora-03113 SYS_OP_COMP SYS_OP_DECOMP SYS_OP_COLL_CONS SYS_ET_BLOB_TO_IMAGE SYS_ET_IMAGE_TO_BLOB INSERTXML from google metalink for xml SYS_ET_BFILE_TO_RAW SYS_ET_RAW_TO_BFILE SYS_CHECKACL from google xml db INSERTXMLBEFORE APPENDCHILDXML DELETEXML from google metalink for xml XMLROOT google for xml XMLCOMMENT for xml XMLPI for xml XMLCDATA for xml REF_AND_VALUE ORA-03113 SYS_OP_XPTHATG SYS_OP_XPTHOP SYS_OP_XPTHIDX ORA-03113 SYS_OP_XTXT2SQLT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 9.2.0 from oracle.exe SYS_OP_TPR LNNVL -- 9.2 没有 在10.1 中有 SYS_OP_CEG SYS_MAKEXML -- from google for XML SYS_XMLCONV SYS_UMAKEXML SYS_OP_IIX SYS_OP_ADT2BIN SYS_OP_BIN2ADT SYS_OP_CL2C 和 lob 相关 SYS_OP_BL2R 和 lob 相关 RANKM DENSE_RANKM PERCENT_RANKM CUME_DISTM FIRSTM SYS_OP_GROUPING SYS_AUDIT SYS_OP_MINE_VALUE ORA-00936: missing expression SYS_OP_COL_PRESENT ORA-00936 SYS_OP_LBID ORA-00938 ORA-01760 select count(distinct sys_op_lbid(32088,'L',t.rowid)) from t; 从index的leaf blocks直接获得信息 SYS_OP_NICOMBINE 5个参数ORA-00600 enicombine1 SYS_OP_NIEXTRACT 3个参数ORA-00600 iniextract1 SYS_OP_VVD ORA-00932 不一致的数据类型: 要求 UDT 得到的却是 CHAR select SYS_OP_VVD('T_TYPE',1) from dual SYS_OP_PAR SQL using SYS_OP_PAR() can fail with ORA-979. This affects the OLAP API. eg: create table A (x number, y number); SELECT XX, YY, TO_CHAR(SYS_OP_PAR(0, GROUPING_ID(XX, YY), XX, YY)) FROM ( SELECT A1.X XX, A2.X YY FROM A A1, A A2 WHERE A1.x =A2.y) GROUP BY XX, ROLLUP(YY) HAVING GROUPING_ID(XX,YY) =1; SYS_OP_PARGID ORA-03113 SYS_OP_LVL 它的返回值是 char(1) 有点像decode 可以输入很多的参数 XMLISVALID from google SYS_DOM_COMPARE 2个参数 ORA-00932: inconsistent datatypes: expected - got - SYS_OP_ALSCRVAL ORA-00904: "SYS_OP_ALSCRVAL": invalid identifier SYS_OP_CONVERT 和字符集相关 SYS_OP_PAR_1 SYS_OP_PARGID_1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 8.1.7 from oracle.exe MERGE$ACTIONS TBL$OR$IDX$PART$NUM responsible for retrieving details of which partition a particular value resides in. PLSFUN SYS_OP_REF 5个参数 SYS_OP_ATG SYS_OP_ATG(VALUE(KOKBF$),5,6,2)>10 from X$KQLFXPL SYS_OP_ADTCONS SYS_OP_DRA SYS_OP_NIX SYS_OP_DUMP SYS_OP_VREF SYS_OP_R2O 9.2.0~lg@FIVE.LG.OK> select SYS_OP_R2O(f) from tv; SYS_OP_R2O(F) --------------------------------------------------- C102036F6E650000B4C6A00588EC5B02 SYS_OP_RMTD SYS_OP_RDTM SYS_OP_OIDVALUE object view SYS_OP_LSVI SYS_OP_MSR SYS_OP_CSR SYS_OP_RPB which seems to return the rownumber within block of a row, which may be better than using the substr(rowid) method. SYS_OP_TRTB is used for trimming or padding strings. REGR_SLOPE 分析函数 REGR_INTERCEPT REGR_COUNT REGR_R2 REGR_AVGX REGR_AVGY REGR_SXX REGR_SYY REGR_SXY ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SYS_OP_NOEXPAND("A"."TYPE") X$KQLFXPL
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3898/viewspace-861922/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/3898/viewspace-861922/