| Procedure Issue [message #149335] |
Thu, 01 December 2005 05:24  |
1821
Messages: 40
Registered: November 2005
|
Member |
|
|
Hey guys, I want the procedure to change a field in the section table based on the hours but for some reason the following doesn't work.
Does anyone have any idea where I went wrong? Thanks.
CREATE OR REPLACE PROCEDURE HOURS IS
HOURVAR NUMBER;
BEGIN
SELECT HOURS
INTO HOURVAR
FROM ADMIN
IF HOURVAR > 250
UPDATE SECTION
SET RATE = 'RRC'
END;
[Updated on: Thu, 01 December 2005 11:31] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Procedure Issue [message #149344 is a reply to message #149341] |
Thu, 01 December 2005 05:47  |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
| Quote: |
Warning: Procedure created with compilation errors
|
is not the error, it's simply a warning that you have an error.
type
sho err
then hit return then copy and paste the actual errors.
PS, you need a ; (semi-colon ) at the end of the update.
HTH
Jim
|
|
|
|
| Re: Procedure Issue [message #149347 is a reply to message #149344] |
Thu, 01 December 2005 05:51 |
1821
Messages: 40
Registered: November 2005
|
Member |
|
|
Thanks, I got the following errors:
LINE/COL ERROR -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
8/4 PL/SQL: ORA-00933: SQL command not properly ended
[Updated on: Thu, 01 December 2005 05:54] Report message to a moderator |
|
|
|
| Re: Procedure Issue [message #149352 is a reply to message #149335] |
Thu, 01 December 2005 06:03 |
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
|
Senior Member |
|
|
hi,
i think jim had already mentioned
this in his earlier post.
also you are missing the then and end if clause in your if statment.
and what if your hours col.
returns more than 1 row.update will again fail.
regards, [Updated on: Thu, 01 December 2005 06:05] Report message to a moderator |
|
|
|
| Re: Procedure Issue [message #149355 is a reply to message #149335] |
Thu, 01 December 2005 06:07 |
1821
Messages: 40
Registered: November 2005
|
Member |
|
|
Hi dhananjay, Sorry I am new to Sql why would it fail if more than 1 row is returned? Thanks
P.s got it working, you were right I forgot the THEN statement and also the END IF. I am still now sure why it fails though when more than one row is returned?
[Updated on: Thu, 01 December 2005 06:13] Report message to a moderator |
|
|
|
| Re: Procedure Issue [message #149390 is a reply to message #149355] |
Thu, 01 December 2005 07:59 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
The update will not fail, but will update each and every row in the table. The select into will fail if there is more than 1 row.
Advice: read some pl/sql docs for syntax and semantics.
|
|
|
|
| Re: Procedure Issue [message #149402 is a reply to message #149390] |
Thu, 01 December 2005 08:57 |
1821
Messages: 40
Registered: November 2005
|
Member |
|
|
Ah yes I see now I didn't realise that the SELECT INTO was only for one row. I am currently reading a Bill Pribyl book (Learning Oracle pl/sql) could you recommend any more pl/sql books?
And do you have any tips for how I can get round the SELECT INTO problem?
Thanks.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
