Fetch data from a table in oracle sql

Question

I've two table in the database, the first one is Person and the second is Pilot. as following:

Person Table:

CREATE TABLE person(
    person_id   NUMBER  PRIMARY KEY,
    last_name   VARCHAR2(30)    NOT NULL,
    first_name  VARCHAR2(30)    NOT NULL,
    hire_date   VARCHAR2(30)    NOT NULL,
    job_type    CHAR        NOT NULL,
    job_status  CHAR        NOT NULL
);
/
INSERT INTO person VALUES (1000, 'Smith', 'Ryan', '04-MAY-90','F', 'I');
INSERT INTO person VALUES (1170, 'Brown', 'Dean', '01-DEC-92','P', 'A');
INSERT INTO person VALUES (2010, 'Fisher', 'Jane', '12-FEB-95','F', 'I');
INSERT INTO person VALUES (2080, 'Brewster', 'Andre', '28-JUL-98', 'F', 'A');
INSERT INTO person VALUES (3190, 'Clark', 'Dan', '04-APR-01','P', 'A');
INSERT INTO person VALUES (3500, 'Jackson', 'Tyler', '01-NOV-05', 'F', 'A');
INSERT INTO person VALUES (4000, 'Miller', 'Mary', '11-JAN-08', 'F', 'A');
INSERT INTO person VALUES (4100, 'Jackson', 'Peter', '08-AUG-11', 'P','I');
INSERT INTO person VALUES (4200, 'Smith', 'Ryan', '08-DEC-12', 'F','A');
COMMIT;
/

Pilot Table:

CREATE TABLE pilot(
person_id       NUMBER      PRIMARY KEY,
pilot_type      VARCHAR2(100)   NOT NULL,
CONSTRAINT fk_person_pilot FOREIGN KEY (person_id) 
    REFERENCES person(person_id)
);
/
INSERT INTO pilot VALUES (1170, 'Commercial pilot');
INSERT INTO pilot VALUES (2010, 'Airline transport pilot');
INSERT INTO pilot VALUES (3500, 'Airline transport pilot');
COMMIT;
/

I'm asked to write a pl/sql block of code that accepts the last name from the user and return the result as following: 1) if the last name is not in the table, it returns all the rows in the table. 2) if the last name is in the table, it shows all of the employee's information.

So far I'm doing well with the code, but I got stuck in the case that there are two employees with the last name. here is the cursor that I wrote:

cursor person_info is
select  last_name, first_name, hire_date, job_type, job_status,      nvl(pilot_type, '-----------') 
from person
full outer join  pilot
on person.person_id = pilot.person_id
where upper(last_name) = upper(v_last_name)
group by  last_name, first_name, hire_date, job_type, job_status, pilot_type
order by last_name, first_name, hire_date asc;

Logically, there are three cases to be covered: the first case, when the entered last name is in the table, I return all the rows in the table and that's done. The second case when there is only one employee with the entered last name, and this case is done as well. The last case when there are more than one employee having the same last name like for example 'Jackson' or 'Smith' in this case, my program crashes and give me the error that my select into statement returns more than one row.

select person_id
into v_n
from person
where upper(last_name) = upper(v_last_name);

if v_n = 1 then
open  person_info;
fetch person_info into v_last_name, v_first_name, v_hire_date, v_job_type,    v_job_status, v_pilot_type;

Can someone help me in guiding me how to fetch the data correctly? I'm not allowed to create any temporary tables or views. I'm so sorry for making the problem longer than it should, but I was trying to be as clear as possible in explaining the problem.

Thank you in advance.

Full outer join isn't necessary because the FK means you can't have a pilot who isn't a person. What does the assignment expect with multiple matches? And how are you returning all the data when there is no match? The mechanism will be the same for many as it is for two. (And can be the same for one). From what you've shown your v_n should be the count of rows, not a specific ID... — Alex Poole
– Alex Poole, Commented Feb 21, 2016 at 11:19

Jordan Daly · Accepted Answer · 2016-02-21 11:17:16Z

1

if the error is "ORA-01422 exact fetch returns more than requested number of rows" then I think your answer is here https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:981494932508

If you EXPECT the query to return more than one row, you would code:

for x in ( select * from t where ... ) loop -- process the X record here end loop;

answered Feb 21, 2016 at 11:17

Jordan Daly

644 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2016-02-21 11:31:20Z

Your immediate issue is that you're selecting the matching person_id into a variable, and then seeing if that specific ID is 1. You don't have an actual ID 1 anyway so that check would never match; but it is that querying matching multiple rows that gets the error, as you can't put two matching IDs into a single scalar variable.

The way you've structured it looks like you are trying to count how many matching rows there are, rather than looking for a specific ID:

select count(person_id)
into v_n
from person
where upper(last_name) = upper(v_last_name);

if v_n = 1 then
....

When you do have multiple matches then you will need to use the same mechanism to return all of those as you do when there are no matches and you return all employees. You may find the logic should be in the cursor query rather then in PL/SQL logic. It depends on the details of the assignment though, and how it expects you to return the data in both (or all three) scenarios.

It's also possible you just aren't expected to hit this problem - it isn't clear if the assignment is finding all employees, or only those that are pilots. The issue still exists in general, but with the data you show there aren't any duplicate pilot last names. If you haven't learned about this kind of error yet perhaps you're getting a bit ahead of what your tutor expects.

Collectives™ on Stack Overflow

Fetch data from a table in oracle sql

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related