1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Package to manipulate CLOB easier with different FUNCTIONs and PROCEDUREs

CREATE OR REPLACE PACKAGE use_clob
AS
PROCEDURE init (p_clob IN OUT CLOB);

FUNCTION UPPER (p_clob IN OUT NOCOPY CLOB)
RETURN CLOB;

FUNCTION REPLACE (
p_clob IN OUT NOCOPY CLOB,
p_pattern VARCHAR2 DEFAULT NULL,
p_expr VARCHAR2 DEFAULT NULL
)
RETURN CLOB;

PROCEDURE PRINT (p_clob IN OUT NOCOPY CLOB);
END use_clob;
/

CREATE OR REPLACE PACKAGE BODY use_clob
AS
PROCEDURE init (p_clob IN OUT CLOB)
IS
amount NUMBER;
BEGIN
DBMS_LOB.createtemporary (p_clob, TRUE, DBMS_LOB.SESSION);
amount := 1;
DBMS_LOB.WRITE (p_clob, amount, 1, '');
EXCEPTION
WHEN OTHERS
THEN
HTP.p (SQLERRM);
END init;

FUNCTION UPPER (p_clob IN OUT NOCOPY CLOB)
RETURN CLOB
IS
lv_clob CLOB := EMPTY_CLOB ();
ln_offset NUMBER := 1;
ln_amount NUMBER := 32767;
lv_buffer VARCHAR2 (32767) := NULL;
ln_length NUMBER := DBMS_LOB.getlength (p_clob);
BEGIN
WHILE (ln_offset < ln_length)
LOOP
DBMS_LOB.READ (p_clob, ln_amount, ln_offset, lv_buffer);
lv_buffer := UPPER (lv_buffer);
DBMS_LOB.writeappend (lv_clob, ln_amount, lv_buffer);
ln_offset := ln_offset + ln_amount;
END LOOP;

RETURN lv_clob;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
HTP.p (SQLERRM);
END UPPER;

FUNCTION REPLACE (
p_clob IN OUT NOCOPY CLOB,
p_pattern VARCHAR2 DEFAULT NULL,
p_expr VARCHAR2 DEFAULT NULL
)
RETURN CLOB
IS
lv_clob CLOB := EMPTY_CLOB ();
ln_offset NUMBER := 1;
ln_amount NUMBER := 32767;
lv_buffer VARCHAR2 (32767) := NULL;
ln_length NUMBER := DBMS_LOB.getlength (p_clob);
x_pattern_is_null EXCEPTION;
BEGIN
IF (p_pattern IS NOT NULL)
THEN
WHILE (ln_offset < ln_length)
LOOP
DBMS_LOB.READ (p_clob, ln_amount, ln_offset, lv_buffer);
lv_buffer := REPLACE (lv_buffer, p_pattern, p_expr);
DBMS_LOB.writeappend (lv_clob, ln_amount, lv_buffer);
ln_offset := ln_offset + ln_amount;
END LOOP;

RETURN lv_clob;
ELSE
RAISE x_pattern_is_null;
END IF;
EXCEPTION
WHEN x_pattern_is_null
THEN
RETURN NULL;
HTP.p ('Erreur de paramètres dans CLOB_REPLACE.');
WHEN OTHERS
THEN
RETURN NULL;
HTP.p (SQLERRM);
END REPLACE;

PROCEDURE PRINT (p_clob IN OUT NOCOPY CLOB)
IS
offset NUMBER := 1;
amount NUMBER := 32767;
len NUMBER := DBMS_LOB.getlength (p_clob);
lc_buffer VARCHAR2 (32767);
BEGIN
IF (DBMS_LOB.ISOPEN (p_clob) != 1)
THEN
DBMS_LOB.OPEN (p_clob, 0);
END IF;

WHILE (offset < len)
LOOP
DBMS_LOB.READ (p_clob, amount, offset, lc_buffer);
HTP.prn (lc_buffer);
offset := offset + amount;
END LOOP;

IF (DBMS_LOB.ISOPEN (p_clob) = 1)
THEN
DBMS_LOB.CLOSE (p_clob);
END IF;
EXCEPTION
WHEN OTHERS
THEN
HTP.p (SQLERRM);
END PRINT;
END use_clob;
/

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy