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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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; |