PL SQL Nested IF ELSE with Cursor

Question

I have three cursors declared CUR_AD1, CUR_AD1_C, CUR_AD_2. All they contain is result coming from a 3 mere select statements which works fine.

Later on I have the following statements:

BEGIN

    FOR AD1_REC IN CUR_AD1 LOOP

      V_ORG_NAME := AD1_REC.ADDRESS_LINE1;


    IF V_ORG_NAME = AD1_REC.ADDRESS_LINE1 THEN
        INSERT INTO DSOPI_PERSON_ADDR_RULE 
        (CCTR_PERSON_ADDRESS_ID, CCTR_PERSON_ID, SRC_ADDRESS_LINE1, SRC_ADDRESS_LINE2, SRC_ADDRESS_LINE3, SRC_ADDRESS_LINE4, 
        ORG_NAME, DEPT_NAME, TGT_ADDRESS_LINE1, TGT_ADDRESS_LINE2, TGT_ADDRESS_LINE3, TGT_ADDRESS_LINE4, 
        STATE, CITY, COUNTRY_NAME, ZIP_CODE, EXTRACT_DATE) 
        VALUES
        (AD1_REC.CCTR_PERSON_ADDRESS_ID, AD1_REC.CCTR_PERSON_ID, AD1_REC.ADDRESS_LINE1, AD1_REC.ADDRESS_LINE2, AD1_REC.ADDRESS_LINE3, AD1_REC.ADDRESS_LINE4, 
         V_ORG_NAME, '', '', AD1_REC.ADDRESS_LINE2, AD1_REC.ADDRESS_LINE3, AD1_REC.ADDRESS_LINE4, 
         AD1_REC.STATE, AD1_REC.CITY, AD1_REC.COUNTRY_NAME, AD1_REC.ZIP_CODE, SYSDATE);

    ELSIF V_ORG_NAME = AD1_REC.ADDRESS_LINE2 THEN
      FOR AD2_REC IN CUR_AD2 LOOP

        INSERT INTO DSOPI_PERSON_ADDR_RULE 
        (CCTR_PERSON_ADDRESS_ID, CCTR_PERSON_ID, SRC_ADDRESS_LINE1, SRC_ADDRESS_LINE2, SRC_ADDRESS_LINE3, SRC_ADDRESS_LINE4, 
        ORG_NAME, DEPT_NAME, TGT_ADDRESS_LINE1, TGT_ADDRESS_LINE2, TGT_ADDRESS_LINE3, TGT_ADDRESS_LINE4,
        STATE, CITY, COUNTRY_NAME, ZIP_CODE, EXTRACT_DATE) 
        VALUES
        (AD2_REC.CCTR_PERSON_ADDRESS_ID, AD2_REC.CCTR_PERSON_ID, AD2_REC.ADDRESS_LINE1, AD2_REC.ADDRESS_LINE2, AD2_REC.ADDRESS_LINE3, AD2_REC.ADDRESS_LINE4, 
         AD2_REC.ADDRESS_LINE2, NULL, AD2_REC.TGT_ADDRESS_LINE1, NULL, AD2_REC.ADDRESS_LINE3, AD2_REC.ADDRESS_LINE4, 
         AD2_REC.STATE, AD2_REC.CITY, AD2_REC.COUNTRY_NAME, AD2_REC.ZIP_CODE, SYSDATE);
         END LOOP;
         END IF;

END LOOP;                
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR' || SQLERRM);
RAISE;    
END;

When I compile the body I keep getting the following errors:

PL/SQL: SQL Statement ignored PLS-00302: component 'ZIP_CODE' must be declared PL/SQL: ORA-00984: column not allowed here

There is no problem with the tables whatsoever. I think it has to do something with the how I am putting my control statements. Any help is highly appreciated.

