create or replace PACKAGE           test_pkg
IS
  TYPE t_array
    IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
    
  FUNCTION SPLIT(
    p_in_string clob,---changed to varchar2 means one less value will be returned
    p_delim     VARCHAR2)
  RETURN T_ARRAY;
end;  

CREATE OR REPLACE 
PACKAGE BODY            test_pkg as 
 FUNCTION Split(p_in_string clob,
                 p_delim     VARCHAR2)
  RETURN T_ARRAY
  IS
    i       NUMBER := 0;
    pos     NUMBER := 0;
--    lv_str  VARCHAR2(2000) := p_in_string;
lv_str  clob := p_in_string;
    strings T_ARRAY;
  BEGIN
      -- determine first chuck of string
      pos := Instr(lv_str, p_delim, 1, 1);

      --if only string is there without any delimeter then it is returning that string itself.
      if pos=0 and lv_str is not null then
        Strings(1):=lv_str;
      end if;

      WHILE ( pos != 0 ) LOOP
  
          i := i + 1;

          Strings(i) := Substr(lv_str, 1, pos - 1);

          lv_str := Substr(lv_str, pos + 1, Length(lv_str));

          pos := Instr(lv_str, p_delim, 1, 1);

          IF pos = 0 THEN
            Strings(i + 1) := lv_str;
          END IF;
      END LOOP;

      RETURN strings;
  END SPLIT;
  end test_pkg;
  
  
--testing after changing clob (3 count) /varchar2(2 count) to  p_in_string of split function
  DECLARE
t_priority  test_pkg.t_array;
BEGIN
t_priority   := test_pkg.SPLIT('~1~', '~');
dbms_output.put_line(t_priority.count);
END;

Declare
--Lv_Str Clob:='99~';
Lv_Str Varchar2(100):='99~';
Pos number:=0;
Begin   
 Dbms_Output.Put_Line('length:'||Length(lv_str));
 Lv_Str := Substr(Lv_Str, 3 + 1, Length(Lv_Str));
  Dbms_Output.Put_Line('str:'||Lv_Str||'vcvc');
 Pos := Instr(Lv_Str, '~', 1, 1);
dbms_output.put_line('pos:'||Pos);
end;

--output
length:3
str:vcvc
pos:



Declare
Lv_Str Clob:='99~';
--Lv_Str Varchar2(100):='99~';
Pos number:=0;
Begin   
 Dbms_Output.Put_Line('length:'||Length(Lv_Str));
 Lv_Str :=Substr(Lv_Str, 3 + 1, Length(Lv_Str));
  Dbms_Output.Put_Line('str:'||Lv_Str||'vcvc');
 Pos := Instr(Lv_Str, '~', 1, 1);
dbms_output.put_line('pos:'||Pos);
End;
--output
length:3
Str:Vcvc
pos:0

SQL> Declare
  2  Lv_Str Clob:='99~';
  3  --Lv_Str Varchar2(100):='99~';
  4  Pos number:=0;
  5  Begin
  6   Dbms_Output.Put_Line('length:'||Length(Lv_Str));
  7   Lv_Str :=Substr(Lv_Str, 3 + 1, Length(Lv_Str));
  8    Dbms_Output.Put_Line('str:'||Lv_Str||'vcvc');
  9    Dbms_Output.Put_Line('length after substr:'||Length(Lv_Str));
 10     Pos := Instr(Lv_Str, '~', 1, 1);
 11     dbms_output.put_line('pos after substr:'||Pos);
 12    lv_str := NULL;
 13    Dbms_Output.Put_Line('length after set null:'||Length(Lv_Str));
 14   Pos := Instr(Lv_Str, '~', 1, 1);
 15  dbms_output.put_line('pos after set null:'||Pos);
 16  End;
 17  /
 
length:3
str:vcvc
length after substr:0
pos after substr:0
length after set null:
pos after set null:
 
PL/SQL procedure successfully completed
 
SQL> 

 Declare
    Lv_Str Clob:='99~';
    --Lv_Str Varchar2(100):='99~';
    Pos number:=0;
    Begin
     Dbms_Output.Put_Line('length:'||Length(Lv_Str));
     Lv_Str :=Substr(Lv_Str, 3 + 1, LENGTH(Lv_Str));
      Dbms_Output.Put_Line( ASCIISTR(Lv_Str) );
       Dbms_Output.Put_Line( ASCII(Lv_Str) );
      Dbms_Output.Put_Line('str:'||ASCIISTR(Lv_Str)||'vcvc');
      Dbms_Output.Put_Line('length after substr:'||Length(Lv_Str));
      Pos := Instr(Lv_Str, '~', 1, 1);
      dbms_output.put_line('pos after substr:'||Pos);
     lv_str := NULL;
     Dbms_Output.Put_Line('length after set null:'||Length(Lv_Str));
    Pos := Instr(Lv_Str, '~', 1, 1);
   dbms_output.put_line('pos after set null:'||Pos);
   End;
 
 --output
length:3


str:vcvc
length after substr:0
pos after substr:0
length after set null:
pos after set null:

  1   Declare
  2      Lv_Str Clob:='99~';
  3      --Lv_Str Varchar2(100):='99~';
  4      Pos number:=0;
  5      Begin
  6       Dbms_Output.Put_Line('length1:'||Length(Lv_Str));
  7       Lv_Str :=Substr(Lv_Str, 3 + 1, LENGTH(Lv_Str));
  8       Dbms_Output.Put_Line('length2:'||Length(Lv_Str));
  9        Dbms_Output.Put_Line( ASCIISTR(Lv_Str) );
 10         Dbms_Output.Put_Line( ASCII(Lv_Str) );
 11        Dbms_Output.Put_Line('str:'||ASCIISTR(Lv_Str)||'vcvc');
 12        Dbms_Output.Put_Line('length after substr:'||Length(Lv_Str));
 13        Pos := Instr(Lv_Str, '~', 1, 1);
 14        dbms_output.put_line('pos after substr:'||Pos);
 15       lv_str := NULL;
 16       Dbms_Output.Put_Line('length after set null:'||Length(Lv_Str));
 17      Pos := Instr(Lv_Str, '~', 1, 1);
 18     dbms_output.put_line('pos after set null:'||Pos);
 19*    End;
SQL> /
length1:3
length2:0
str:vcvc
length after substr:0
pos after substr:0
length after set null:
pos after set null:

PL/SQL procedure successfully completed.

SQL>