How to use Insert query within for loop in PLSQL using implicit cursor

Question

I have an existing table and I want to add some entry in another table for each row of the given first table. I am writing my PLSQL command as:

BEGIN
    FOR record in (select cola_guid, hapc_guid, tar_guid from tabA) LOOP
            
            select count(*) INTO v_record_exists
                    from p where
                    p.cola_guid = record.cola_guid;

            IF v_record_exists = 0 THEN
                    execute immediate 'insert into NTABLE (tar_guid, PC_NAE, PCV) values (record.tar_guid, ' || '''abcd''' || ', ' || '''val1''' || ')';
            ELSE
                    execute immediate 'insert into NTABLE (tar_guid, PC_NAE, PCV) values (record.tar_guid, ' || '''abcd''' || ', ' || '''val2''' || ')';
            END IF;

            execute immediate 'insert into NTABLE (tar_guid, PC_NAE, PCV) values (record.tar_guid, ' || '''RA_hapc_guid''' || ', record.hapc_guid)';
            execute immediate 'insert into NTABLE (tar_guid, PC_NAE, PCV) select record.tar_guid, PC_NAE, PCV from  p where record.cola_guid = p.cola_guid and PC_NAE = ' || '''propVal''' || ' ';
    END LOOP;
END;

Now I am getting error:

ORA-00984: column not allowed here

in line:

execute immediate 'insert into NTABLE (tar_guid, PC_NAE, PCV) values (record.tar_guid, ' || '''abcd''' || ', ' || '''val1''' || ')';

I am new to PLSQL world but I really tried triaging and googling but wasn't able to resolve. Please guide and help.

Answer 1

There is no need for you to use dynamic sql here - you know all the columns and tables you're inserting/selecting from, so you can simply use the PL/SQL variables directly in the SQL statement.

Also, when you're writing SQL inside PL/SQL, for performance reasons (as well as easy to read, maintain and debug) you should think set based.

It's entirely possible to do all your inserts in a single insert statement, which you can put inside a procedure.

BEGIN
  INSERT INTO ntable (tar_guid, pc_nae, pcv)
  WITH results AS (SELECT t.cola_guid,
                          t.hapc_guid,
                          t.tar_guid,
                          CASE WHEN EXISTS (SELECT NULL FROM p WHERE p.cola_guid = t.cola_guid) THEN 'val1' ELSE 'val2' END val
                   FROM   taba t)
  SELECT tar_guid,
         'abcd' pc_nae,
         val pcv
  FROM   results
  UNION ALL
  SELECT tar_guid,
         'RA_hapc_guid' pc_nae
         hapc_guid pcv
  FROM   results
  UNION ALL
  SELECT p.tar_guid,
         p.pc_nae,
         p.pcv
  FROM   results r
         inner JOIN p ON r.cola_guid = p.cola_guid
  WHERE  p.pc_nae = 'propVal';
END;
/

Don't forget you'll need to commit/rollback as required!