Stored procedure prob!

Dev Shed Forums Sponsor:

February 2nd, 2005, 06:45 AM

stuyg

Registered User

Join Date: Feb 2005

Posts: 9

Time spent in forums: 2 h 4 m 44 sec
Reputation Power: 0

Stored procedure prob!

Hi, can someone help me, when i try to create this Proc using SQL*Plus on 9i i get error:

Warning: Procedure created with compilation errors.

CREATE OR REPLACE PROCEDURE sp_IssuePresc
(
PID_in IN integer,
drug_in IN varchar2(100),
qty_in IN integer,
pName_in IN varchar2(50),
docName_in IN varchar2(50),
issued_in IN boolean,
expires_in IN date,
)
AS
BEGIN
INSERT INTO Prescription(prescriptionID, PID, drug, qty, pName, docName, issued, expires)
VALUES(perscription_seq.NEXTVAL, PID_in, drug_in, qty_in, pName_in, docName_in, issued_in, expires_in)
END

All help appreciated!!!!!

February 2nd, 2005, 11:07 AM

paulh1983

Contributing User

Join Date: Dec 2004

Posts: 1,255

Time spent in forums: 1 Week 1 Day 15 h 14 m
Reputation Power: 17

edited wrong advice!

February 2nd, 2005, 11:55 AM

stuyg

Registered User

Join Date: Feb 2005

Posts: 9

Time spent in forums: 2 h 4 m 44 sec
Reputation Power: 0

Quote:

Originally Posted by paulh1983

one thing is that you have declared the some variables but they have no VALUES?

you could try something like:
[pseudo english]

create procedure (parameters )

AS

Begin
insert into the table
end
/

let me know if you need detailed procedure!

to run this one you would do:
exec procedure_name(parameters)

Hi Paul,

any chance you could show the full code? i'm new to Oracle and havent a clue!

Thanks in advance!

February 2nd, 2005, 12:46 PM

paulh1983

Contributing User

Join Date: Dec 2004

Posts: 1,255

Time spent in forums: 1 Week 1 Day 15 h 14 m
Reputation Power: 17

edited! my mistake! wrong advice

February 2nd, 2005, 01:01 PM

paulh1983

Contributing User

Join Date: Dec 2004

Posts: 1,255

Time spent in forums: 1 Week 1 Day 15 h 14 m
Reputation Power: 17

try this code:

Code:


CREATE OR REPLACE PROCEDURE sp_IssuePresc 
(
	PID_in	   integer,
	drug_in     varchar2(100),
	qty_in	    integer,
	pName_in    varchar2(50),
	docName_in  varchar2(50),
	issued_in	  boolean,
	expires_in	date,
) 
AS	
BEGIN
	INSERT INTO Prescription(prescriptionID, PID, drug, qty, pName, docName, issued, expires)
	VALUES(perscription_seq.NEXTVAL, PID_in, drug_in, qty_in, pName_in, docName_in, issued_in, expires_in)
END;
/

forget everything i have said before this post!! ! i wasnt looking at your code too carefully! sorry

ok i think u need to remove the IN from there1

