create or replace function get_token(
the_list varchar2,
the_index number,
delim varchar2 := ','
)
return varchar2
is
start_pos number;
end_pos number;
begin
if the_index = 1 then
start_pos := 1;
else
start_pos := instr(the_list, delim, 1, the_index - 1);
if start_pos = 0 then
return null;
else
start_pos := start_pos + length(delim);
end if;
end if;

end_pos := instr(the_list, delim, start_pos, 1);

if end_pos = 0 then
return substr(the_list, start_pos);
else
return substr(the_list, start_pos, end_pos - start_pos);
end if;

end get_token;
/

select
get_token('foo,bar,baz',1), -- 'foo'
get_token('foo,bar,baz',3), -- 'baz'
--
get_token('a,,b',2), -- '' (null)
get_token('a,,b',3), -- 'b'
--
get_token('a|b|c',2,'|'), -- 'b'
get_token('a|b|c',4,'|') -- '' (null)
from
dual
/

Oracle logging message to a table.

create table log_test (message varchar2(255));
create table log_results (resultvalue number);


CREATE OR REPLACE PROCEDURE LOG_MESSAGE (MESSAGE_IN IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO LOG_TEST (MESSAGE) VALUES (MESSAGE_IN);
COMMIT;
END;
/

create or replace
PROCEDURE CREATE_ERROR (VALUE_IN IN NUMBER) IS
l_x NUMBER;
BEGIN
LOG_MESSAGE( 'Attempting to divide by ' || to_char(VALUE_IN) );
l_x := 10 / VALUE_IN;
INSERT INTO log_results (resultvalue) values (l_x);
LOG_MESSAGE( 'Finished to divide by ' || to_char(VALUE_IN) );
COMMIT;
END;
/

select * from log_test;

SQL> select * from log_test;

no rows selected

SQL> select * from log_results;

no rows selected

SQL> exec create_error(2);

PL/SQL procedure successfully completed.

SQL> exec create_error(1);

PL/SQL procedure successfully completed.

SQL> exec create_error(0);
BEGIN create_error(0); END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TPM.CREATE_ERROR", line 5
ORA-06512: at line 1


SQL> exec create_error(4);

PL/SQL procedure successfully completed.

SQL> select * from log_results;

RESULTVALUE
-----------
5
10
2.5

SQL> select * from log_test;

MESSAGE
--------------------------------------------------------------------------------
Attempting to divide by 2
Finished to divide by 2
Attempting to divide by 1
Finished to divide by 1
Attempting to divide by 0
Attempting to divide by 4
Finished to divide by 4

7 rows selected.