execute immediate insert !!

[dbasupuser]

Is it possible to use execute immediate "insert into table select distinct values
inside the loop.

Eg:

for c_tab_col_rec in c_tab_col loop
execute immediate 'insert into ABC(distinct_val)
select distinct '||c_tab_col_rec.column_name||' from '||c_tab_col_rec.table_name;
end loop;

[tabreaz]

Try it out, if you have any problems then post the errors

[dbasupuser]

execute immediate 'insert into ABC(distinct_val) ' ||
' select distinct '||c_column_name_rec.column_name||' from yyy.'||c_table_name_rec.table_name;

ERROR

ORA-00904: "SHORT_DISPLAY": invalid identifier
ORA-06512: at line 22

I'm not using SHORT_DISPLAY ANYWHERE IN THE CODE.

[tabreaz]

Print that string, using dbms_output package, check if the column name or table name have any invalid character.

The problem may also be other, if you can provide that stored procedure then someone can help you.

[gamyers]

Maybe your c_column_name_rec is out of step with c_table_name_rec