Get values from Comma/CSV/TAB delimiter string - Oracle 11g

CREATE OR REPLACE FUNCTION get_field (p_text       IN VARCHAR2,
                    p_position   IN NUMBER,
                    p_sep        IN VARCHAR2)  RETURN VARCHAR2
IS

   l_sep         VARCHAR2 (1);
   l_output      VARCHAR2 (240);

BEGIN

   IF UPPER (p_sep) = 'TAB'
   THEN
      l_sep := CHR (9);
   ELSIF UPPER (p_sep) = 'CSV'
   THEN
      l_sep := ',';
   END IF;

   l_output := TRIM ((REPLACE (REPLACE (SUBSTR(rtrim( regexp_substr( p_text || l_sep, '.*?' || l_sep, 1, p_position ), l_sep ),1,240), CHR (10), ''), CHR (13), '')));
   RETURN (l_output);


END get_field;

Output 

TAB delimiter string

SQL> SELECT GET_FIELD('ABC CHIDAM RAM DIV ',1,'TAB') FROM DUAL;

GET_FIELD('ABCCHIDAMRAMDIV',1,

--------------------------------------------------------------------------------

ABC

SQL> SELECT GET_FIELD('ABC CHIDAM RAM DIV ',2,'TAB') FROM DUAL;

GET_FIELD('ABCCHIDAMRAMDIV',2,

--------------------------------------------------------------------------------

CHIDAM

SQL> SELECT GET_FIELD('ABC CHIDAM RAM DIV ',3,'TAB') FROM DUAL;

GET_FIELD('ABCCHIDAMRAMDIV',3,

--------------------------------------------------------------------------------

RAM

SQL> SELECT GET_FIELD('ABC CHIDAM RAM DIV ',4,'TAB') FROM DUAL;

GET_FIELD('ABCCHIDAMRAMDIV',4,

--------------------------------------------------------------------------------

SQL> SELECT GET_FIELD('ABC CHIDAM RAM DIV ',5,'TAB') FROM DUAL;

GET_FIELD('ABCCHIDAMRAMDIV',5,

--------------------------------------------------------------------------------

SQL> SELECT GET_FIELD('ABC CHIDAM RAM DIV ',6,'TAB') FROM DUAL;

GET_FIELD('ABCCHIDAMRAMDIV',6,

--------------------------------------------------------------------------------

DIV

CSV/Comma fields

SQL> SELECT GET_FIELD('ABC,CHIDAM,RAM,,,DIV',1,'CSV') FROM DUAL;

GET_FIELD('ABC,CHIDAM,RAM,,,DI

--------------------------------------------------------------------------------

ABC

SQL> SELECT GET_FIELD('ABC,CHIDAM,RAM,,,DIV',2,'CSV') FROM DUAL;

GET_FIELD('ABC,CHIDAM,RAM,,,DI

--------------------------------------------------------------------------------

CHIDAM

SQL> SELECT GET_FIELD('ABC,CHIDAM,RAM,,,DIV',5,'CSV') FROM DUAL;

GET_FIELD('ABC,CHIDAM,RAM,,,DI

--------------------------------------------------------------------------------

SQL> SELECT GET_FIELD('ABC,CHIDAM,RAM,,,DIV',6,'CSV') FROM DUAL;

GET_FIELD('ABC,CHIDAM,RAM,,,DI

--------------------------------------------------------------------------------

DIV

Oracle 11g - REGEXP_SUBSTR extract comma delimiter string to fields

select REGEXP_SUBSTR('CHIDAM,,20-Feb-17,1-Feb-19', '([^,]*)(,|$)', 1, 1,NULL,1) f1,

REGEXP_SUBSTR('CHIDAM,,20-Feb-17,1-Feb-19', '([^,]*)(,|$)', 1, 2,NULL,1) f2,

REGEXP_SUBSTR('CHIDAM,,20-Feb-17,1-Feb-19', '([^,]*)(,|$)', 1, 3,NULL,1) f3,

REGEXP_SUBSTR('CHIDAM,,20-Feb-17,1-Feb-19', '([^,]*)(,|$)', 1, 4,NULL,1) f4

from dual

Output

F1	F2	F3	F4
CHIDAM		20-Feb-17	1-Feb-19

WebADI - Delete Integrator and Interface through API

Deleting interface through application and trying to create the same name, It will not allow to recreate the same interface name, for that we need to delete the interface through API

-- Get an Interface/Integrator Details

SELECT biv.application_id

       ,biv.integrator_code

       ,biv.user_name

       ,bib.interface_code

   FROM bne_integrators_vl biv

       ,bne_interfaces_b   bib

  WHERE upper(user_name) like '%XXAK%'

    AND bib.integrator_code = biv.integrator_code ;

--Delete an Intergrator

DECLARE

   vn_number number:=0;

BEGIN

   vn_number:= bne_integrator_utils.delete_integrator

               (p_application_id => 20003,

                p_integrator_code => 'XXAKTESTUPADI_XINTG');

               

   dbms_output.put_line(' ADI Deleted : '||vn_number);

   COMMIT;

   --

EXCEPTION  

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END

/

--Delete an Interface

DECLARE

   vn_number   NUMBER;

BEGIN

   vn_number := bne_integrator_utils.delete_interface

                (p_application_id => 20003,

        p_interface_code  => 'XXAKTESTUPADI_XINTG_INTF1');

                

   DBMS_OUTPUT.put_line ('ADI Interface Deleted '||vn_number);

   COMMIT;

   --

EXCEPTION  

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Error: '||sqlerrm);

      ROLLBACK;

END;

/

Concurrent program attached request Group - Query

SELECT FRT.RESPONSIBILITY_NAME,

       FRG.REQUEST_GROUP_NAME,

       FRGU.REQUEST_UNIT_TYPE,

       FRGU.REQUEST_UNIT_ID,

       FCPT.USER_CONCURRENT_PROGRAM_NAME

  FROM FND_RESPONSIBILITY         FR,

       FND_RESPONSIBILITY_TL      FRT,

       FND_REQUEST_GROUPS         FRG,

       FND_REQUEST_GROUP_UNITS    FRGU,

       FND_CONCURRENT_PROGRAMS_TL FCPT

 WHERE FRT.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID

   AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID

   AND FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID

   AND FCPT.CONCURRENT_PROGRAM_ID = FRGU.REQUEST_UNIT_ID

   AND FRT.LANGUAGE = USERENV('LANG')

   AND FCPT.LANGUAGE = USERENV('LANG')

   AND FCPT.USER_CONCURRENT_PROGRAM_NAME =

       'PO Changes'

 ORDER BY 1, 2, 3, 4