Discussion in 'SQL PL/SQL' started by mdevendh, Aug 9, 2011.
Can anyone tell me the dynamic sql concept with simple example
I presume you're asking about using execute immediate with dynamic strings. It's a complex topic and there are numerous examples that can be given. Basically dynamic sql is used when one needs to create a statement 'on the fly' usually (but not limited to) creating select statements when the table name is not known beforehand:
2 sql_stmt varchar2(4000);
3 tabnm varchar2(35):='EMP';
4 tabrec emp%rowtype;
6 sql_stmt := 'select * from '||tabnm||' where rownum = 1';
8 EXECUTE immediate sql_stmt INTO tabrec;
10 dbms_output.put_line(tabrec.empno||' ' ||tabrec.ename||' '||tabrec.sal||' '||tabrec.hiredate);
7369 SMITH 800 17-DEC-80
PL/SQL PROCEDURE successfully completed.
Remember that execute immediate involves a 'context switch' and the data returned from the dynamic select will not be displayed in the PL/SQL 'window' and must be returned to a variable (as illustrated).
This link may hepl you to get an overview.