Examine the structure of the TEST_DETAILS table:
Name Null? Type
——————- ——– ————-TEST_ID NUMBER
DESCRIPTION CLOB
DESCRIPTION data was entered earlier and saved for TEST_ID 12.
You execute this PL/SQL block to add data to the end of the existing data in the DESCRIPTION
column for TEST_ID 12:
DECLARE
clob_loc CLOB;
buf CHAR(12);
BEGIN
SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ;
buf := ‘0123456789’;
DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf);
COMMIT;
END;
/
It generates an error on execution.
What correction should you do to achieve the required result?
A.
WRITEAPPEND must be replaced with APPEND.
B.
The BUF variable data type must be changed to CLOB.
C.
FOR UPDATE must be added to the SELECT statement.
D.
The GETLENGTH routine must be replaced with the LENGTH built-in function in
WRITEAPPEND.
C, 100%
D
C
DROP TABLE TEST_DETAILS PURGE
CREATE TABLE test_details
(
TEST_ID INTEGER,
DESCRIPTION CLOB
);
INSERT INTO TEST_DETAILS VALUES (12 , ‘ABC’);
COMMIT;
SELECT * FROM TEST_DETAILS;
DECLARE
clob_loc CLOB;
buf CHAR(12);
BEGIN
SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ;
buf := ‘0123456789’;
DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf);
COMMIT;
END;
ORA-22920: riga contenente il valore LOB non bloccata
ORA-06512: a “SYS.DBMS_LOB”, line 1146
ORA-06512: a line 7
DECLARE
clob_loc CLOB;
buf CHAR(12);
BEGIN
SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 FOR UPDATE ;
buf := ‘0123456789’;
DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf);
COMMIT;
END;
SELECT * FROM TEST_DETAILS;
12 ABC0123456789
Thanks Piero for your testing. A persistent CLOB variable is associated with one column, like you select one LOB column into one persistent LOB variable. Not like scalar column, you need to explicily write-lock the row before really update if you want to update the LOB columm. The reason is that the rollback overhead of scalar row is comparative smaller than that of LOB row.