wm_concat标识符无效_【案例】ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效

手动创建Function

以SYS用户创建包、包体、函数

CREATE OR REPLACE TYPE WM_CONCAT_IMPL ASOBJECT

(

CURR_STRVARCHAR2(32767),

STATICFUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,

MEMBERFUNCTION ODCIAGGREGATEITERATE(SELF INOUT WM_CONCAT_IMPL,

P1IN VARCHAR2) RETURN NUMBER,

MEMBERFUNCTION ODCIAGGREGATETERMINATE(SELF INWM_CONCAT_IMPL,

RETURNVALUE OUTVARCHAR2,

FLAGSIN NUMBER)RETURN NUMBER,

MEMBERFUNCTION ODCIAGGREGATEMERGE(SELF INOUT WM_CONCAT_IMPL,

SCTX2IN WM_CONCAT_IMPL) RETURN NUMBER);

View Code

创建类型body

CREATE OR REPLACETYPE BODY WM_CONCAT_IMPLISSTATICFUNCTION ODCIAGGREGATEINITIALIZE(SCTX INOUT WM_CONCAT_IMPL)RETURN NUMBER

IS

BEGINSCTX := WM_CONCAT_IMPL(NULL) ;RETURNODCICONST.SUCCESS;END;

MEMBERFUNCTION ODCIAGGREGATEITERATE(SELF INOUT WM_CONCAT_IMPL,

P1IN VARCHAR2)RETURN NUMBER

IS

BEGIN

IF(CURR_STR IS NOT NULL) THENCURR_STR := CURR_STR || ',' ||P1;ELSECURR_STR :=P1;END IF;RETURNODCICONST.SUCCESS;END;

MEMBERFUNCTION ODCIAGGREGATETERMINATE(SELF INWM_CONCAT_IMPL,

RETURNVALUE OUTVARCHAR2,

FLAGSIN NUMBER)RETURN NUMBER

IS

BEGINRETURNVALUE :=CURR_STR ;RETURNODCICONST.SUCCESS;END;

MEMBERFUNCTION ODCIAGGREGATEMERGE(SELF INOUT WM_CONCAT_IMPL,

SCTX2INWM_CONCAT_IMPL)RETURN NUMBER

IS

BEGIN

IF(SCTX2.CURR_STR IS NOT NULL) THENSELF.CURR_STR := SELF.CURR_STR || ',' ||SCTX2.CURR_STR ;END IF;RETURNODCICONST.SUCCESS;END;END;

View Code

创建函数

CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;

创建同义词并授权

create public synonym WM_CONCAT_IMPL forsys.WM_CONCAT_IMPL;create public synonym wm_concat forsys.wm_concat;grant execute on WM_CONCAT_IMPL to public;grant execute on wm_concat to public;

SQL> @?/rdbms/admin/owmctab.plb

SQL> @?/rdbms/admin/owmaggrs.plb

SQL> @?/rdbms/admin/owmaggrb.plb

Before Oracle Database 12c, an Invoker Rights (IR) unit always ran with the privileges of its invoker.

If its invoker had higher privileges than its owner, then the IR unit might perform operations unintended by,

or forbidden to, its owner. As of Oracle Database 12c, an IR unit can run with the privileges

of its invoker only if its owner has either the INHERIT PRIVILEGES privilege on the invoker or the INHERIT ANY PRIVILEGES privilege.

GRANT INHERIT PRIVILEGES ON USER SYS TO public;

SQL> select owner,object_name,object_type from dba_objects where owner='WMSYS' and object_type ='FUNCTION';

OWNER OBJECT_NAME OBJECT_TYPE

-------------------- ---------------------------------------- -----------------------

WMSYS WM_CONCAT FUNCTION

SQL>