Oracle dbms_lcr Examples

User Application Enqueue

newunit1 := SYS.LCR$_ROW_UNIT( 'TICKER',
SYS.AnyData.ConvertVarchar2('QCOM'),
DBMS_LCR.NOT_A_LOB, NULL, NULL);

newunit2 := SYS.LCR$_ROW_UNIT( 'OPEN_PX', SYS.AnyData.ConvertNumbe
(38.40), DBMS_LCR.NOT_A_LOB, NULL, NULL);

newunit3 := SYS.LCR$_ROW_UNIT( 'CLOSE_PX', SYS.AnyData.ConvertNumbe
(38.95), DBMS_LCR.NOT_A_LOB, NULL, NULL);

newunit4 := SYS.LCR$_ROW_UNIT( 'PX_DATE', SYS.AnyData.Convertdat
(sysdate+1), DBMS_LCR.NOT_A_LOB, NULL, NULL);

new_vals := SYS.LCR$_ROW_LIST(newunit1, newunit2,
newunit3, newunit4);

-- Now execute Store Proc to enqueue an Update DML
lcr_row_on_mktprice(
source_dbname => 'dnytst10.world',
cmd_type => 'UPDATE',
obj_owner => 'TEST1',
obj_name => 'MKTPRICE',
old_vals => old_vals,
new_vals => new_vals);
--
END;
/

To update a row and change the ticker AMZN to QCOM and also change the other columns' values, use the following SQL block:

DECLARE
newunit1 SYS.LCR$_ROW_UNIT;
newunit2 SYS.LCR$_ROW_UNIT;
newunit3 SYS.LCR$_ROW_UNIT;
newunit4 SYS.LCR$_ROW_UNIT;
new_vals SYS.LCR$_ROW_LIST;
old_vals SYS.LCR$_ROW_LIST;

BEGIN
newunit1 := SYS.LCR$_ROW_UNIT( 'TICKER',
SYS.AnyData.ConvertVarchar2('AMZN'),
DBMS_LCR.NOT_A_LOB, NULL, NULL)

newunit2 := SYS.LCR$_ROW_UNIT( 'OPEN_PX', SYS.AnyData.ConvertNumbe
(40.45), DBMS_LCR.NOT_A_LOB, NULL, NULL);

newunit3 := SYS.LCR$_ROW_UNIT( 'CLOSE_PX', SYS.AnyData.ConvertNumbe
(40.90), DBMS_LCR.NOT_A_LOB, NULL, NULL);

newunit4 := SYS.LCR$_ROW_UNIT( 'PX_DATE', SYS.AnyData.Convertdate( trun
(sysdate) ), DBMS_LCR.NOT_A_LOB, NULL, NULL);

old_vals := SYS.LCR$_ROW_LIST(newunit1, newunit2, newunit3, newunit4);
 

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo,  Donald Burleson, and Steve Callan).  Buy direct from the publisher and save 30%!