What correction must be done to ensure the PL/SQL block executes successfully?

DATA_FILES is a directory object that contains the DETAILS.TXT text file.
You have the required permissions to access the directory object.

You create a table using the following command:
CREATE TABLE clob_tab(col2 CLOB);
View the Exhibit and examine the PL/SQL block that you execute for loading the external text file
into the table that currently has no rows. The PL/SQL block results in an error.

DATA_FILES is a directory object that contains the DETAILS.TXT text file.
You have the required permissions to access the directory object.

What correction must be done to ensure the PL/SQL block executes successfully?

A.
The L_OUT variable must be initialized to an empty locator.

B.
The L_OUT variable has to be declared as a temporary LOB.

C.
The A_CLOB variable has to be declared as a temporary LOB.

D.
The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to
correctly initialize the locator.

Show Hint

← Previous question

Next question →

PIERO

TEST PROPOSE ONLY D SOLUTION BUT REALLY ALSO C IS RIGHT

PLEASE SOMEONE CAN REPLY TO ME TO EXPLAIN WHY C IS WRONG ANSWER ?

ABOUT C :

DECLARE
A_CLOB CLOB := EMPTY_CLOB();
— A_BFILE BFILE := BFILENAME(‘DATA_FILES’,’details.txt’);
A_BFILE BFILE := BFILENAME(‘DIRPM’,’prova_testo.txt’);
N NUMBER;
L_OUT CLOB;
BEGIN
DBMS_LOB.FILEOPEN(A_BFILE);
dbms_lob.createtemporary(A_CLOB,false);
DBMS_LOB.LOADFROMFILE(A_CLOB, A_BFILE, DBMS_LOB.GETLENGTH(A_BFILE));
INSERT INTO CLOB_TAB_T(COL2) VALUES(A_CLOB);
DBMS_LOB.FILECLOSE(A_BFILE);
COMMIT;
SELECT COL2 INTO L_OUT FROM CLOB_TAB_T;
N := DBMS_LOB.GETLENGTH(L_OUT);
DBMS_OUTPUT.PUT_LINE(N);
END;

SELECT COL2 FROM CLOB_TAB_T

ABBARABBACCICCICOCCO

ABOUT D :

DECLARE
A_CLOB CLOB := EMPTY_CLOB();
— A_BFILE BFILE := BFILENAME(‘DATA_FILES’,’details.txt’);
A_BFILE BFILE := BFILENAME(‘DIRPM’,’prova_testo.txt’);
N NUMBER;
L_OUT CLOB;
BEGIN
INSERT INTO CLOB_TAB(COL2) VALUES(EMPTY_CLOB)
RETURNING COL2 INTO A_CLOB;
DBMS_LOB.FILEOPEN(A_BFILE);
DBMS_LOB.LOADFROMFILE(A_CLOB, A_BFILE, DBMS_LOB.GETLENGTH(A_BFILE));
DBMS_LOB.FILECLOSE(A_BFILE);
COMMIT;
SELECT COL2 INTO L_OUT FROM CLOB_TAB;
N := DBMS_LOB.GETLENGTH(L_OUT);
DBMS_OUTPUT.PUT_LINE(N);
END;

SELECT COL2 FROM CLOB_TAB

ABBARABBACCICCICOCCO

———— I’ll be glad to receive a reply about it

Thomas Kyte

Don’t change an order of lines!
“C” is wrong because of insert before reading file.