Splitting a string into variables in Oracle based on the separator

DECLARE
  l_string VARCHAR2(240) := 'ram:mano:har:reddy';
  l_var1   VARCHAR2(240);
  l_var2   VARCHAR2(240);
  l_var3   VARCHAR2(240);
  l_var4   VARCHAR2(240);
BEGIN
  SELECT 
  trim('"' FROM regexp_substr(l_string,'" "|[^:]+',1,1)) Col1,
  trim('"' FROM regexp_substr(l_string,'" "|[^:]+',1,2)) Col2,
  trim('"' FROM regexp_substr(l_string,'" "|[^:]+',1,3)) Col3,
  trim('"' FROM regexp_substr(l_string,'" "|[^:]+',1,4)) Col4
  INTO
  l_var1 ,
  l_var2 ,
  l_var3 ,
  l_var4
 FROM dual ;
  dbms_output.put_line('Var1 : ' ||l_var1);
  dbms_output.put_line('Var2 : ' ||l_var2);
  dbms_output.put_line('Var3 : ' ||l_var3);
  dbms_output.put_line('Var4 : ' ||l_var4);
END;


note:
REGEXP_SUBSTR returns string of data type VARCHAR2 or CLOB. REGEXP_SUBSTR uses regular expressions to specify the start and end of the returned string. The simplest format for this function is:
REGEXP_SUBSTR(source_string, pattern_to_find)

The general format for the function with all the options is:

REGEXP_SUBSTR(source-string, 
               pattern-to-find [, 
               position, 
               occurrence, 
               match-parameter])

source_string:the source string
pattern-to-find:the pattern to search for.
position:where to start.
occurrence:occurrence of the pattern-to-find in the source-string you want to search for.
match-parameter:for further customizing.

"i" in match-parameter can be used for caseinsensitive matching
"c" in match-parameter can be used for casesensitive matching
"n" in match-parameter allows the period to match the new line character
"m" in match-parameter allows for more than one line in source-string