CREATE OR REPLACE PROCEDURE parse_string(
    input_string IN VARCHAR2,
    separators IN SYS.ODCIVARCHAR2LIST,
    out_fields OUT SYS.ODCIVARCHAR2LIST
)
IS
    separator_index PLS_INTEGER;
    start_index PLS_INTEGER := 1;
    field_length PLS_INTEGER;
    i PLS_INTEGER;
BEGIN
    FOR i IN 1..6 LOOP
        separator_index := INSTR(input_string, separators(i), start_index);
        
        IF separator_index = 0 THEN
            field_length := LENGTH(input_string) - start_index + 1;
        ELSE 
            field_length := separator_index - start_index;
        END IF;
        
        out_fields(i) := SUBSTR(input_string, start_index, field_length);
        
        IF separator_index = 0 THEN
            EXIT;
        END IF;
        
        start_index := separator_index + LENGTH(separators(i));
    END LOOP;
END;
/


DECLARE
    input_string VARCHAR2(1000) := '/BOOK/HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH/JJJJJJJJJJJJJJJJJJJJ//KKKKKKKKKKKKKKK/BOOK/LLLLLLLLLLLL';
    separators SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('//', '/', 'BOOK', 'EARLY', 'ASD', 'SHP'); 
    out_fields SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
    i PLS_INTEGER;
BEGIN
    parse_string(input_string, separators, out_fields);
    
    FOR i IN 1..out_fields.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Field ' || i || ': ' || out_fields(i));
    END LOOP;
END;
/

PL/SQL Online Compiler

Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.

About PL/SQL

PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.

Syntax help

Following is the syntax structure for the PL/SQL code blocks

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

Example

DECLARE 
   message  varchar2(100):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Named procedures

CREATE OR REPLACE FUNCTION 
hello_user
   (user_name IN VARCHAR2) 
    RETURN VARCHAR2
IS
BEGIN
   RETURN 'Hello ' || user_name;
END hello_user;
/

BEGIN
   dbms_output.put_line(hello_user('Peter'));
END;
/

Exception handling

BEGIN
  DBMS_OUTPUT.put_line (1/0);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;