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;