1

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.

J. zhao
  • 43
  • 1
  • 7

1 Answers1

0

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;
/
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • Hi GurV, thanks for your reply. I am a new-hand and I wrote the UDF after reading some previous questions and answers. Did not know that was TSQL. Sorry. I tried your advise, but oracle live sql kept telling me "Errors: FUNCTION CHECKPOS PLS-00103: Encountered the symbol "ALTER" ". Any ideas? – J. zhao Feb 13 '17 at 21:06
  • I tested it on livesql. Did you add the forward slash at the end of the function? – Gurwinder Singh Feb 13 '17 at 21:09
  • My fault. Thank you! After I add the forward slash, the function worked. However, the check constraint statement "ALTER TABLE info ADD CONSTRAINT info_nick_ck CHECK (checkPos(empno) * length(nick) <16);" gives an error saying "ORA-00904: "CHECKPOS": invalid identifier ". Why? Thank you so much for your help! – J. zhao Feb 13 '17 at 21:18
  • Regarding that visiting this link - http://stackoverflow.com/questions/16778948/check-constraint-calling-a-function-oracle-sql-developer – Gurwinder Singh Feb 13 '17 at 21:24