Oracle Database

Oracle DB 10G Recreate WMSYS And WMCONCAT

Bazı durumlarda WMSYS kullanıcısı yada WMCONCAT fonksiyonunu kaybedebilirsiniz. Eğer böyle bir durum ile karşılaşırsanız aşağıdaki adımları uygulamanız yeterli.

WMSYS Recreate

$sqlplus / as sysdba
SQL>@$ORACLE_HOME/rdbms/admin/owmctab.plb

Recreate Function

CREATE OR REPLACE TYPE wm_concat_impl
AUTHID CURRENT_USER
AS OBJECT (
curr_str VARCHAR2 (32767),
STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT wm_concat_impl,
p1 IN VARCHAR2
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (
SELF IN wm_concat_impl,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT wm_concat_impl,
sctx2 IN wm_concat_impl
)
RETURN NUMBER
);
/

[sql]
CREATE OR REPLACE TYPE BODY wm_concat_impl
IS
STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl)
RETURN NUMBER
IS
BEGIN
sctx := wm_concat_impl (NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT wm_concat_impl,
p1 IN VARCHAR2
)
RETURN NUMBER
IS
BEGIN
IF (curr_str IS NOT NULL)
THEN
curr_str := curr_str || ',' || p1;
ELSE
curr_str := p1;
END IF;

RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate (
SELF IN wm_concat_impl,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := curr_str;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT wm_concat_impl,
sctx2 IN wm_concat_impl
)
RETURN NUMBER
IS
BEGIN
IF (sctx2.curr_str IS NOT NULL)
THEN
SELF.curr_str := SELF.curr_str || ',' || sctx2.curr_str;
END IF;

RETURN odciconst.success;
END;
END;
/
CREATE OR REPLACE FUNCTION wm_concat (p1 VARCHAR2)
RETURN VARCHAR2
AGGREGATE USING wm_concat_impl;
/

Bugra Parlayan

I use this blog in my spare time to jot down thoughts and share my professional experiences. It’s my personal space to unwind and reflect. Feel free to share or reuse anything you find helpful here — just a small thank you is more than enough :) You can reach me at: bugra[@]bugraparlayan.com.tr

Related Articles

Back to top button
Close