Skip to Main Content
  • Questions
  • Need input on Dynamic Update stored procedure with bind variables

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Chris.

Asked: February 10, 2017 - 7:37 am UTC

Last updated: February 13, 2017 - 10:34 pm UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

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!!

and Connor said...

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.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A bit confused

Christopher Tarjono, February 11, 2017 - 4:00 am UTC

Hi Connor,

Thanks for responding so quickly, just need to confirm, when you said :

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:

which "b)" sql were you referring to? I think something is missing. Did yo mean something like this?

wsql := 'AND 1=1';

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 || ''', ';
ELSE
  wsql := wsql || ' AND :ORDER_ID IS NULL';
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 || ''', ';
ELSE
  wsql := wsql || ' AND :STATUS_CD IS NULL';
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 || ''', ';
ELSE
  wsql := wsql || ' AND :PRODUCT_ID IS NULL';
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 || ''', ';
ELSE
  wsql := wsql || ' AND :QUANTITY IS NULL';
END IF;

sql := sql || ' VERSION_NUMBER = VERSION_NUMBER + 1 WHERE ORDER_ITEM_ID = :ORDER_ITEM_ID' || wsql;
EXECUTE IMMEDIATE sql USING SP_U_T_ORDER_ITEM.ORDER_ITEM_ID, SP_U_T_ORDER_ITEM.ORDER_ID, SP_U_T_ORDER_ITEM.STATUS_CD, SP_U_T_ORDER_ITEM.PRODUCT_ID, SP_U_T_ORDER_ITEM.QUANTITY;


Also, please give your input on my question regarding updating foreign key value T_ORDER_ITEM.ORDER_ID, will this cause a problem? or will it only be a problem when i update T_ORDER.ORDER_ID (subsequent impact to child tables)?

aside from that, i understand your advise is to combine all the variants, i guess i need to understand more on which are the most commonly udpated columns in the table.
Connor McDonald
February 13, 2017 - 10:34 pm UTC

Yes that is correct. This way, anything with >10 binds can be captured with a single execute immediate passing ALL of the binds.

Of course, you can always drop back down to dbms_sql if thats your preference, but the theory is that this is the exceptional case anyway.

In terms of: "understand more on which are the most commonly udpated columns" you application can do this for you. Each time you run this code, write a record indicating which columns where specified to a log table.

After a few days/weeks/months etc you can use this to rationalise/improve the code.

Regarding the update - I generally would not update a column value (even to itself) if I can avoid it.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library