Home » SQL & PL/SQL » SQL & PL/SQL » Question (oracle 9)
Question [message #401273] Sun, 03 May 2009 15:15 Go to next message
bigredcu29
Messages: 6
Registered: May 2009
Junior Member
Hey,

I'm trying to figure out how to simply check and see if a given item is in a table, and if its not, then raise an exception.

for example i have a table employees and each employee has a primary key employID. I need to write a procedure where employID is given as a parameter and if the one given isnt in the table then raise an exception.

Thanks!
Re: Question [message #401274 is a reply to message #401273] Sun, 03 May 2009 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/


When all else fails, you should Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

Many fine coding examples can be found at http://asktom.oracle.com
Re: Question [message #401277 is a reply to message #401274] Sun, 03 May 2009 17:21 Go to previous messageGo to next message
bigredcu29
Messages: 6
Registered: May 2009
Junior Member
Sorry,

could you tell me what else you need to help you specifically?
Re: Question [message #401278 is a reply to message #401273] Sun, 03 May 2009 17:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results
Re: Question [message #401279 is a reply to message #401273] Sun, 03 May 2009 17:47 Go to previous messageGo to next message
bigredcu29
Messages: 6
Registered: May 2009
Junior Member
ok, heres a table students

create table students (sid char(4) primary key check (sid like 'S%'), sname varchar2(20));

and some data:

insert into students values ('S001', 'Scott');
insert into students values ('S002', 'Frank');
insert into students values ('S003', 'Bill');
insert into students values ('S004', 'Sam');
insert into students values ('S005', 'Todd');
insert into students values ('S006', 'Brendan');
insert into students values ('S007', 'Mike');

so i need to write a procedure, get_sname(t_sid in students.sid%type) for example, and if the sid given by the user isn't an sid in the table, i need to raise a user created exception.

i appreciate the help

Re: Question [message #401280 is a reply to message #401273] Sun, 03 May 2009 17:57 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
We don't do homework assignments here.

Visit ASKTOM site above & do keyword search on "USER EXCEPTION"
Modify the code to meet your requirements.
Re: Question [message #401283 is a reply to message #401273] Sun, 03 May 2009 18:07 Go to previous messageGo to next message
bigredcu29
Messages: 6
Registered: May 2009
Junior Member
this isn't doing homework its just help with it. Im doing a big project and im stuck on this small part. Im sure its a simple solution and i have been trying to find solutions for days now and just cant seem to get it. I've done plenty of user exceptions but im having trouble just doing a check to see if the data is in the table.
Re: Question [message #401286 is a reply to message #401273] Sun, 03 May 2009 18:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>just cant seem to get it.
above is not a valid Oracle response or error code.
Post what you have tried using CUT & PASTE so we can see code & what exactly Oracle responded.

>I've done plenty of user exceptions
Post working examples.
Re: Question [message #401287 is a reply to message #401273] Sun, 03 May 2009 18:23 Go to previous messageGo to next message
bigredcu29
Messages: 6
Registered: May 2009
Junior Member
working example (I simplified the students table before):

procedure add_student
(t_sid in students.sid%type, t_sname in students.sname%type,
t_status in students.status%type, t_gpa in students.gpa%type,
t_email in students.email%type, t_deptname in students.deptname%type) is
invalid_gpa exception;
invalid_null_val exception;
invalid_student_status exception;
begin
if t_gpa < 0 or t_gpa > 4 then
raise invalid_gpa;
elseif (t_sid is null) then
raise invalid_null_val;
elseif (t_sname is null) then
raise invalid_null_val;
elseif (t_deptname is null) then
raise invalid_null_val;
elseif (t_status not in ('freshman', 'sophomore', 'junior', 'senior', 'graduate')) then
raise invalid_student_status;
else
insert into students (SID, SNAME, STATUS, GPA, EMAIL, DEPTNAME)
values (t_sid, t_sname, t_status, t_gpa, t_email, t_deptname);
end if;
exception
when invalid_gpa then
dbms_output.put_line('GPA is invalid.');
when invalid_null_val then
dbms_output.put_line('improper null value.');
when invalid_student_status then
dbms_output.put_line('improper student status.');
end;

as for what ive tried, ive done an sql query for that student and then if a no_data_found exception is raised then ive handeled that. But thats not the way i need to do it. I need a USER raised exception.
Re: Question [message #401288 is a reply to message #401273] Sun, 03 May 2009 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
The posted code contains syntax errors.
We need to see how Oracle executes your code.
All posted code should be FORMATTED as described in Posting Guidelines.

> I need a USER raised exception.
I have never seen a business requirement which stipulates type of exception being raised.
Such a requirement is typical for homework assignment.
Please explain why the exception MUST be a USER exception.
Re: Question [message #401289 is a reply to message #401273] Sun, 03 May 2009 18:36 Go to previous messageGo to next message
bigredcu29
Messages: 6
Registered: May 2009
Junior Member
That code works for me.... but either way...

I need it to be a user exception because i need to do multiple checks to see if the given values are in the tables. For example, i need to check if a students id is valid and in the same procedure check if a class id is valid. I cant use a no_data_fount exception because i need the exception to be unique for each problem and not just a generalization.
Re: Question [message #401293 is a reply to message #401273] Sun, 03 May 2009 21:06 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If the SID already exists (as PK) in table, then INSERT will fail.

What should happen when a single INSERT contains multiple errors?
Previous Topic: tuning or refining query
Next Topic: Help needed
Goto Forum:
  


Current Time: Sat Dec 10 07:15:26 CST 2016

Total time taken to generate the page: 0.20218 seconds