Substr Instr to get first middle and last name

select
substr(:party_name,1,
instr(:party_name,'|',1,1)-1)FNAME,
SUBSTR(:party_name, INSTR(:party_name,'|', 1, 1)+1,
INSTR(:party_name,'|',1,2)-INSTR(:party_name,'|',1,1)-1)MNAME,
substr(:party_name,instr(:party_name,'|',-1,1)+1)LNAME
from dual;

select :party_name,

  TRIM(SUBSTR(:party_name, 1, INSTR(:party_name, ' ', -1,1))) fname,

    SUBSTR(:party_name, INSTR(:party_name, ' ', -1)+1) lname

  from dual;

/* Formatted on 7/10/2023 3:19:47 PM (QP5 v5.294) */

SET SERVEROUTPUT ON

/

DECLARE

   --i number

   l_count_line_id   NUMBER;

   l_party_name      VARCHAR2 (100);

   l_name            VARCHAR2 (100);

BEGIN

   l_count_line_id := 4;

   l_party_name := '012,345,678,910';

   DBMS_OUTPUT.put_line ('l_party_name   :   ' || l_party_name);

   FOR i IN 1 .. l_count_line_id

   LOOP

      l_name :=

         REGEXP_SUBSTR (l_party_name

                      , '[^,]+'

                      , 1

                      , i);

      DBMS_OUTPUT.put_line ('l_name   :   ' || l_name);

   END LOOP;

END;

update and insert using for all bulk collect

DECLARE
    CURSOR rec_cur IS
     SELECT row_id, x_sfdc_account_id
             FROM s_org_ext
             where x_sfdc_account_id is not null
--             and row_id  in ('1+12N+1177',
--'1+12N+1182',
--'1+12N+1185')
;

    TYPE vc1_tab_t IS TABLE OF VARCHAR2(256);
    TYPE vc2_tab_t IS TABLE OF VARCHAR2(256);

    pk_tab vc1_tab_t;
--    fk_tab vc1_tab_t;
    fill_tab VC2_TAB_T;
BEGIN
    OPEN rec_cur;
    LOOP
        FETCH rec_cur BULK COLLECT INTO pk_tab,  fill_tab LIMIT 1000;
        EXIT WHEN pk_tab.COUNT() = 0;

        FORALL i IN pk_tab.FIRST .. pk_tab.LAST
           UPDATE md_parties
            SET    attribute_osr = fill_tab(i)
            WHERE  orig_system_reference = pk_tab(i)
            and source_system =5;
            commit;
    END LOOP;
    CLOSE rec_cur;
END;



DECLARE
    CURSOR rec_cur IS
       SELECT *
     FROM md_party_asset_link_Bkp;

    TYPE vc1_tab_t IS TABLE OF rec_cur%rowtype;

    pk_tab vc1_tab_t;
--    fk_tab vc1_tab_t;
BEGIN
    OPEN rec_cur;
    LOOP
        FETCH rec_cur BULK COLLECT INTO pk_tab LIMIT 100000;
        EXIT WHEN pk_tab.COUNT() = 0;

        FORALL i IN pk_tab.FIRST .. pk_tab.LAST
           INSERT INTO md_party_asset_link
           VALUES pk_tab (i);
            commit;
    END LOOP;
    CLOSE rec_cur;
END;


/* Formatted on 9/13/2016 3:18:21 PM (QP5 v5.185.11230.41888) */
DECLARE
   TYPE prod_tab IS TABLE OF md_party_asset_link_Bkp%ROWTYPE;

   products_tab   prod_tab := prod_tab ();
   start_time     NUMBER;
   end_time       NUMBER;
BEGIN
   -- Populate a collection - 100000 rows
   SELECT *
     BULK COLLECT INTO products_tab
     FROM md_party_asset_link_Bkp;

   Start_time := DBMS_UTILITY.get_time;

   FORALL i IN products_tab.FIRST .. products_tab.LAST
      INSERT INTO md_party_asset_link
           VALUES products_tab (i);

   end_time := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.PUT_LINE ('Bulk Insert: ' || TO_CHAR (end_time - start_time));
   COMMIT;
END;