Arif75 wrote:Dynamic SQL can be used in situations where it avoids many, many variations on the same structured SQL for different circumstances. The most common use I see is in reporting where users want data filtered or sorted by different criteria depending on circumstances - it allows individual clauses to be used as needed.
Can someone explain me what is Dynamic Sql in a precise way with some examples and when to use it.
declare vstr varchar2(80); begin vstr:= 'CREATE TABLE t1 (v1 number(10))'; execute immediate vstr; end; or begin execute immediate 'CREATE TABLE t1 (v1 number(10))'; end; eg. declare vstr varchar2(80); c1 number(10); n1 number(10); begin vstr := 'select * from emp'; c1:= dbms_sql.open_cursor; dbms_sql.parse(c1,vstr,'native'); n1 := dbms_sql.execute(c1) end
N_i_R_v_A_n_A wrote:I would consider each table that is created "on the fly" as a big programmer bug. Everybody who does such a thing, clearly has no proper understanding what a database table is good for.
It can be used for a lot of things ,but we have used it often to create a table on the fly in the PL/SQL block,