Use Oracle's dynamic SQL support

Oracle dynamic SQL

• Dbms_ SQL built-in package
• Native dynamic SQL

Three common functions of native dynamic SQL

• Execute dynamic DDL or DML statements that do not contain Bound variables.
• Execute dynamic DDL or DML containing Bound variables
• Dql support, Data Query Language

Scenario/simple process

Code

Declare tmpstr varchar2 (4000): = NULL; tmpi number (10): = 0; begin for C in (select B. table_name BTN from user_constraints a left join user_constraints B on. owner = B. owner and B. R _ constraint_name =. constraint_name where. owner = 'nstcsa224 'and. table_name = 'cpm _ branch') loop if (C. BTN is not null) Then tmpstr: = 'in in select count (*) into: CC from' | C. BTN | '; end;'; execute immediate tmpstr using out tmpi; dbms_output.put_line ('SQL statement:' | tmpstr); dbms_output.put_line ('table name: '| C. BTN | 'data: '| tmpi); end if; end loop; Exception when others then dbms_output.put_line (sqlerrm); end;

References

1. Oracle Database 10g PL/SQL