ORA-01578 repair after data block corruption

In Oracle databases, you can use the wm_concat (column) function to merge fields. However, this method is not recommended by Oracle because WMSYS is used for Workspace Manager, the function objects may vary with versions, and such changes are reflected in 11.2.0.3 and 10.2.0.5. The original return value of the WM_CONCAT function is VARCHAR2, but in some versions it will become more CLOB. This change causes many program exceptions.

Oracle recommends that you use a user-defined function to implement this function, instead of using the internal function of WorkSpace. This Function contains a Type, Type Body, and Function. You can refer to the implementation method of Oracle to implement this Function.

The following describes the use of the wm_concat (column) function in oracle and the custom function zh_concat for merging multiline records.

1. Use of the wm_concat (column) function in oracle

T_student table:

SNAME SDREE SAGE SEX

Li Kun, 26 male

Cao guisheng, 26 male from Bank of China

27 male Liu Bo

Ji zhengguang IBM 23 female

Li xueyu, 25 female from Microsoft

25 female Li Xueqi wensi

26 male Chen Xu, CNOOC

Han Zhengyang, 24 male from CNOOC

Chen Weidong nuclear 24 male

Liu Bing, Youku, 24 male

Ding Chengyun Lenovo 25 female

25 male Wang Peng ZTE

 

The expected result is:

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

Male Li Kun, Cao guisheng, Liu Bo, Chen Weidong, Han Zhengyang, Chen Xu, Liu Bing, Wang Peng

Ji zhengguang, Li xueyu, Ding Chengyun, Li Xueqi

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

The SQL statement is:

Select t. sex, wmsys. wm_concat (t. sname) from t_student tgroup by t. sex

Ii. Custom function zh_concat (extracted from the Internet and available directly)

Wm_concat appears in oracle10g, which is not only encrypted, but also in a separate user, which is inconvenient to use. And versions earlier than 10 Gb cannot be used. It is easy to use

Run the following type and the Creation script of a function to build your own wm_concat (zh_concat ). The usage is the same as that of wm_concat:

Type:

 

Create or replace TYPE zh_concat_im

AUTHID CURRENT_USER AS OBJECT

(

CURR_STR VARCHAR2 (32767 ),

Static function odciaggresponinitialize (sctx in out zh_concat_im) return number,

Member function odciaggregateiterate (self in out zh_concat_im,

P1 IN VARCHAR2) return number,

Member function odciaggresponterminate (self in zh_concat_im,

Returnvalue out VARCHAR2,

Flags in number)

Return number,

Member function odciaggregatemerge (self in out zh_concat_im,

SCTX2 IN zh_concat_im) RETURN NUMBER

);

/

 

Create or replace type body zh_concat_im

IS

Static function odciaggresponinitialize (sctx in out zh_concat_im)

RETURN NUMBER

IS

BEGIN

SCTX: = zh_concat_im (NULL );

Return odciconst. SUCCESS;

END;

Member function odciaggregateiterate (self in out zh_concat_im,

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 odciaggresponterminate (self in zh_concat_im,

Returnvalue out VARCHAR2,

Flags in number)

RETURN NUMBER

IS

BEGIN

RETURNVALUE: = CURR_STR;

Return odciconst. SUCCESS;

END;

Member function odciaggregatemerge (self in out zh_concat_im,

SCTX2 IN zh_concat_im)

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;

/

Function:

Create or replace FUNCTION zh_concat (P1 VARCHAR2)

RETURN VARCHAR2 aggregate using zh_concat_im;

SPFILE error causing database startup failure (ORA-01565)

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting