• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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(Mechanic ' || name || ' specialises in ' || special );
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('Mechanic ' || name || ' specialises in ' || special);
11. END mechProc;
0
graziazi
Asked:
graziazi
  • 14
  • 12
  • 7
2 Solutions
 
johanntagleCommented:
it should be:

Select speciality_id into id from mechanic where mechanic_name =  name;
0
 
slightwv (䄆 Netminder) Commented:
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;
/
0
 
johanntagleCommented:
furthermore, I will put some exception handlers into the code

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;

Open in new window


Going even further, you can find specialty_name in one query by joining mechanic and speciality tables.

Note: code above is untested
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
johanntagleCommented:
Looks like slightwv and I are working in tandem again =)
0
 
graziaziAuthor Commented:
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;
0
 
slightwv (䄆 Netminder) Commented:
On at the same time?

I would probably join the tables into a single select.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
johanntagleCommented:
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;

Open in new window


graziazi, you need to do the same thing I did for the previous select to that part of the code.
0
 
graziaziAuthor Commented:
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?
0
 
johanntagleCommented:
Main difference of a function is it returns a value to the caller.  This is different from printing it via dbms_output.put_line.  See below for a function version of your procedure:

CREATE OR REPLACE FUNCTION mechfunc (name IN NVARCHAR2)
RETURN NVARCHAR2
IS 
id NUMBER;
special NVARCHAR2;
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;

Open in new window


Again, code above is untested.  Read up more on functions and procedures, and PL/SQL in general using the online docs Oracle provides
0
 
graziaziAuthor Commented:
Thanks Johanntagle the procedure executed however when I execute the proc I get the error......

ORA-01722: invalid number
0
 
johanntagleCommented:
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.
0
 
graziaziAuthor Commented:
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
0
 
johanntagleCommented:
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.
0
 
johanntagleCommented:
No I don't think you ran my exact function because it won't compile - I missed some semicolons.  It should be:

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;

Open in new window


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.
0
 
graziaziAuthor Commented:
Still getting 'invalid number' for the procedure I'm afraid
0
 
johanntagleCommented:
Can you send over your update procedure?
0
 
graziaziAuthor Commented:
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 :)
0
 
johanntagleCommented:
updated procedure, I mean.
0
 
graziaziAuthor Commented:
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;
/

Open in new window

0
 
johanntagleCommented:
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.
0
 
graziaziAuthor Commented:
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?
0
 
graziaziAuthor Commented:
Going to go off to bed - 5am here. I'll accept solution(s) tomorrow. Thanks for all the help!
0
 
johanntagleCommented:
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.
0
 
graziaziAuthor Commented:
No actual error message. It just says  '0 rows updated' or something like this
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
johanntagleCommented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
johanntagleCommented:
Well, it's my turn to sign off.  Good night from my part of the world =)
0
 
graziaziAuthor Commented:
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;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Again: dbms_output DOES NOT return a value.  It just sends it to the screen.

You need a function to return a value.
0
 
slightwv (䄆 Netminder) Commented:
Below is a working sample and what I'm saying.

I guessed at your tables but the function returns the speciality_name.  You can select it.  I even then created tab1 and used the function to update a row.


--drop table mechanic purge;
--drop table speciality purge;

create table mechanic(mech_name char(1), speciality_id char(1));
create table speciality(speciality_id char(1), speciality_name char(1));

insert into mechanic values('a','1');
insert into mechanic values('b','2');
insert into speciality values('1','A');
insert into speciality values('2','B');
commit;

CREATE OR REPLACE function mechSpecProc (in_mech_name IN NVARCHAR2) return varchar2
IS 
retName Speciality.speciality_name%type;
BEGIN

	select speciality_name into retName from speciality s, mechanic m
	where s.speciality_id=m.speciality_id and m.mech_name=in_mech_name;

	return retName;
END mechSpecProc;
/

show errors

select mechSpecProc('a') from dual;

drop table tab1 purge;
create table tab1 (col1 char(1));

insert into tab1 values('z');
commit;

update tab1 set col1=mechSpecProc('a'); 
commit;

select * from tab1;

Open in new window

0
 
graziaziAuthor Commented:
johanntagle & slightwv thanks for all the insight help!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 14
  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now