Constraint Error [message #310162] |
Mon, 31 March 2008 08:07  |
rangan.s
Messages: 75 Registered: February 2008 Location: chennai
|
Member |
|
|
create or replace type dept AS OBJECT (dept_name varchar2(180))
/
create table emp
(
id number,
deptname dept
);
/
create function check_for_valid_id(name dept,designame varchar2)
return varchar2
as
res varchar2(3);
begin
if(name is null) then
res := 'NO';
else
res := 'YES';
end if;
return NVL(res,'NO');
end;
/
alter table emp add constraint chk_emp_dept check(check_for_valid(dept,'Mgr') = 'YES');
ORA-00904: invalid column name
|
|
|
|
|
|
|
|
Re: Constraint Error [message #310347 is a reply to message #310162] |
Tue, 01 April 2008 00:31   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
I am really amazed. You succeeded to create object type, still have no idea how to use it. In this case, reading documentation really helps. It is available eg. online on http://tahiti.oracle.com/.
You shall have a look into PL/SQL User's Guide and Reference, especially the Chapter 12 Using PL/SQL With Object Types. There are many code examples there too.
Just be aware, there is a difference between NULL and DEPT( TO_CHAR( NULL ) ). The CHECK_FOR_VALID_ID function you posted will return 'NO' only for the first case.
[Edit: corrected typo]
[Updated on: Tue, 01 April 2008 00:31] Report message to a moderator
|
|
|
Re: Constraint Error [message #310360 is a reply to message #310340] |
Tue, 01 April 2008 00:51   |
rangan.s
Messages: 75 Registered: February 2008 Location: chennai
|
Member |
|
|
Michel, we tried to pass the table column deptname along with its typename, it says the same, invalid column name.
Could you tell whether it is possible or not?
alter table emp_info add constraint chk_emp_dept check(check_for_valid(deptname.dept,'Mgr') = 'YES')
ORA-00904: invalid column name
|
|
|
|
|
|