Q:

PL/SQL Why my function return NULL value instead of a value in 'HH24:MI:SS' format?

I'm trying to code a PL/SQL function to receive by parameter a number in VARCHAR2 type and return a hour in format 'HH24:MI:SS'. eg: If I send by parameter '6', the function will return 06:00:00. The problem is the function returned NULL value but If I write the same process in a simple PL/SQL block it works fine. Do you have any idea?. Thanks :)
CREATE OR REPLACE FUNCTION format_hour_test (
    p_hour VARCHAR2)
RETURN VARCHAR2
IS
    v_hour_time TIMESTAMP;
    v_hour_char VARCHAR2(50);
BEGIN
    IF
        REGEXP_LIKE(p_hour, '(0-9)') THEN
            v_hour_time := TO_TIMESTAMP(p_hour, 'HH24');
    END IF;

    v_hour_char := TO_CHAR(v_hour_time, 'HH24:MI:SS');

    RETURN v_hour_char;

END format_hour_test;
/
SHOW ERRORS;

When I write a anonymus block with the same process this works fine.
SET SERVEROUTPUT ON

DECLARE
    v_hour      VARCHAR2(20) := '6';
    v_hour_time TIMESTAMP := TO_TIMESTAMP(v_hour, 'HH24');
    v_result    VARCHAR2(20) := TO_CHAR(v_hour_time, 'HH24:MI:SS');
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_result);
END;

A:

You have the wrong type of brackets in your regular expression; it should be:
IF REGEXP_LIKE(p_hour, '[0-9]') THEN

You used grouping parentheses, insteaf of matching/class brackets. You would get a match if you passed in the string '0-9', which wouldn't then be converted to a date/time.
Read more.

