Execute immediate in netezza stored procedure is not inserting value to a table

Question

When I am running this Netezza stored procedure, I am getting an error

attribute 'SOME_VALUE' not found

As per requirement I have to get value from one table (TABLE_A) and insert into another table (TABLE_B).

This is the procedure:

create or replace procedure my_proc()
returns boolean 
execute as owner
language NZPLSQL
as
BEGIN_PROC
    declare rec RECORD ;
BEGIN
    for rec in SELECT * from TABLE_A loop
    EXECUTE IMMEDIATE 
    'INSERT INTO TABLE_B(COLUMN_B) 
     values( '||     rec.COLUMN_A_OFTABLE_A ||  ')';
END LOOP;
END;
END_PROC;

execute my_proc()

Here below, I am able to insert a string. But I need to insert different value depending on other table as I mentioned above.

EXECUTE IMMEDIATE 'INSERT INTO TABLE_B(COLUMN_B) values( ''Y'');';

Answer 1

When building a string that you are going run EXECUTE IMMEDIATE against, you have be careful to have everything quoted properly. In your case it's thinking that it needs to treat SOME_VALUE as an attribute/column, and it can't any column with that name.

Wrap your column reference in quote_literal() and it will interpret the contents of your column and quote-escape it properly for you.

create or replace procedure my_proc()
returns boolean 
execute as owner
language NZPLSQL
as
BEGIN_PROC
    declare rec RECORD ;
BEGIN
    for rec in SELECT * from TABLE_A loop
    EXECUTE IMMEDIATE 
    'INSERT INTO TABLE_B(COLUMN_B) 
     values( '||  quote_literal(rec.COLUMN_A_OFTABLE_A) ||  ')';
END LOOP;
END;
END_PROC;

You can find some more information in the documentation here.

Note: I am assuming that you have some more complicated logic to implement in this stored procedure, because looping over row by row will be much, much slower that insert..select. Often by an order of magnitude.