CODEWITHSUNDEEP
oracle-databasevalidationtypesintegerprocedure
Ish Bhatt
Ish Bhatt

Reputation: 31

how to validate integer datatype in oracle procedure

I have a parameter with integer data type in a Oracle procedure which produces number of rows mentioned in the parameter into a table.

I want to validate the parameter with its data type. that means, if the parameter value is 5.0 then it creates 5 rows and if the value is 5.2 then it produces error. How do I create this logic?

Upvotes: 1

Views: 1529

Answers (1)

Jon Heller
Jon Heller

Reputation: 36902

Oddly, PL/SQL does not enforce INTEGER parameters. I would expect Oracle to either implicitly convert the data or throw an error if 5.2 was passed to an INTEGER parameter. Looks like you'll need to add your own validation:

create or replace procedure test_procedure(a integer) is
begin
    if a is not null and a <> trunc(a) then
        raise_application_error(-20000, 'Parameter must be an integer');
    end if;
end;
/

--Works
begin
    test_procedure(5.0);
end;
/

--Fails with "ORA-20000: Parameter must be an integer".
begin
    test_procedure(5.2);
end;
/

Upvotes: 2

Related Questions