Home » SQL & PL/SQL » SQL & PL/SQL » error using If statement in procedure
error using If statement in procedure [message #210994] Sun, 24 December 2006 15:56 Go to next message
vipersting
Messages: 6
Registered: February 2006
Junior Member
Hi. I have a procedure, I want to use an if statement to check if car reg is greater 999, if so then to set it to 999. im getting this error: ERROR at line 7: PL/SQL: ORA-00922: missing or invalid option: cant see whats wrong, any help? thanks.

CREATE OR REPLACE PROCEDURE newCar
(vName IN VARCHAR2, vReg IN NUMBER)
IS
BEGIN
If vReg > 999
Then
set vReg = 999
where carReg = vReg;
INSERT INTO Cars
(carName, carReg)
VALUES
(vName, vReg);

END;
Re: error using If statement in procedure [message #210995 is a reply to message #210994] Sun, 24 December 2006 17:18 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You should read the fine PL/SQL Reference manual found at http://tahiti.oracle.com to learn the correct syntax for IF statement.
Re: error using If statement in procedure [message #211017 is a reply to message #210995] Mon, 25 December 2006 03:14 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that there's more than just an IF syntax here ... what about 'set ... where' (where did this come from?), along with '=' which should be ':=' and PLS-00363 error?

As a gift, here are some possible solutions: IF is, maybe, not that good - there are other ways to do that.

First, using an IF-THEN-ELSE construct: after you read manual(s) Anacedent suggested in previous post and learn how to use correct command syntax, you'll still need to use additional variable ('l_vreg') because you can not modify argument's value in PL/SQL procedure.
CREATE OR REPLACE PROCEDURE Newcar
  (vname IN VARCHAR2, vreg IN NUMBER)
IS
  l_vreg NUMBER;
BEGIN
  l_vreg := vreg;
  IF vreg > 999 THEN
     l_vreg := 999;
  END IF;
  
  INSERT INTO CARS (carname, carreg)
    VALUES
    (vname, l_vreg);
END;
/

Another option uses CASE:
CREATE OR REPLACE PROCEDURE newcar2
  (vname IN VARCHAR2, vreg IN NUMBER)
IS
BEGIN
  INSERT INTO CARS (carname, carreg)
    (SELECT vname, 
        CASE
          WHEN vreg > 999 THEN 999
          ELSE vreg
        END
     FROM dual
    );
END;
/

If you don't like it, there's a DECODE + SIGN possiblity:
CREATE OR REPLACE PROCEDURE newcar3
  (vname IN VARCHAR2, vreg IN NUMBER)
IS
BEGIN
  INSERT INTO CARS (carname, carreg)
    (SELECT vname, 
       DECODE(SIGN(999 - vreg), -1, 999,
                                     vreg
             )
     FROM dual
    );
END;
/

There might be another solutions too, but I'd left them to you.
Re: error using If statement in procedure [message #211043 is a reply to message #211017] Mon, 25 December 2006 16:16 Go to previous messageGo to next message
vipersting
Messages: 6
Registered: February 2006
Junior Member
thank you very much for that. just a quick question. now that the procedure has been created, suppose i wanted to add values, i would use this line to call procedure and then pass two values.

execute newCar('test', 123)

the sytax execute works on SQL*plus - since i created something similar, but now i am using oracle express edition, and it seems the word execute is not recognised.

ORA-00900: invalid SQL statement - is the error I get. I tried, start and run instead of execute, no luck.

Any suggestions?

Thanks
Re: error using If statement in procedure [message #211044 is a reply to message #210994] Mon, 25 December 2006 19:06 Go to previous messageGo to next message
vipersting
Messages: 6
Registered: February 2006
Junior Member
Hi.

I just realised execute command doesn't run on the oracle express edition interface but works fine on the SQL command line that comes with it too.

Thanks again.

Re: error using If statement in procedure [message #211146 is a reply to message #210994] Tue, 26 December 2006 16:05 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Of course the easiest way is to do away with the entire procedure, simply


insert into cars values(carname,least(999,carreg));
Previous Topic: Mathematical Theory of Structured Query Language?
Next Topic: How to create List by Range partition
Goto Forum:
  


Current Time: Wed Dec 07 10:38:23 CST 2016

Total time taken to generate the page: 0.09483 seconds