2021- Parameter Data Type Checks

by

This post a quick eplanation of how Oracle validates datatype when compiling and executing code.

Parameter Datatypes

Parameter datatype are very important because they dictates the behavior of the PL/SQL compiler. For example the get_pname function has 2 parameters:

  • p_os – the number of the operating system – the datatype is number so it will not accept anything else.
  • p_mrn – the patients mrn – the datatype varchar2 so it will accepts strings and/or numbers
FUNCTION get_pname ( p_os NUMBER,
                      p_mrn VARCHAR2 )
         RETURN VARCHAR2 IS
  v_ret_val VARCHAR2(30);
BEGIN
  SELECT ( SELECT patient_name
             FROM patient
            WHERE patient_id = pmrn.patient_id )
    INTO v_ret_val
    FROM patient_mrn pmrn
   WHERE ordering_system = p_os
     AND patient_mrn = p_mrn;
  RETURN(v_ret_val);
EXCEPTION
  WHEN OTHERS THEN
    RETURN(NULL);
END;

And here are some example calls specifying the correct types of data.

-- First call finds Patient 1
-- Second call finds nothing and returns NULL
BEGIN
  DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname(1,'MRN123'));
  DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname(1,'NOTTHERE'));
END;
Patient 1

What if we try passing incorrect datatypes such as a date? The PL/SQL compile recognizes that the NOTANUMBER text cannot be converted to a number so raise the error.

-- raises ORA-06502: PL/SQL: numeric or value error: character to
-- number conversion error
BEGIN
  DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname('NOTANUMBER','MRN123'));
END;

One way to ensure the parameter is the correct datatype is using the VALIDATE_CONVERSION function. It returns 1 if the defined conversion is possible. The first call below cannot translate NOTANUMBER to a NUMBER so it display an incorrect datatype message. The second call converts 1 to a number and then calls the get_pname function.

BEGIN
  IF VALIDATE_CONVERSION('NOTANUMBER' AS NUMBER) = 1 THEN
    DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname('NOTANUMBER','MRN123'));
  ELSE
    DBMS_OUTPUT.PUT_LINE('Please pass correct datatypes');
  END IF;
  IF VALIDATE_CONVERSION(1 AS NUMBER) = 1 THEN
    DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname(1,'MRN123'));
  ELSE
    DBMS_OUTPUT.PUT_LINE('Please pass correct datatypes');
  END IF;
END;
Please pass correct datatypes
Patient 1

The compiler is pretty good with DATE parameters as well. For example it notices the SYSDATE function returns a DATE value in thus call and raises PLS-00306: wrong number or types of arguments in call to ‘GET_PNAME’

BEGIN
  DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname(SYSDATE,SYSDATE));
END;

It even notices local variables. This call fails.

DECLARE
  v_date DATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname(v_date,v_date));
END;

It wont even allows a procedure to be created if the parameters types mismatch.

This raises – PLS-00306: wrong number or types of arguments in call to ‘GET_PNAME’

CREATE OR REPLACE PROCEDURE x AS
  v_date DATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname(v_date,v_date));
END;

It’s very thorough. Even traps code that will never exist!

CREATE OR REPLACE PROCEDURE x AS
  v_date DATE;
BEGIN
  IF 1 = 2 THEN
    DBMS_OUTPUT.PUT_LINE(patient_pkg.get_pname(v_date,v_date));
  END IF;
END;

Thanks for reading!

Leave a Comment