Home » SQL & PL/SQL » SQL & PL/SQL » Constraint Error (Oracle 10q)
Constraint Error [message #310162] Mon, 31 March 2008 08:07 Go to next message
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 #310170 is a reply to message #310162] Mon, 31 March 2008 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
check_for_valid(dept,'Mgr')

What is dept? Where is it declared?

By the way, you have 2 different function names.
So:
1/ Always copy and paste what you executed
2/ Format it

Regards
Michel
Re: Constraint Error [message #310328 is a reply to message #310162] Mon, 31 March 2008 23:46 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
Dept is an OBJECT Type Declared in the Shown Code
Re: Constraint Error [message #310334 is a reply to message #310328] Tue, 01 April 2008 00:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So can you give an object type as parameter on procedure call?
Isn't a variable you have to give?

Regards
Michel
Re: Constraint Error [message #310335 is a reply to message #310328] Tue, 01 April 2008 00:12 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
yes. We need to pass the Object variable to the function .which is saying invalid column . can you help ?
Re: Constraint Error [message #310340 is a reply to message #310335] Tue, 01 April 2008 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Which object?

Regards
Michel
Re: Constraint Error [message #310347 is a reply to message #310162] Tue, 01 April 2008 00:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Constraint Error [message #310373 is a reply to message #310360] Tue, 01 April 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Constraint can't referenced another table, this is said in the documentation, please read it.

Regards
Michel
Re: Constraint Error [message #310388 is a reply to message #310360] Tue, 01 April 2008 01:29 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Nevermind; in Oracle, check constraints can't contain a "user-defined SQL function":
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_constraints.htm#sthref581

SQL> select deterministic from all_procedures where procedure_name = 'CHECK_FOR_VALID';
NO
NO


Annoyingly enough, the validation operations that the function performs should be deterministic.

Unless someone has a better idea, I guess I'll have to use a trigger for this.
Re: Constraint Error [message #310408 is a reply to message #310388] Tue, 01 April 2008 02:47 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Huh?

Do you have two different logins to this site?
Previous Topic: Issues on financial year (multiple merges)
Next Topic: Enter symbol is coming while inserting rows table into file
Goto Forum:
  


Current Time: Sun Feb 16 00:40:32 CST 2025