PL SQL Procedure problem
Hi.
I have a table Mechanic with Mechanic_id, Mechanic_name and speciality_id
I also have a table Speciality with speciality_id and speciality_name.
I am trying to create a PL SQL procedure where I can enter in a mechanic's name and the mechanic's speciality is returned.
This is what I have so far........
CREATE OR REPLACE PROCEDURE mechProc (name IN NVARCHAR2)
IS
id NUMBER;
special NVARCHAR2;
BEGIN
Select speciality_id from mechanic where mechanic_name = name;
Insert speciality_id INTO id;
Select speciality_name from speciality where speciality = id;
Insert speciality INTO special;
DBMS_OUTPUT.PUT_LINE(Mecha
END mechProc;
/
Here's my error......
ERROR at line 9: PL/SQL: SQL Statement ignored7. Insert speciality_id INTO id;
8. Select speciality_name from speciality where speciality_id = id;
9. Insert speciality_name INTO special;
10. DBMS_OUTPUT.PUT_LINE('Mech
11. END mechProc;
I have a table Mechanic with Mechanic_id, Mechanic_name and speciality_id
I also have a table Speciality with speciality_id and speciality_name.
I am trying to create a PL SQL procedure where I can enter in a mechanic's name and the mechanic's speciality is returned.
This is what I have so far........
CREATE OR REPLACE PROCEDURE mechProc (name IN NVARCHAR2)
IS
id NUMBER;
special NVARCHAR2;
BEGIN
Select speciality_id from mechanic where mechanic_name = name;
Insert speciality_id INTO id;
Select speciality_name from speciality where speciality = id;
Insert speciality INTO special;
DBMS_OUTPUT.PUT_LINE(Mecha
END mechProc;
/
Here's my error......
ERROR at line 9: PL/SQL: SQL Statement ignored7. Insert speciality_id INTO id;
8. Select speciality_name from speciality where speciality_id = id;
9. Insert speciality_name INTO special;
10. DBMS_OUTPUT.PUT_LINE('Mech
11. END mechProc;
Procedure or function?
You insert into a table not a variable.
I would go with a function. If you go with a procedure you need an out variable:
Create or replace function myfunc(inName varchar2) return varchar2 is
Retval varchar2;
Begin
Select spec into retval from table;
Return retval;
End;
/
Or procedure:
Create or replace procedure myproc(inname in varchar2, outResult out varchar2) I'd
Begin
Select spec into Outresult from table;
End;
/
You insert into a table not a variable.
I would go with a function. If you go with a procedure you need an out variable:
Create or replace function myfunc(inName varchar2) return varchar2 is
Retval varchar2;
Begin
Select spec into retval from table;
Return retval;
End;
/
Or procedure:
Create or replace procedure myproc(inname in varchar2, outResult out varchar2) I'd
Begin
Select spec into Outresult from table;
End;
/
furthermore, I will put some exception handlers into the code
Going even further, you can find specialty_name in one query by joining mechanic and speciality tables.
Note: code above is untested
CREATE OR REPLACE PROCEDURE mechProc (name IN NVARCHAR2)
IS
id NUMBER;
special NVARCHAR2;
BEGIN
Select speciality_id into id from mechanic where mechanic_name = name;
Select speciality_name into special from speciality where speciality = id;
DBMS_OUTPUT.PUT_LINE(Mechanic ' || name || ' specialises in ' || special );
EXCEPTION
when TOO_MANY_ROWS then
DBMS_OUTPUT.PUT_LINE(Mechanic ' || name || ' has more than one match' );
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE(Mechanic ' || name || ' not found' );
END mechProc;Going even further, you can find specialty_name in one query by joining mechanic and speciality tables.
Note: code above is untested
Looks like slightwv and I are working in tandem again =)
ASKER
Hmmm, still getting this error........
ERROR at line 9: PL/SQL: SQL Statement ignored7. Insert speciality_id INTO id;
8. Select speciality_name from speciality where speciality = id;
9. Insert speciality INTO special;
ERROR at line 9: PL/SQL: SQL Statement ignored7. Insert speciality_id INTO id;
8. Select speciality_name from speciality where speciality = id;
9. Insert speciality INTO special;
On at the same time?
I would probably join the tables into a single select.
I would probably join the tables into a single select.
Look at our examples. You don't insert into a variable.
You select INTO a variable.
Check the online docs. There are tons of examples on this.
You select INTO a variable.
Check the online docs. There are tons of examples on this.
ERROR at line 9: PL/SQL: SQL Statement ignored7. Insert speciality_id INTO id;
8. Select speciality_name from speciality where speciality = id;
9. Insert speciality INTO special;graziazi, you need to do the same thing I did for the previous select to that part of the code.
ASKER
sure, thanks johann. I'll try it.
slightwv: The function sounds interesting. I'm a littel confused though. I presume I can stick my select statements somewhere in there?
slightwv: The function sounds interesting. I'm a littel confused though. I presume I can stick my select statements somewhere in there?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Johanntagle the procedure executed however when I execute the proc I get the error......
ORA-01722: invalid number
ORA-01722: invalid number
It means your specialty_id column contains non-numeric data. If so then just declare id as varchar2. Better yet, define it as
id mechanic.specialty_id%type
so that it will inherit the data type of the column.
id mechanic.specialty_id%type
so that it will inherit the data type of the column.
ASKER
I just ran the function (thanks btw) and I'm getting the error ........
ORA-01422: exact fetch returns more than requested number of rows
so there's probably something up with my tables
ORA-01422: exact fetch returns more than requested number of rows
so there's probably something up with my tables
You ran the exact function I created? The exception handlers should have handled that. But yes it means your data is such that either mechanic.mechanic_name or special.speciality (or both) have non-unique values.
No I don't think you ran my exact function because it won't compile - I missed some semicolons. It should be:
Please read up on exception handling and take time to understand the examples given to you - you should have related the ORA-01422 message to the exception handlers I created if you just studied it a bit.
CREATE OR REPLACE FUNCTION mechfunc (name IN NVARCHAR2)
RETURN NVARCHAR2
IS
id mechanic.specialty_id%type;
special special.speciality_name%type;
BEGIN
BEGIN
Select speciality_id into id from mechanic where mechanic_name = name;
Select speciality_name into special from speciality where speciality = id;
EXCEPTION
when TOO_MANY_ROWS then
special := 'Too many matches found for ' ||name';
when NO_DATA_FOUND then
special := 'No match found for ' ||name';
END;
return special;
END mechProc;Please read up on exception handling and take time to understand the examples given to you - you should have related the ORA-01422 message to the exception handlers I created if you just studied it a bit.
ASKER
Still getting 'invalid number' for the procedure I'm afraid
Can you send over your update procedure?
ASKER
I took the exception stuff out, as I was getting errors. I was going to put it back in though :)
I agree with you about doing a little extra study; I won't learn anything otherwise :)
I agree with you about doing a little extra study; I won't learn anything otherwise :)
updated procedure, I mean.
ASKER
Here you go....
CREATE OR REPLACE PROCEDURE mechSpecProc (mech_name IN NVARCHAR2)
IS
id Mechanic.speciality_id%type;
special Speciality.speciality_id%type;
BEGIN
Select speciality_id into id from mechanic where name = mech_name;
Select speciality_name INTO special from speciality where speciality_name = id;
DBMS_OUTPUT.PUT_LINE('Mechanic' || mech_name || ' specialises in ' || special );
END mechSpecProc;
/
In don't get it. With this you can get the too many rows or the no data found error, but not the invalid number error.
ASKER
Hmm, I'm not sure. I think I'll have to spend a bit longer getting up to scratch on this.
I'm happy with what you showed me, as I understand where I can use it.
Just one question (unrelated)before I go....
If I try and update a column in a table, it's not updating. Is that because there are references present to other tables?
I'm happy with what you showed me, as I understand where I can use it.
Just one question (unrelated)before I go....
If I try and update a column in a table, it's not updating. Is that because there are references present to other tables?
ASKER
Going to go off to bed - 5am here. I'll accept solution(s) tomorrow. Thanks for all the help!
If I try and update a column in a table, it's not updating. Is that because there are references present to other tables?
Many possible reasons for this - what exactly are you experiencing?
1. it's stuck forever at doing the update statement - somebody must have updated the record before and haven't executed a commit
2. there are integrity constraints (referential and otherwise) being violated - you will get a specific error message for this.
ASKER
No actual error message. It just says '0 rows updated' or something like this
What are you updating? The question is how to return a value from a procedure.
Dbms_output does NOT 'return' a value. It only echoos it to the screen.
Check my original examples. The function returns a value you can use in an update. The procedure has an OUT parameter you can assign then use in an update.
Dbms_output does NOT 'return' a value. It only echoos it to the screen.
Check my original examples. The function returns a value you can use in an update. The procedure has an OUT parameter you can assign then use in an update.
slightwv: the update is actually a different question.
graziazi: suggest you post it as a new question, and give the details such as the statement used to update, and the current contents of the table being updated.
graziazi: suggest you post it as a new question, and give the details such as the statement used to update, and the current contents of the table being updated.
I figured it was a different question.
I assume they want to update a column based on the output of the code. This is what a function is for. Not a procedure.
I assume they want to update a column based on the output of the code. This is what a function is for. Not a procedure.
Well, it's my turn to sign off. Good night from my part of the world =)
ASKER
Here's a procedure below that I want to do an update. It echos out relevant info, but doesn;'t actual update the table.
CREATE OR REPLACE PROCEDURE updateNextofkinProc (nextof IN NVARCHAR2, patientname IN NVARCHAR2)
IS
BEGIN
UPDATE patient SET next_of_kin = nextof WHERE name = patientname;
DBMS_OUTPUT.PUT_LINE('Next of kin changed to ' || nextof);
END updateNextofkinProc;
/
Again: dbms_output DOES NOT return a value. Â It just sends it to the screen.
You need a function to return a value.
You need a function to return a value.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
johanntagle & slightwv thanks for all the insight help!
Select speciality_id into id from mechanic where mechanic_name = name;