Oracle FOR Loop inside a FOR loop

Question

Here is my code. Please forgive me for not putting variables in declaration section as the editor was giving me tough time in formatting it before I could submit my issue.

I want the result variable (v_Var) to have value printed as

v_ID = :NEW.ID;
v_NAME = :NEW.NAME;
v_ENTITY_ID = :NEW.ENTITY_ID;

BUT, it is getting printed as

v_ID = :NEW.ID;
v_ID = :NEW.ID;
v_NAME = :NEW.NAME;
v_ENTITY_ID = :NEW.ENTITY_ID;

Since the table TEMP_TRG_CONSTRNT has 2 rows, it is working for v_ID also for two times. I know the issue is with external FOR loop but I am not sure how to handle it.

DECLARE
    CURSOR c1 IS 
SELECT NAME, OCCUR_COUNT FROM IFMS_SYSTEMCONFIGURATION.TEMP_TRG_CONSTRNT;


BEGIN
    v_TableName := 'MyTable';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE IFMS_SYSTEMCONFIGURATION.TEMP_TRG_CONSTRNT';

    INSERT INTO IFMS_SYSTEMCONFIGURATION.TEMP_TRG_CONSTRNT (NAME, OCCUR_COUNT)
    SELECT A.FKN, COUNT(A.FKN) AS OCCUR_COUNT FROM 
    (
      SELECT A.CONSTRAINT_NAME AS FKN FROM ALL_CONSTRAINTS A
      INNER JOIN ALL_CONS_COLUMNS B
      ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME 
      WHERE  A.CONSTRAINT_TYPE IN ('P', 'U') AND A.TABLE_NAME = 'MyTable'
    )A
    GROUP BY A.FKN;

    --FOR CONSTR_NAME IN (SELECT NAME FROM IFMS_SYSTEMCONFIGURATION.TEMP_TRG_CONSTRNT)
    FOR CONSTR_NAME IN c1
    LOOP
    --SELECT NAME, OCCUR_COUNT INTO v_Constr_Name, v_Index_Count FROM TEMP_TRG_CONSTRNT WHERE NAME = CONSTR_NAME.NAME;
      FOR COL_NAME IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME = CONSTR_NAME.NAME)   
      LOOP
      v_Var := v_Var || 'v_' || COL_NAME.COLUMN_NAME || ' = :NEW.' || COL_NAME.COLUMN_NAME || ';' || CHR(13);
      END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_Var);
    END LOOP;

    END;

Have you tried SELECT DISTINCT inside the for loop to remove duplicates? — Jeremy
– Jeremy, Commented Feb 27, 2017 at 13:29
Basically i have to do FOR EACH instead of FOR on table TEMP_TRG_CONSTRNT — Sachin Vaidya
– Sachin Vaidya, Commented Feb 27, 2017 at 13:33
On further more analysis, I think the issue is where I am construncting my string, for variable v_Var.. someone please suggest a better way — Sachin Vaidya
– Sachin Vaidya, Commented Feb 27, 2017 at 14:13

Boneist · Accepted Answer · 2017-02-27 14:24:14Z

2

There could be a couple of things causing your results:

There are tables in different schemas with the same name (you're missing a join on owner between the two tables)
There could be more than one constraint on the same table.

In addition to those, you've also managed to recreate a Nested Loop join, by doing the nested looping. In general, this is a bad idea - what if a Hash Join was more performant? You will have effectively hobbled Oracle by using your nested cursor for loops. At the very least, you could join your two cursors in a single sql statement before looping through it.

However, it looks like you're trying to generate a list of variables without having to type them. You can do this in a single SQL statement - no need for PL/SQL, like so:

SELECT DISTINCT con.constraint_name,
                con.owner,
                con.table_name,
                col.column_name,
                'v_'||col.column_name||' := :NEW.'||col.column_name||';'
FROM   all_constraints con
       inner JOIN all_cons_columns col ON con.constraint_name = col.constraint_name
                                           AND con.owner = col.owner
                                           AND con.constraint_type IN ('P', 'U')
                                           --AND con.owner = 'SOME_OWNER' -- uncomment out and put in the correct owner name, esp if your table exists in more than one owner.
                                           AND con.table_name = 'YOUR_TABLE'
ORDER BY con.owner, con.table_name;

Note that I've included extra columns, so that you can work out why you're getting the results you're getting, just in case that doesn't match what you're expecting to see. I included the DISTINCT keyword to take care of the case where you have multiple constraints returned for a single owner.table.

If you want to generate a list of variables for multiple tables at once, you might want to use the aggregate listagg function on the above query (meaning you could remove the DISTINCT) with a delimiter of CHR(10).

answered Feb 27, 2017 at 14:24

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

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

2 Comments

Sachin Vaidya Over a year ago

thanks for suggesting the alternate way.. I figured out in my approach, the issue was the way I was constructing the v_Var (output)

Sachin Vaidya Over a year ago

I also like to add that your suggestion is a smarter one.

Collectives™ on Stack Overflow

Oracle FOR Loop inside a FOR loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related