PL/SQL   «Prev 

Erase Oracle LOB

Inserting LOB data using SQL*Loader

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. Close the LOB variable, display the length of the destination variable, commit the changes, and end the PL/SQL block.
  7. SQL*Plus now displays the result of the compilation of your block. This is the end of the simulation. Click the Exit button.