Erase Oracle LOB
Inserting LOB data using SQL*Loader
-
With SQL*Plus, connect to the database by using
PETSTORE
as the User Name, GREATPETS
as the Password, and
MYDB
as the Host String. Approve the entries.
-
You are now connected to the database. Start the PL/SQL block by declaring two variables: one a destination variable to store the value of
the LOB and the other a source variable to point at the file within the file system. This variable must be of type
BFILE
and
should point to a directory and a specific file within it, which must be loaded to the LOB column.
- Start the procedural section with a
BEGIN
statement and select the BLOB data into the variable defined above from
PRODUCT_PHOTO_OBJ_TABLE, where PRODUCT_ID
is 10 with a FOR UPDATE
clause.
- Open the LOB variable using
DBMS_LOB.FILEOPEN
, which requires one parameter: the variable name with the LOB value from the
file. Once the LOB variable is open, get the length of the source variable by using DBMS_LOB.GETLENGTH
and display it using
DBMS_OUTPUT.PUT_LINE
.
- Now load the data into the destination variable from the source variable by using the
DBMS_LOB.LOADFROMFILE
function, which
requires three variables: the destination variable, the source variable, and the length of the LOB.
-
Close the LOB variable, display the length of the destination variable, commit the changes, and end the PL/SQL block.
- SQL*Plus now displays the result of the compilation of your block. This is the end of the simulation. Click the Exit button.