Using Execute Immediate (Dynamic SQL)

The following example is used to get/compare the record count from one database to the other database. I am posting this for the beginners who wants to know the usage of dynamic SQL.

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance. 

Example 1:

create or replace function son_created_records(p_table_name in varchar2,p_on_date in date) return number is

l_sql varchar2(200);

l_error varchar2(300);

l_result number :=0;

l_table_name varchar2(100);

l_date date;

begin

l_table_name := upper(p_table_name);

l_date := p_on_date;

l_sql := 'select count(*) from '||l_table_name||' where trunc(CREATION_DATE) = trunc(TO_DATE('||''''||l_date||''''||','||''''||'DD-MON-RRRR'||''''||'))';

execute immediate l_sql into l_result;

return l_result;

exception when others then

l_error:= substr(sqlerrm,1,200);

return 9999; -- If you predefine 9999 as error code

--dbms_output.put_line('Error is -'||l_error);

end son_created_records;

If anybody wants to check how many users have created on a particular date (SYSDATE here) in different instances. 

> select son_created_records('fnd_users',SYSDATE) from dual

If anybody wants to check how many expense reports have raised on a particular date (say 15th Jan, 2011 here)in different instances.

> select son_created_records('ap_expense_report_headers_all','15-JAN-2011') from dual

Example 2:

To be contd....

Thanks,

Rajesh