Hi AskTom Team,
Good day to you! I have some question on some best practice on implementing dynamic update statement using Stored Procedure. I have read many example in this site alone, also other sources, but none seems to answer my specific question, so below i have an example of dynamic update sql (literal) that i want to change using bind vars, please let me know pros/cons of each variant and which are preferred.
Simplified environment:
table T_ORDER with columns:
- ORDER_ID (PK)
- STATUS_CD
- ORDER_DATE
- CUSTOMER_ID
- VERSION_NUMBER
table T_ORDER_ITEM with columns:
- ORDER_ITEM_ID (PK)
- ORDER_ID (FK to T_ORDER.ORDER_ID)
- STATUS_CD
- PRODUCT_ID
- QUANTITY
- VERSION_NUMBER
Problem SQL (Procedure name is SP_U_T_ORDER_ITEM):
sql := 'UPDATE T_ORDER_ITEM SET';
IF (SP_U_T_ORDER_ITEM.ORDER_ID IS NOT NULL AND LENGTH(SP_U_T_ORDER_ITEM.ORDER_ID) > 0) THEN
sql := sql || ' ORDER_ID = ''' || SP_U_T_ORDER_ITEM.ORDER_ID || ''', ';
END IF;
IF (SP_U_T_ORDER_ITEM.STATUS_CD IS NOT NULL AND LENGTH(SP_U_T_ORDER_ITEM.STATUS_CD) > 0) THEN
sql := sql || ' STATUS_CD = ''' || SP_U_T_ORDER_ITEM.STATUS_CD || ''', ';
END IF;
IF (SP_U_T_ORDER_ITEM.PRODUCT_ID IS NOT NULL AND LENGTH(SP_U_T_ORDER_ITEM.PRODUCT_ID) > 0) THEN
sql := sql || ' PRODUCT_ID = ''' || SP_U_T_ORDER_ITEM.PRODUCT_ID || ''', ';
END IF;
IF (SP_U_T_ORDER_ITEM.QUANTITY IS NOT NULL AND LENGTH(SP_U_T_ORDER_ITEM.QUANTITY) > 0) THEN
sql := sql || ' QUANTITY = ''' || SP_U_T_ORDER_ITEM.QUANTITY || ''', ';
END IF;
sql := sql || ' VERSION_NUMBER = VERSION_NUMBER + 1 WHERE ORDER_ITEM_ID = :ORDER_ITEM_ID';
EXECUTE IMMEDIATE sql USING SP_U_T_ORDER_ITEM.ORDER_ITEM_ID;
So with the above sql, obviously its a very bad literal sql. Only 1 input param is mandatory which is ORDER_ITEM_ID hence that is the only bind variable supplied to the EXECUTE IMMEDIATE.
Below are some variants that i came up with, please advise:
Variant 1:
v_param VARCHAR2(4000);
arrLOV sys.OdciVarchar2List := sys.OdciVarchar2List();
varCount PLS_INTEGER := 0;
sql := 'UPDATE T_ORDER_ITEM SET VERSION_NUMBER = VERSION_NUMBER + 1';
IF (SP_U_T_ORDER_ITEM.ORDER_ID IS NOT NULL AND LENGTH(SP_U_T_ORDER_ITEM.ORDER_ID) > 0) THEN
v_param:= v_param ||'|'||SP_U_T_ORDER_ITEM.ORDER_ID;
varCount := varCount+1;
varDynQuery:= varDynQuery || ', ORDER_ID = :ORDER_ID ';
END IF;
IF (SP_U_T_ORDER_ITEM.STATUS_CD IS NOT NULL AND LENGTH(SP_U_T_ORDER_ITEM.STATUS_CD) > 0) THEN
v_param:= v_param ||'|'||SP_U_T_ORDER_ITEM.STATUS_CD;
varCount := varCount+1;
varDynQuery:= varDynQuery || ', STATUS_CD = :STATUS_CD ';
END IF;
IF (SP_U_T_ORDER_ITEM.PRODUCT_ID IS NOT NULL AND LENGTH(SP_U_T_ORDER_ITEM.PRODUCT_ID) > 0) THEN
v_param:= v_param ||'|'||SP_U_T_ORDER_ITEM.PRODUCT_ID;
varCount := varCount+1;
varDynQuery:= varDynQuery || ', PRODUCT_ID = :PRODUCT_ID ';
END IF;
IF (SP_U_T_ORDER_ITEM.QUANTITY IS NOT NULL AND LENGTH(SP_U_T_ORDER_ITEM.QUANTITY) > 0) THEN
v_param:= v_param ||'|'||SP_U_T_ORDER_ITEM.QUANTITY;
varCount := varCount+1;
varDynQuery:= varDynQuery || ', QUANTITY = :QUANTITY ';
END IF;
sql := sql || ' WHERE ORDER_ITEM_ID = :ORDER_ITEM_ID';
FOR arr IN (SELECT REGEXP_SUBSTR (v_param,'[^|]+',1,LEVEL) param FROM DUAL CONNECT BY REGEXP_SUBSTR (v_param,'[^|]+',1,LEVEL) IS NOT NULL)
LOOP
arrLOV.EXTEND;
arrLOV (arrLOV.COUNT) := arr.param;
END LOOP;
CASE varCount
WHEN 1 THEN
EXECUTE IMMEDIATE sql USING arrLOV(1),SP_U_T_ORDER_ITEM.ORDER_ITEM_ID;
WHEN 2 THEN
EXECUTE IMMEDIATE sql USING arrLOV(1),arrLOV(2),SP_U_T_ORDER_ITEM.ORDER_ITEM_ID;
WHEN 3 THEN
EXECUTE IMMEDIATE sql USING arrLOV(1),arrLOV(2),arrLOV(3),SP_U_T_ORDER_ITEM.ORDER_ITEM_ID;
WHEN 4 THEN
EXECUTE IMMEDIATE sql USING arrLOV(1),arrLOV(2),arrLOV(3),arrLOV(4),SP_U_T_ORDER_ITEM.ORDER_ITEM_ID;
END CASE;
This should be a solid workaround using bind variables, but some of our tables have > 80 columns and sometimes hundreds of input params to the SP, and with the way the EXECUTE IMMEDIATE is formed, it will be a very2 long SP and will reduce readibility/maintainability. Is there a limit to how long/max lines of SP in oracle 11g?
Variant 2:
DECLARE
REC T_ORDER_ITEM%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO REC FROM T_ORDER_ITEM WHERE ORDER_ITEM_ID = SP_U_ORDER_ITEM.ORDER_ITEM_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE;
WHEN OTHERS THEN
RAISE;
END;
UPDATE T_ORDER_ITEM
SET ORDER_ID = CASE WHEN SP_U_ORDER_ITEM.ORDER_ID IS NOT NULL AND SP_U_ORDER_ITEM.ORDER_ID <> REC.ORDER_ID THEN SP_U_ORDER_ITEM.ORDER_ID ELSE REC.ORDER_ID END,
STATUS_CD = CASE WHEN SP_U_ORDER_ITEM.STATUS_CD IS NOT NULL AND SP_U_ORDER_ITEM.STATUS_CD <> REC.STATUS_CD THEN SP_U_ORDER_ITEM.STATUS_CD ELSE REC.STATUS_CD END,
PRODUCT_ID = CASE WHEN SP_U_ORDER_ITEM.PRODUCT_ID IS NOT NULL AND SP_U_ORDER_ITEM.PRODUCT_ID <> REC.PRODUCT_ID THEN SP_U_ORDER_ITEM.PRODUCT_ID ELSE REC.PRODUCT_ID END,
QUANTITY = CASE WHEN SP_U_ORDER_ITEM.QUANTITY IS NOT NULL AND SP_U_ORDER_ITEM.QUANTITY <> REC.QUANTITY THEN SP_U_ORDER_ITEM.QUANTITY ELSE REC.QUANTITY END,
VERSION_NUMBER = VERSION_NUMBER + 1
WHERE ORDER_ITEM_ID = SP_U_ORDER_ITEM.ORDER_ITEM_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
This variant is inspired by the great Tom Kyte himself (
http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50asktom-165477.html ).
Quoting him below:
BEGIN
UPDATE detail
SET acolumn = 1,
bcolumn = nvl(p_hiredate, bcolumn),
<ccolumn and others here> ...
<WHERE clause if provided>
END;
And that’s it. If P_HIREDATE is null, then the UPDATE will effectively set BCOLUMN to itself. If P_HIREDATE is not null, then P_HIREDATE will be used to update BCOLUMN. It is true that this would generate a bit more redo and undo, because every column would be modified every time, but because the indexes would not be maintained for any indexed column whose value did not actually change, this would be minimal. For the first case, then, the solution is to use static SQL and NVL().
I didnt use NVL here since I needed additional validation. Also how big of an impact would it be if ORDER_ID, which is a foreign key to a parent T_ORDER table, is being updated everytime ? Will it cause contention?
In general each ORDER will have around 10-20 ORDER_ITEM and during order processing, each ORDER_ITEM records will be updated in no order whatsoever (sent to third party system, FIFO, batch processes) by asynchronous fulfillment agents (at least 5 daemon agents updating these 10-20 ORDER_ITEMS at all times) until the STATUS_CD is udpated to complete.
In our real ORDER_ITEM table, it could have up to 7 foreign keys, hence the worry.
Please let me know your thoughts on this, pros/cons of each variants, or if you ahve a better solution to my current predicament.
Thanks again for your time, keep up the good work!!
They are not mutually exclusive options. I like the idea of having your "best" code for the "most common" usage, with a fallback to the more generic case.
So for example, I might choose to do:
a) Lets say the most common usage is an update with 2 known parameters. I'd code that explicitly:
if param1 is not null and param7 is not null and [all others are null] then
update T
set col1 = param1, col7=param7
where pk = ...
b) then I'll perhaps drop into variant 1 for (say) up to 10 parameters
c) which leaves me now at situations with 10+ parameters. I might *all* of them ( because I'm expecting them to be rare) with a bucket 'execute immediate' that uses *all* the parameters, ie I'll build my sql as per (b) above, but then add:
"and :bind18 is null"
"and :bind19 is null"
etc etc all the way to my max (lets says its 30 potential binds).
Now I have a single execute immediate which is:
execute immediate '...' using param1, ...., param30
where (in this case) param18 through param30 were null.
Hope that makes sense.