How To Loop over a Node List

Attune node lists (formerly known as server groups) store data in comma-separated values (CSV) format. This example loops over the replicationServers node list and updates the HOST_DETAILS table based on the CSV text values replicationServerHostIds and replicationServerTypeList.

DELETE FROM HOST_DETAILS;

set serveroutput on
DECLARE
  hosts VARCHAR2(200) := '{replicationServers.serverHostnames}';
  host_ids VARCHAR2(100) := '{replicationServerHostIds}';
  host_types VARCHAR2(100) := '{replicationServerTypeList}';
BEGIN
  FOR row IN
  (SELECT trim(regexp_substr(hosts, '[^,]+', 1, LEVEL)) host,
          trim(regexp_substr(host_ids, '[^,]+', 1, LEVEL)) host_id,
          trim(regexp_substr(host_types, '[^,]+', 1, LEVEL)) host_type,
          LEVEL priority

   FROM dual
    CONNECT BY LEVEL <= regexp_count(hosts, ',')+1
  )
  LOOP
    dbms_output.put_line('Adding server ' || row.host
                        || ', id=' || row.host_id
                        || ', type=' || row.host_type
                        || ', priority=' || row.priority);
    EXECUTE IMMEDIATE
       'Insert into ENMAC.HOST_DETAILS ('
           || 'HOST_NAME,HOST_PRIORITY,HOST_ID,HOST_LAST_TRANSACTION,HOST_STATUS,'
           || 'HOST_TYPE,HOST_NETWORK,HOST_LAST_ERROR,HOST_GROUP,HOST_SEND_TXNS,HOST_RECV_TXNS,HOST_THIS_SERVER,'
           || 'HOST_LAST_REQUEST_ID,HOST_TRANSACTION_TIME,LAST_SEND_TRANSACTION_SERIAL,HOST_HALT,'
           || 'HOST_ROLL_TRANSACTION_ID'
           || ')'
           || ' VALUES (''' || row.host || ''',' || row.priority || ',''' || row.host_id || ''''
           || ',0,1, '|| row.host_type ||' , ''Default'',0,null,''Y'',''Y'',null,null,null,null,null,null)';
   END LOOP;
 END;