Use column name as key for PL/SQL associative array when updating another column

Question

I have an Excel table with two columns of data. Column A are codes, column B are the corresponding country names. I turned it into an associative array, ueln_country.

Now the task is to update HORSE table's column COUNTRY_OF_RESIDENCE. Horses have a column UELN where first three letters correspond to the codes in the Excel table.

I have to check if the code exists in the Excel table. If it does, I have to update HORSE.country_of_residence with a CLASSIFICATOR.code where CLASSIFICATOR.name = **the corresponding country in column B** andCLASSIFICATOR.dom_code = 'ISOCODE'`.

First try gets the error

PLS-00201: identifier 'UELN' must be declared

As I understood, it's because I can only use declared variables in PL/SQL statement.

declare
  type TBL_UELN_COUNTRY is table of varchar2(50) index by varchar2 (3);
  test                  TBL_UELN_COUNTRY;
  ueln_country          TBL_UELN_COUNTRY;

begin
  ueln_country('008') := 'ALBAANIA';
  ueln_country('010') := 'ANTARKTIS';
  ueln_country('011') := 'ANTARKTIS';
  ....

  update HORSE
  set COUNTRY_OF_RESIDENCE=
      when (...dummy_case...) then
        (select code from meta.classifcator 
         where dom_code = 'ISOCODE' 
         and name = ueln_country(substr(UELN, 1, 3)))
  where UELN is not null;
end;
/

Second try.

So because of the first error I tried to somehow declare the variables.

I knew it wouldn't work (ORA-01422: exact fetch returns more than requested number of rows) but made it to show where my idea is going:

declare
  type TBL_UELN_COUNTRY is table of varchar2(50) index by varchar2 (3);
  test                  TBL_UELN_COUNTRY;
  ueln_country          TBL_UELN_COUNTRY;

  v_ueln horse.UELN%TYPE;

begin

  select UELN into v_ueln from HORSE;

  ueln_country('008') := 'ALBAANIA';
  ueln_country('010') := 'ANTARKTIS';
  ueln_country('011') := 'ANTARKTIS';
  ....


  update HORSE
  set COUNTRY_OF_RESIDENCE=
      when (...dummy_case...) then
        (select code from meta.classifcator 
         where dom_code = 'ISOCODE'
         and name = ueln_country(substr(v_ueln, 1, 3)))
  where UELN is not null;
end;
/

So I want pick a value from associative array where the key = substr(specific_horse.UELN, 1, 3).

Searched through Google and Stack for hours and didn't find an answer.

---

The ugly and very slow working solution was just where I didn't make the associate array and made 400+ cases for every Excel table row in the form like when -key- then select code from meta.classificator where dom_code = 'ISOKOOD' and name = -value-

Answer 1

associative array can not be used in SQL. If you use expression like array(index) in SQL then, in fact, PL/SQL engine gets value by index and then result is bound into SQL engine before execution of the SQL statement.

More specifically

declare
  type TBL_UELN_COUNTRY is table of varchar2(50) index by varchar2 (3);
  test                  TBL_UELN_COUNTRY;
  dummy varchar2(30);
begin
  test('GBP') := 'UK';
  test('USD') := 'USA';

  select /*+ qwerty */ test('GBP')
  into dummy
  from dual;  
end;
/

If we check binds for a cursor we see that actual bind value has a type VARCHAR(128) - :B1. test('GBP') in PL/SQL code is passed as bind variable B1.

SQL> column sql_text format a50
SQL> select sbc.datatype_string, sql_text
  2    from v$sql s join v$sql_bind_capture sbc
  3      on s.sql_id = sbc.sql_id
  4   where lower(sql_text) not like '%v$sql%'
  5     and lower(sql_fulltext) like 'select %qwerty%';

DATATYPE_STRING SQL_TEXT
--------------- --------------------------------------------------
VARCHAR2(128)   SELECT /*+ qwerty */ :B1 FROM DUAL

SQL engine knows nothing about associative array and apparently it cannot pass and index value to array and get an element of the array back.

If you still want to use associative array to look-up some values you can declare package variable and a getter function (you may also want to implement the logic to handle a case when there is no element in array for a given index - otherwise you'll get run-time exception in such case).

create or replace package pkg as

  function GetCountry(idx in varchar2) return varchar2;

end pkg;
/
sho err

create or replace package body pkg as

  type TBL_UELN_COUNTRY is table of varchar2(50) index by varchar2 (3);

  test pkg.TBL_UELN_COUNTRY;

  function GetCountry(idx in varchar2) return varchar2 as
    begin return test(idx); end;

-- initializing
begin
  test('GBP') := 'UK';
  test('USD') := 'USA';
end pkg;
/
sho err

And finally

SQL> set serveroutput on
SQL> declare
  2    dummy varchar2(30);
  3  begin
  4    with t(idx) as (select 'GBP' from dual)
  5    select pkg.GetCountry(t.idx)
  6    into dummy
  7    from t;
  8    dbms_output.put_line(dummy);
  9  end;
 10  /
UK

PL/SQL procedure successfully completed.