oracle - Cursor Operation in Netezza -

create or replace procedure sp_new_procedure1( ) returns reftable(employees) language nzplsql begin_proc   declare      l_conditions  varchar(1000);     p_rec         record;  begin      p_rec in  select empid, mgrid, empname, salary  employees mgrid = 7      loop               l_conditions  :=  'insert  '  ||                                reftablename     ||                                '  values ('     ||                                p_rec.empid      ||                                ','              ||                                p_rec.mgrid      ||                                ','              ||                                p_rec.empname    ||                                ','              ||                                p_rec.salary     ||                                '  ) ; ' ;       execute immediate l_conditions;         l_conditions  := ' ';      end loop;           return reftable; end; end_proc; 

when run this:

select sp_new_procedure1() 

i errors:

error [01000] notice:  error occurred while executing pl/pgsql function sp_new_procedure1 error [01000] notice:  line 24 @ execute statement error [42s22] error:  attribute 'dan' not found 

can whats wrong ...thanks

this has nothing cursor itself, , how building dynamical sql string.

when building dynamic sql in netezza stored procedure, can use quote_ident , quote_literal helper functions let system know whether passing literal, or whether passing identifier. there example in online documentation here. figure out escaped quotation notation needed.

since trying put values stored in columns of p_rec record values part of insert statement, use quote_literal this:

create or replace procedure sp_new_procedure1( ) returns reftable(employees) language nzplsql begin_proc   declare  l_conditions  varchar(1000); p_rec         record;  begin  p_rec in  select empid, mgrid, empname, salary  employees mgrid = 7  loop           l_conditions  :=  'insert  '  ||                            reftablename     ||                            '  values ('     ||                            quote_literal(p_rec.empid)  ||                            ','              ||                            quote_literal(p_rec.mgrid)    ||                            ','              ||                            quote_literal(p_rec.empname)   ||                            ','              ||                            quote_literal(p_rec.salary )    ||                            '  ) ; ' ;   execute immediate l_conditions;     l_conditions  := ' ';  end loop;       return reftable; end; end_proc; 

that being said, using cursor loop on records insert row 1 @ time horribly inefficient in mpp database netezza. assuming question follow-on question alternative recursive cte explore hierarchies, there's nothing wrong looping in general, try avoid doing record record. here version exploit mpp nature of system. record, if going return result set reftable, choice dynamic sql.

create or replace procedure sp_new_procedure1( ) returns reftable(employees) language nzplsql begin_proc   declare      l_conditions  varchar(1000);     p_rec         record;  begin  --    p_rec in  select empid, mgrid, empname, salary  employees mgrid = 7  --    loop               l_conditions  :=  'insert  '  ||                                reftablename     ||                                '  select empid, mgrid, empname, salary  employees mgrid = 7 ; ' ;       execute immediate l_conditions;         l_conditions  := ' ';  --    end loop;           return reftable; end; end_proc;