CODEWITHSUNDEEP
oracle-databaseif-statementstored-proceduresplsql
Vishal5364
Vishal5364

Reputation: 293

If..else not working in Oracle

I have written a procedure containing a simple if..else if..end if as below. While compiling it throws an error as

Error(103,8): PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if Error(113,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map

I am unable to find the cause of error as I assume that I have closed all the IFs correctly. Can anyone pls help. Below is my whole procedure

CREATE OR REPLACE PROCEDURE proc_ntitems_update
AS
   CURSOR cur_systeminfo
   IS
      SELECT location,
             childseq,
             btproduct,
             product,
             questionid
        FROM systeminfo;

   vproduct         tblsysteminfo_products.product%TYPE;
   vquestionid      tblsysteminfo_products.questionid%TYPE;
   vlocation        systeminfo.location%TYPE;
   vchildseq        systeminfo.childseq%TYPE;
   verror           VARCHAR2 (200);
   consrgsitename   VARCHAR2 (100);
   consrgsite       VARCHAR2 (100);
   conmgsitename    VARCHAR2 (100);
   intname          NUMBER;
   vsicount         NUMBER;
BEGIN
   OPEN cur_systeminfo;

   LOOP
      FETCH cur_systeminfo
      INTO vlocation, vchildseq, vbtproduct, vproduct, vquestionid;

      EXIT WHEN cur_systeminfo%NOTFOUND;

      IF INSTR (vlocation, '_') > 0
      THEN
         vquestionid := consrgsitename;

         IF SUBSTR (vlocation, 1, LENGTH (consrgsite)) = consrgsite
         THEN
            intname := LENGTH (consrgsite);
            vquestionid := consrgsitename;
         ELSE
            IF SUBSTR (vlocation, 1, LENGTH (consrg50site)) = consrg50site
            THEN
               intname := LENGTH (consrgsite);
               vquestionid := consrgsitename;
               vlocation :=
                     vlocation
                  || SUBSTR (vlocation, intname + 1, LENGTH (vlocation));
            --Check if this item is for a DATA site. These have a Location of locations_data_n where n is  the site number.

            ELSE
               IF LOWER (SUBSTR (vlocation, 1, 14)) = 'locations_data'
               THEN
                  vsitename := 'Yes';
                  vquestionid := 'txtLocationName';
               ELSE
                  IF     LOWER (SUBSTR (vlocation, 1, 14)) = 'locations_data'
                     AND vchildseq = '0'
                  THEN
                     BEGIN
                        SELECT COUNT (location)
                          INTO vsicount
                          FROM systeminfo
                         WHERE LOWER (vlocation) = 'locations_1';
                     EXCEPTION
                        WHEN nodatafound
                        THEN
                           verror :=
                                 ' proc_ntitems_update '
                              || SUBSTR (
                                    TO_CHAR (SQLCODE) || ' - ' || SQLERRM,
                                    1,
                                    480);
                        WHEN OTHERS
                        THEN
                           verror :=
                                 ' proc_ntitems_update '
                              || SUBSTR (
                                    TO_CHAR (SQLCODE) || ' - ' || SQLERRM,
                                    1,
                                    480);
                     END;

                     IF vsicount = 0
                     THEN
                        vsitename := 'No';
                        intname := LENGTH (vlocation);
                        vquestionid := 'txtLocationName';
                     END IF;
                  ELSE
                     IF     LOWER (SUBSTR (vlocation, 1, 14)) =
                               'locations_data'
                        AND vchildseq > '0'
                     THEN
                        vquestionid := 'txtLocationName';
                     ELSE
                        IF LOWER (SUBSTR (vlocation, 1, 5)) = 'ripe_'
                        THEN
                           vremotesitenumber :=
                              SUBSTR (
                                 vlocation,
                                 -1,
                                 (LENGTH (vloaction) - INSTR (vlocation, '_')));
                           vproduct := 'MMRemote';
                           vquestionid := 'cboRemoteType' || vremotesitenumber;
                           vsitename := vecvalue || ' ' || vremotesitenumber;
                        ELSE
                           IF LOWER (
                                 SUBSTR (vlocation,
                                         1,
                                         LENGTH (conmerchandisesite))) =
                                 conmerchandisesite
                           THEN
                              vsitename := conmerchandisesite;
                           ELSE
                              vsitename := btproduct;
                           END IF;


                           BEGIN
                              UPDATE tblntitems
                                 SET location = vlocation,
                                     childseq = vchildseq,
                                     sitename = vbtproduct,
                                     product = vproduct,
                                     questionid = vquestionid
                               WHERE questionid = vquestionid;
                           EXCEPTION
                              WHEN OTHERS
                              THEN
                                 verror :=
                                       'proc_ntitems_update '
                                    || SUBSTR (
                                             TO_CHAR (SQLCODE)
                                          || ' - '
                                          || SQLERRM,
                                          1,
                                          480);
                           END;
                        END IF;
                     END loop;

   CLOSE   cur_systeminfo;

      EXCEPTION
        WHEN OTHERS
        THEN
              verror := 'proc_ntitems_update ' || substr (to_char (sqlcode) || ' - ' || sqlerrm, 1, 480);

        INSERT INTO log_import_errors VALUES ('sysdate', verror);
END;

Upvotes: 0

Views: 1939

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You are creating nested if-then-else statements; you have the ELSE and IF on the same line but they are separate levels of control. It might be easier to understand where you're going wrong if arranged with indentation to show the nesting, as:

LOOP
  ...
  IF Instr(Vlocation,'_') >0 THEN
    ...
    IF SUBSTR(Vlocation,1,LENGTH(Consrgsite)) = Consrgsite THEN
      ...
    ELSE
      IF SUBSTR(Vlocation,1,LENGTH(Consrg50site)) = Consrg50site THEN
        ...
      ELSE
        IF Lower(SUBSTR(Vlocation,1,14)) = 'locations_data' THEN
          ...
        ELSE
          IF Lower(SUBSTR(Vlocation,1,14)) = 'locations_data' AND Vchildseq = '0' THEN
            ...
            IF Vsicount = 0 THEN
              ...
            END IF;
          ELSE
            IF Lower(SUBSTR(Vlocation,1,14)) = 'locations_data' AND Vchildseq > '0' THEN
              ...
            ELSE
              IF Lower(SUBSTR(Vlocation,1,5)) = 'ripe_' THEN
                ...
              ELSE
                IF Lower(SUBSTR(Vlocation,1,LENGTH(Conmerchandisesite))) = Conmerchandisesite THEN
                  ...
                ELSE
                  ...
                END IF;
                ...
              END IF;
            END LOOP;

Where you currently have END LOOP it's expecting to see END IF, which would match the IF Lower(SUBSTR(Vlocation,1,14)) = 'locations_data' AND Vchildseq > '0' THEN line.

As Tony Andrews said in a comment you need to have an END IF to match every IF. You currently have six levels of control that are opened with IF but not closed with END IF.

You seem to be expecting it to work like ELSIF, which you can use instead:

LOOP
  ...
  IF Instr(Vlocation,'_') >0 THEN
    ...
    IF SUBSTR(Vlocation,1,LENGTH(Consrgsite)) = Consrgsite THEN
      ...
    ELSIF SUBSTR(Vlocation,1,LENGTH(Consrg50site)) = Consrg50site THEN
      ...
    ELSIF Lower(SUBSTR(Vlocation,1,14)) = 'locations_data' THEN
      ...
    ELSIF Lower(SUBSTR(Vlocation,1,14)) = 'locations_data' AND Vchildseq = '0' THEN
      ...
      IF Vsicount = 0 THEN
        ...
      END IF;
    ELSIF Lower(SUBSTR(Vlocation,1,14)) = 'locations_data' AND Vchildseq > '0' THEN
      ...
    ELSIF Lower(SUBSTR(Vlocation,1,5)) = 'ripe_' THEN
      ...
    ELSIF Lower(SUBSTR(Vlocation,1,LENGTH(Conmerchandisesite))) = Conmerchandisesite THEN
      ...
    ELSE
      ...
    END IF;
  ...
  END IF;
END LOOP;

You could also use a searched CASE expression instead of if-then-elsif-else, but the effect if the same.

Read more about conditional control statements.

Upvotes: 2

Related Questions