A place to store elegant code snippets
1. Check if a string is a valid DATE type in PL/SQL
Click here
DECLARE
v_date VARCHAR2 (20) := '01-02-2009';
BEGIN
DBMS_OUTPUT.put_line (TO_DATE (v_date, 'mm/dd/yyyy'));
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line (TO_DATE (v_date, 'mm-dd-yyyy'));
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line (TO_DATE (v_date, 'MON dd yyyy'));
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line (TO_DATE (v_date, 'mm-dd-yy'));
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line (TO_DATE (v_date, 'mm/dd/yy'));
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line (TO_DATE (v_date, 'dd-mm-yy'));
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_OUTPUT.put_line (TO_DATE (2439856 + v_date, 'j'));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (TO_DATE(NULL));
END;
END;
END;
END;
END;
END;
END;
2. Check if input is a NUMBER datatype in PL/SQL
Click here
DECLARE
p_data VARCHAR2 (20);
v_number NUMBER;
BEGIN
v_number := p_data;
DBMS_OUTPUT.PUT_LINE ('It is a number');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('It is not a number');
END;
3. Generate a Fibonacci sequence using recursive CTE
Click here
WITH sequencia_fibonacci(fibonacci_nro, proximo_nro) AS (
-- consulta âncora
SELECT 0, 1
UNION ALL
-- consulta recursiva
SELECT proximo_nro, fibonacci_nro = fibonacci_nro + proximo_nro
FROM sequencia_fibonacci
WHERE fibonacci_nro < 100
)
SELECT fibonacci_nro FROM sequencia_fibonacci;