and one more thing if your prescription tables has these 8 columns it would be better if you used:
insert into Prescription values((perscription_seq.NEXTVAL, PID_in, drug_in, qty_in, pName_in, docName_in, issued_in, expires_in)

and one more thing, after the end there should be ; and a /

February 2nd, 2005, 01:39 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Munich, Bavaria

Posts: 1,007

Time spent in forums: 1 Week 3 Days 13 h 7 m 13 sec
Reputation Power: 67

Does "show errors" give you more detailed information?

February 2nd, 2005, 01:50 PM

paulh1983

Contributing User

Join Date: Dec 2004

Posts: 1,255

Time spent in forums: 1 Week 1 Day 15 h 14 m
Reputation Power: 17

Quote:

Originally Posted by shammat

Does "show errors" give you more detailed information?

what he means is that after you type in your procedure and it says compilation error, type in "show errors" and press enter, you should get some info

cant believe i missed that out

February 2nd, 2005, 02:01 PM

stuyg

Registered User

Join Date: Feb 2005

Posts: 9

Time spent in forums: 2 h 4 m 44 sec
Reputation Power: 0

Quote:

Originally Posted by paulh1983

what he means is that after you type in your procedure and it says compilation error, type in "show errors" and press enter, you should get some info

cant believe i missed that out

it doesnt show anything!

February 2nd, 2005, 02:40 PM

paulh1983

Contributing User

Join Date: Dec 2004

Posts: 1,255

Time spent in forums: 1 Week 1 Day 15 h 14 m
Reputation Power: 17

Quote:

Originally Posted by stuyg

it doesnt show anything!

give me the table info (post it here) and i am gonna try it myself

#10

February 2nd, 2005, 04:01 PM

hedge

Contributing User

Join Date: Apr 2002

Posts: 693

Time spent in forums: 4 Days 23 h 47 m 32 sec
Reputation Power: 20

extra comma after last argument, prescription spelled wrong, missing semi-colons

PHP Code:


		
			
 CREATE OR REPLACE PROCEDURE sp_IssuePresc (

 PID_in IN integer, drug_in IN varchar2(100), qty_in IN integer, pName_in IN varchar2(50),

 docName_in IN varchar2(50), issued_in IN boolean, expires_in IN date) AS 

BEGIN

  INSERT INTO Prescription (prescriptionID, PID, drug, qty, pName, docName, issued, expires)

    VALUES (prescription_seq.NEXTVAL, PID_in, drug_in, qty_in, pName_in, docName_in, issued_in, expires_in);

END;

/

what's the point of making this a sp anyway?

#11

February 2nd, 2005, 04:21 PM

stuyg

Registered User

Join Date: Feb 2005

Posts: 9

Time spent in forums: 2 h 4 m 44 sec
Reputation Power: 0

Quote:

Originally Posted by paulh1983

give me the table info (post it here) and i am gonna try it myself

I've left the code in college, i can post it in the morning, hope thats ok!

#12

February 2nd, 2005, 04:28 PM

paulh1983

Contributing User

Join Date: Dec 2004

Posts: 1,255

Time spent in forums: 1 Week 1 Day 15 h 14 m
Reputation Power: 17

Quote:

Originally Posted by stuyg

I've left the code in college, i can post it in the morning, hope thats ok!

yeah thats fine.

#13

February 2nd, 2005, 05:20 PM

dancesport_warr

Contributing User

Join Date: Aug 2004

Posts: 130

Time spent in forums: 1 Day 14 h 14 m 36 sec
Reputation Power: 5

when specifying the arguments for stored procedures in Oracle, u r not allowed to specify lengths so signature of your stored procedure should be:

Code:

CREATE OR REPLACE PROCEDURE sp_IssuePresc
(
PID_in IN integer,
drug_in IN varchar2,
qty_in IN integer,
pName_in IN varchar2,
docName_in IN varchar2,
issued_in IN boolean,
expires_in IN date,
)

The only other potential error is make sure that arguments have same data type as the columns they're inserting into... im not sure if Oracle converts the arguments to column data types if theyre not already when inserting but just to be on the safe side make sure that they are the same.

#14

February 10th, 2005, 09:54 AM

shafique

Senior Member

Join Date: Sep 2003

Location: Canada

Posts: 305

Time spent in forums: 2 h 45 m 20 sec
Reputation Power: 6

Code:

CREATE OR REPLACE PROCEDURE sp_IssuePresc 
(
PID_in IN integer,
drug_in IN varchar2(100),
qty_in IN integer,
pName_in IN varchar2(50),
docName_in IN varchar2(50),
issued_in IN boolean,
expires_in IN date, -- remove the comma because it is a last argument in the list
) 
AS 
BEGIN
INSERT INTO Prescription(prescriptionID, PID, drug, qty, pName, docName, issued, expires)
VALUES(perscription_seq.NEXTVAL, PID_in, drug_in, qty_in, pName_in, docName_in, issued_in, expires_in); -- put the samicolon at the end of statement
END; --put samicolon at the end of statement
/  -- put the termination of block

Yes Oracle converts the one data type to its compatible data type. The another issue when you are passing values you need to keep sequence until you are using column alias in calling of procedure.

#15

February 10th, 2005, 12:35 PM

stuyg

Registered User

Join Date: Feb 2005

Posts: 9

Time spent in forums: 2 h 4 m 44 sec
Reputation Power: 0

Quote:

Originally Posted by shafique

Code:

CREATE OR REPLACE PROCEDURE sp_IssuePresc 
(
PID_in IN integer,
drug_in IN varchar2(100),
qty_in IN integer,
pName_in IN varchar2(50),
docName_in IN varchar2(50),
issued_in IN boolean,
expires_in IN date, -- remove the comma because it is a last argument in the list
) 
AS 
BEGIN
INSERT INTO Prescription(prescriptionID, PID, drug, qty, pName, docName, issued, expires)
VALUES(perscription_seq.NEXTVAL, PID_in, drug_in, qty_in, pName_in, docName_in, issued_in, expires_in); -- put the samicolon at the end of statement
END; --put samicolon at the end of statement
/  -- put the termination of block

Yes Oracle converts the one data type to its compatible data type. The another issue when you are passing values you need to keep sequence until you are using column alias in calling of procedure.

Thanks guys!

My syntax ia a shambles! I'm a complete newbie to this stuff, the proc works now!

Stu.