DDL for Table DSOPI_PERSON_ADDR_RULE

  CREATE TABLE "CALSEED_OWNER"."DSOPI_PERSON_ADDR_RULE" 
   (    "CCTR_PERSON_ADDRESS_ID" NUMBER(12,0), 
    "CCTR_PERSON_ID" NUMBER(12,0), 
    "SRC_ADDRESS_LINE1" VARCHAR2(100 BYTE), 
    "SRC_ADDRESS_LINE2" VARCHAR2(100 BYTE), 
    "SRC_ADDRESS_LINE3" VARCHAR2(100 BYTE), 
    "SRC_ADDRESS_LINE4" VARCHAR2(100 BYTE), 
    "ORG_NAME" VARCHAR2(100 BYTE), 
    "DEPT_NAME" VARCHAR2(100 BYTE), 
    "TGT_ADDRESS_LINE1" VARCHAR2(100 BYTE), 
    "TGT_ADDRESS_LINE2" VARCHAR2(100 BYTE), 
    "TGT_ADDRESS_LINE3" VARCHAR2(100 BYTE), 
    "TGT_ADDRESS_LINE4" VARCHAR2(100 BYTE), 
    "STATE" VARCHAR2(100 BYTE), 
    "CITY" VARCHAR2(100 BYTE), 
    "COUNTRY_NAME" VARCHAR2(100 BYTE), 
    "ZIP_CODE" VARCHAR2(100 BYTE), 
    "EXTRACT_DATE" DATE, 
    "STREET" VARCHAR2(100 BYTE), 
    "STREET_CONTD" VARCHAR2(100 BYTE)

CURSOR CUR_AD2 IS         
        SELECT CCTR_PERSON_ADDRESS_ID, 'AL2' AL,ADDRESS_LINE2 
        FROM STG_RT2_PERSON_ADDRESS WHERE UPPER(ADDRESS_LINE2) IN 
        ( SELECT UPPER(ORG_NAME) FROM STG_RT2_ORGANIZATION
        WHERE NOT REGEXP_LIKE(ORG_NAME, '[0-9]') 
        AND NOT (LOWER(ORG_NAME) LIKE 'unknown' OR LOWER(ORG_NAME) LIKE 'no address%'
        OR ORG_NAME ='-' OR ORG_NAME=' '));

Logic seems strange to start with. V_ORG_NAME is set to AD1_REC.ADDRESS_LINE1, then immediate compared to the same value. If this is supposed to be a NULL check, the perhaps usage of IS NULL would be more clear. Also, to debug, don't catch and raise the exception, then you will see the line number. Remove the IF statement altogether and try both statements individually to see which (or if both) causes grief. Probably need to see the definitions as well. — Glenn
– Glenn, Commented Oct 18, 2012 at 3:11
perhaps you are right about the null check thought. this code was written by another colleague of mine so I am not sure why he did that. But from what you are saying it seems i need to put equality check outside of the loop? Correct me if I am wrong. — Bytekoder
– Bytekoder, Commented Oct 18, 2012 at 3:24
Can you post a describe of DSOPI_PERSON_ADDR_RULE and the cursors? — Roger
– Roger, Commented Oct 18, 2012 at 4:14
As Roger said, please post the description of the cursors - CUR_AD2, to be precise. — Jeffrey Kemp
– Jeffrey Kemp, Commented Oct 18, 2012 at 4:44

Roger · Accepted Answer · 2012-10-18 05:16:43Z

2

For debugging purposes you can use

DBMS_UTILITY.format_error_backtrace;

Make sure all alias in your cursors (not only your table) match the columns you are trying to access e.g.

FOR x IN SELECT zip_code zipCode FROM myTable LOOP
    dbms_output.put_line(x.zip_Code)
END LOOP;

The example breaks because x contains zipCode instead of zip_code.

With the information you provided, we can't give any further help, post a describe of your table aswell as your cursors declaration or make a compilable example.

This is useful http://sqlfiddle.com/

Edit

The problem is your cursor declaration

  SELECT
    CCTR_PERSON_ADDRESS_ID,
    'AL2' AL,
    ADDRESS_LINE2,
    ZIP_CODE -- ADD THIS FOR THE ZIP CODE, YOU NEED TO ADD ALL THE OTHER COLUMNS ASWELL...
 FROM
    STG_RT2_PERSON_ADDRESS
  WHERE
    UPPER(ADDRESS_LINE2) IN
    (
      SELECT
        UPPER(ORG_NAME)
      FROM
        STG_RT2_ORGANIZATION
      WHERE
        NOT REGEXP_LIKE(ORG_NAME, '[0-9]')
      AND NOT
        (
          LOWER(ORG_NAME) LIKE 'unknown'
        OR LOWER(ORG_NAME) LIKE 'no address%'
        OR ORG_NAME ='-'
        OR ORG_NAME =' '
        )
    );

Every CUR_AD_2 is a record on your result set, therefore it has its same number of columns and names, in your query, you have only 3 columns but you are trying to access a lot of them (like zip code) that are not in the cursor; make sure to add ZIP_CODE and all the other columns needed. Or just delete them from the INSERT statement

edited Oct 18, 2012 at 5:16

answered Oct 18, 2012 at 5:08

Roger

2,9624 gold badges32 silver badges40 bronze badges

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

3 Comments

Bytekoder Over a year ago

What I have realized is its not the zip code. Even lets say I remove zip code the error is back again pointing at the second last column. I think there is some control statement issue after i start the second loop.

Bytekoder Over a year ago

Now the moment I add TGT_ADDRESS_LINE1 column, it says that loop index variable 'AD2_REC' use is invalid. Any ideas?

Roger Over a year ago

let us continue this discussion in chat

Tribhuwan · Accepted Answer · 2012-10-18 04:12:46Z

0

Please check zip_code spelling matchecs column name in the table

answered Oct 18, 2012 at 4:12

Tribhuwan

1801 gold badge1 silver badge11 bronze badges

1 Comment

Bytekoder Over a year ago

Its not the zip code even if I remove that the error pointer goes to next last column... I think its the whole block.

Collectives™ on Stack Overflow

PL SQL Nested IF ELSE with Cursor

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related