PL/SQL escape string for string interpolation-CodePudding

I have the following API which takes a clob as one of it's arguments.

Receive_Order_API.Packed_Arrival(clob_,inParam1_,inParam2_);

When I use the debugger in our ERP, the api-call looks like this:

DECLARE
clob_ VARCHAR2(32000) := '!
$HEADER_START=TRUE
$SOURCE_REF1=45963
$SOURCE_REF2=1
$SOURCE_REF3=1
$SOURCE_REF4=
$SOURCE_REF_TYPE_DB=PURCHASE_ORDER
$CONV_FACTOR=1
$CONTRACT=3566
$DESCRIPTION=EX 1000V 3x95mm² AL
$LINE_END=TRUE
$HEADER_END=TRUE
';

   inParam1_ VARCHAR2(32000) := '';
   inParam2_ VARCHAR2(32000) := 'FALSE';

BEGIN
    clob_ := Receive_Order_API.Packed_Arrival(clob_,inParam1_,inParam2_);

END;

What I am trying to do is to replace the data with my own values fetched from another table, something like this:

$SOURCE_REF1=:someOtherValue
$SOURCE_REF2=1
$SOURCE_REF3=1
$SOURCE_REF4=
$SOURCE_REF_TYPE_DB=PURCHASE_ORDER
$CONV_FACTOR=1
$CONTRACT=:someOtherValue

I guess somehow I need to escape the string to be able to replace the values but I can't figure out how and have been stuck on this for a while now.

Any help would be much appreciated!

CodePudding user response：

Something like this?

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     clob_          VARCHAR2 (32000) := '!
  3  $HEADER_START=TRUE
  4  $SOURCE_REF1=45963
  5  $SOURCE_REF2=1
  6  $SOURCE_REF3=1
  7  $SOURCE_REF4=
  8  $SOURCE_REF_TYPE_DB=PURCHASE_ORDER
  9  $CONV_FACTOR=1
 10  $CONTRACT=3566
 11  $DESCRIPTION=EX 1000V 3x95mm² AL
 12  $LINE_END=TRUE
 13  $HEADER_END=TRUE
 14  ';
 15
 16     inParam1_      VARCHAR2 (32000) := '';
 17     inParam2_      VARCHAR2 (32000) := 'FALSE';
 18
 19     l_source_ref1  NUMBER;
 20     l_contract     NUMBER;
 21  BEGIN
 22     SELECT 11111, 22222
 23       INTO l_source_ref1, l_contract
 24       FROM DUAL;
 25
 26     clob_ :=
 27        REGEXP_REPLACE (clob_,
 28                        '\$SOURCE_REF1=\d ',                 --> escape $
 29                        '$SOURCE_REF1=' || l_source_ref1);
 30     clob_ :=
 31        REGEXP_REPLACE (clob_, '\$CONTRACT=\d ',             --> escape $
 32                               '$CONTRACT=' || l_contract);
 33
 34     DBMS_OUTPUT.put_line (clob_);
 35  END;
 36  /

Result:

!
$HEADER_START=TRUE
$SOURCE_REF1=11111                 --> new value here
$SOURCE_REF2=1
$SOURCE_REF3=1
$SOURCE_RE
F4=
$SOURCE_REF_TYPE_DB=PURCHASE_ORDER
$CONV_FACTOR=1
$CONTRACT=22222                    --> and here
$DESCRIPTI
ON=EX 1000V 3x95mm2 AL
$LINE_END=TRUE
$HEADER_END=TRUE


PL/SQL procedure successfully completed.

SQL>