CODEWITHSUNDEEP
oracle-databaseplsql
Afraz Afaq
Afraz Afaq

Reputation: 117

Warning: Function created with compilation errors

im having a error im so frustrate right now. I think due to this error im also having another error of cannot reference other columns while adding a check constraint in the table. I have to use this function in check constraint to compare with end time.

  1  create or replace function timing(dat in date, bran in varchar2(30), audi in number)
  2  return number is time number
  3  begin
  4  select s_end into time from checking
  5  where s_date=dat and branch=bran and a_id = audi;
  6  return time;
  7* end timing
SQL> /

and my table is 

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 S_ID                                      NOT NULL NUMBER
 M_ID                                               NUMBER
 A_ID                                               NUMBER
 S_DATE                                             DATE
 S_START                                            NUMBER
 S_END                                              NUMBER
 BRANCH                                             VARCHAR2(30)

The error is:

1/46 PLS-00103: Encountered the symbol "(" when expecting following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. 
3/1 PLS-00103: Encountered the symbol "BEGIN" when expecting the following: := . ( @ % ; not null range default character 
The symbol ";" was substituted for "BEGIN" to continue. 
7/10 PLS-00103: Encountered the symbol "end-of-file" when expecting

Upvotes: 0

Views: 676

Answers (2)

Belayer
Belayer

Reputation: 14861

You have posted this same question in a slightly different form. I'll answer here in slightly different form. A check constraint cannot reference a user defined function. Oracle does not permit it so your contention "have to use this function in check constraint" cannot be!

Upvotes: 2

Randy
Randy

Reputation: 16677

try like this

create or replace function timing(dat in date, bran in varchar2, audi in number)
return number 
is 
time number;
begin
   select s_end into time 
   from checking
   where s_date=dat and branch=bran 
   and a_id = audi;
return time;
end :
/

Upvotes: 0

Related Questions