Using declare and if statements in Oracle sql?

Question

Hi I am havin gtrouble running this query. I am trying to get the input parameter and based on that value use it in the query.

The error I am getting is:

**Error(26,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior **

create or replace 
PROCEDURE GET_STATES (
    i_id IN NUMBER,
    o_result OUT SYS_REFCURSOR,

IS

DECLARE country VARCHAR2 = '';

BEGIN
  o_sqlmsg  := 'SUCCESS';
  o_sqlcode := 0;

  IF i_id == 284 
    THEN country := 'US';
    ELSE country := 'CA';
  END IF;
  OPEN o_result FOR 
    Select cnint as Id, cnabbv as Code, rtrim(cnname) as Name, i_id as CId
    from country 
    order by cnname;

A closing bracket ')' here at o_result OUT SYS_REFCURSOR) , Also DECLARE keyword is not needed, when used to declare Procedures/Functions! — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Apr 8, 2015 at 15:51
The equality operator is = in SQL (and PL/SQL). Not ==. And you need to use a proper assignment operator := in the variable declaration as well. — user330315
– user330315, Commented Apr 8, 2015 at 15:55
why bother with the if to set the value of country when you do not use it in the where? Good idea not to name your variables with the same name as a table and better idea to tell Oracle how big your VARCHAR2 is going to be...as in DECLARE v_country VARCHAR2(2); — kevinskio
– kevinskio, Commented Apr 8, 2015 at 16:15
Please, study more about basic Oracle script syntax before asking a question. — ThinkJet
– ThinkJet, Commented Apr 8, 2015 at 16:26
Is COUNTRY the name of a table in your database, or are you expecting that setting the variable country to 'US' or 'CA' will change the name of the table which is accessed by the SELECT statement? — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Apr 8, 2015 at 17:26

Bob Jarvis - Слава Україні · Accepted Answer · 2015-04-08 17:22:25Z

1

Your procedure shouldn't have a DECLARE keyword in it and is missing its final END statement, along with a few other minor syntax problems. Change it to look something like

create or replace 
PROCEDURE GET_STATES (i_id IN NUMBER,
                      o_result OUT SYS_REFCURSOR)
IS
  country VARCHAR2;
BEGIN
  o_sqlmsg  := 'SUCCESS';
  o_sqlcode := 0;

  IF i_id = 284 
    THEN country := 'US';
    ELSE country := 'CA';
  END IF;

  OPEN o_result FOR 
    Select cnint as Id,
           cnabbv as Code,
           rtrim(cnname) as Name,
           i_id as CId
    from country 
    order by cnname;
END GET_STATES;

Best of luck.

answered Apr 8, 2015 at 17:22

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using declare and if statements in Oracle sql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related