PROCEDURE PRINT_CLOB(P_CLOB IN CLOB) AS
L_OFFSET NUMBER DEFAULT 1;
BEGIN
LOOP
EXIT WHEN L_OFFSET > DBMS_LOB.GETLENGTH(P_CLOB);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
DBMS_LOB.SUBSTR(P_CLOB, 4000, L_OFFSET));
L_OFFSET := L_OFFSET + 4000;
END LOOP;
END;
由于使用FND_FILE.PUT_LINE没有dbms_output.put_line的250个字符的限制,但是Varchar2还是有4000字符的限制的。
FUNCTION SPLIT(P_STR IN VARCHAR2, P_PATTERN IN VARCHAR2 := ';')
RETURN T_VARRAY
AS
LTAB T_VARRAY := T_VARRAY();
POS INTEGER := 0;
LS VARCHAR2(255) := P_STR;
BEGIN
POS := INSTR(LS, P_PATTERN);
WHILE POS > 0 LOOP
LTAB.EXTEND;
LTAB(LTAB.COUNT) := SUBSTR(LS, 1, POS - 1);
LS := SUBSTR(LS, POS + 1);
POS := INSTR(LS, P_PATTERN);
END LOOP;
LTAB.EXTEND;
LTAB(LTAB.COUNT) := LS;
RETURN LTAB;
END;