Replace String in Oracle Procedure

Question

I have a problem about oracle procedure,since I'm new in sql language :D, here is my query

CREATE OR REPLACE PROCEDURE MONDESINT.UPDATECOADESCRIPTION IS

DESCRIPT MONDES_MSTR_CHART_OF_ACCOUNT.NAMA_AKUN%type;

BEGIN

    FOR SATU IN (select NO_AKUN, NAMA_AKUN 
                from mondes_mstr_chart_of_account 
                 where NO_AKUN LIKE '4-1-200-2-03-000%') 
    LOOP
        select REPLACE(NAMA_AKUN,SUBSTR(NAMA_AKUN,0,33),'Utang-Dana Deposit-USD') 
        INTO DESCRIPT 
        from mondes_mstr_chart_of_account 
        where NO_AKUN = '4-1-200-2-03-0009';

        update mondes_mstr_chart_of_account 
        set NAMA_AKUN = DESCRIPT 
        where NO_AKUN = '4-1-200-2-03-0009';
    END LOOP;

END UPDATECOADESCRIPTION;

In this case, I would replace some string in a column on a table. Column name is "NAMA_AKUN", I would replace NAMA_AKUN where NO_AKUN LIKE '4-1-200-2-03-000%'. In the code above, I try it for one record that NO_AKUN = '4-1-200-2-03-0009'.

select REPLACE(NAMA_AKUN,SUBSTR(NAMA_AKUN,0,33),'Utang-Dana Deposit-USD') 
  from mondes_mstr_chart_of_account 
 where NO_AKUN = '4-1-200-2-03-0009';

...runs well, it will replace "DOLLAR AMERICA" to "USD", IE: "BLA BLa Bla-DOLLAR AMERICA-Bla Bla" will be change to "BLA BLa Bla-USD-Bla Bla"

note : character length before "DOLLAR" is equal for all records.

The problem is...

When I execute this procedure, the result will be :"BLA BLa Bla-USD" without any on the next of USD. expected result is "BLA BLa Bla-USD-Bla Bla"

any idea ?

Answer 1

The way you have written the loop will update your one record multiple times, so you cannot be sure which precise string is applied last. Perhaps there's an account with a value of "BLA BLa Bla-DOLLAR AMERICA"?

You don't need all that PL/SQL to do this. A single SQL statement would suffice:

    update mondes_mstr_chart_of_account 
    set NAMA_AKUN = replace(NAMA_AKUN, 'DOLLAR AMERICA', 'USD')  
    where NO_AKUN = '4-1-200-2-03-0009';

It's simpler, it's more performant and it's correct. You can embed that statement in a stored procedure, parameterized to your requirements.

Answer 2

I believe that @APC has supplied the correct solution. You can test this without changing the data by converting the UPDATE statement to a SELECT:

SELECT NAMA_AKUN, REPLACE(NAMA_AKUN, 'DOLLAR AMERICA', 'USD') AS NEW_NAMA_AKUN
  FROM MONDES_MSTR_CHART_OF_ACCOUNT
  WHERE INSTR(NAMA_AKUN, 'DOLLAR AMERICA') > 0;

This will show you the current value of NAMA_AKUN and how it would look after being converted.

Share and enjoy.