2.4 Performing DML and Query Operations on LOBs in Nested Tables

This section describes the INSERT, UPDATE, and SELECT operations on LOBs in Nested Tables. To update LOBs in a nested table, you must lock the row containing the LOB explicitly.

To lock the row containing the LOB, you must specify the FOR UPDATE clause in the subquery prior to updating the LOB value. The following example shows how to perform DML and query operations on LOBs in nested tables.

Note:

Locking the row of a parent table does not lock the row of a nested table containing LOB columns.

Example 2-1 Performing DML and Query Operations on LOBs in Nested Tables

CONNECT pm/pm;
 
  
-------------------------------------------------------------------------
---------- Inserting LOBs in Nested Tables ------------------------------
-------------------------------------------------------------------------
  
-- INSERT a row into the NT column of print_media with actual data for lob
INSERT INTO print_media (product_id, ad_id, ad_textdocs_ntab)
VALUES
(1, 1, textdoc_tab(textdoc_typ('txt', to_blob('BABABABABABA')),
                   textdoc_typ('pdf', to_blob('AAAAAAAAAAAA'))));
  
-- INSERT a row into the NT column of print_media with empty_lob for the lob
INSERT INTO print_media (product_id, ad_id, ad_textdocs_ntab)
VALUES
(2, 2, textdoc_tab(textdoc_typ('txt', empty_blob()),
                   textdoc_typ('pdf', empty_blob())));
  
SET SERVEROUTPUT ON
 
-------------------------------------------------------------------------
---------- Read/Write LOBs in Nested Tables using locators --------------
-------------------------------------------------------------------------
 
-- INSERT-RETURNING, then write to the LOBs
DECLARE
  txt textdoc_tab;
BEGIN
  INSERT INTO print_media p(product_id, ad_id, ad_textdocs_ntab) VALUES
    (3, 3, textdoc_tab(textdoc_typ('txt', empty_blob()),
                       textdoc_typ('pdf', empty_blob())))
  RETURNING p.ad_textdocs_ntab into txt;
  
  for elem in 1 .. txt.count loop
    DBMS_LOB.WRITEAPPEND(txt(elem).formatted_doc, 2, hextoraw(elem||'FF'));
  end loop;
END;
/
  
SELECT ad_textdocs_ntab FROM print_media WHERE product_id = 3;
 
-- SELECT on NT lob, then read
DECLARE
  txt textdoc_tab;
  pos INTEGER;
  amt INTEGER;
  buf RAW(40);
BEGIN
  SELECT ad_textdocs_ntab INTO txt FROM print_media WHERE product_id = 1;
  
  for elem in 1 .. txt.count loop
    amt := 40; 
    pos := 1;
    DBMS_LOB.READ(txt(elem).formatted_doc, amt, pos, buf);
    DBMS_OUTPUT.PUT_LINE(buf);
  end loop;
END;
/
  
-- SELECT for update on the NT lob, then write
DECLARE
  txt textdoc_tab;
  pos INTEGER;
  amt INTEGER;
  buf RAW(40);
BEGIN
  SELECT ad_textdocs_ntab INTO txt FROM print_media
  WHERE product_id = 1 FOR UPDATE;
  
  for elem in 1 .. txt.count loop
    DBMS_LOB.WRITEAPPEND(txt(elem).formatted_doc, 2, hextoraw(elem||'FF'));
  end loop;
END;
/
  
SELECT ad_textdocs_ntab FROM print_media WHERE product_id = 1;