Oracle SQL add check constraint at multiple table level

Question

I have two tables as below.

create table emp (empno varchar2(5), position varchar2(5));

create table info (empno varchar2(5), nick varchar2(20));

empno is the primary key for both tables and empno is also a foreign key in info, with parent table emp.

Now I want to add a check constraint so that except for the gm or catcher( value in column position), the nick should not exceed 16 letters.

I tried the UDF method as below.

create function checkPos (@empno varchar2(5))
returns bit
as
begin
declare @par bit

select @par = CASE WHEN pos = 'gm' or pos = 'catcher'
THEN 0 ELSE 1 END
FROM emp
WHERE empno = @empno

RETURN(@par)
END

ALTER TABLE info ADD CONSTRAINT info_nick_ck CHECK (checkPos(empno) * length(nick) <16);

However, it does not work in oracle live sql.

Please help. Thank you in advance.

Answer 1

Your code looks like TSQL code.

Try this function in Oracle:

create function checkPos (p_empno varchar2)
return number
as
    v_par number := 0;
begin
    select case 
            when pos = 'gm'
                or pos = 'catcher'
                then 0
            else 1
            end into v_par
    from emp
    where empno = p_empno;

    return v_par;
exception
    when no_data_found then
        dbms_output.put_line('No data found');
        -- Do something about it
end;
/