Unit Testing With Complex Dynamic Query in PL/SQL - Oracle

Dynamic queries are complex sometimes and it is very hard to debug them. Here is a simple approach to get the dynamic queries and debug them.

I am taking a very simple dynamic query while you can test it with complex one.

Its Global table approach to get dynamic query as static query and then debug the query in SQL window.

So lets start with a dynamic query.

DECLARE
vsql varchar(4000) := '';
vResult varchar2(1000) := '';
v_name varchar(100) := 'My_Name';

BEGIN

vsql:='insert into temp_table(id, name)  values(1, '''|| v_name ||''' )';
  delete from test_sql;
  insert into test_sql values(vsql);      
  commit;
EXECUTE IMMEDIATE VSQL ;
END;

The above Given dynamic query Program will execute and insert the constructed query into test_sql table.

To get this Static query you just have to first run the your procedure/function and then execute

select * from test_sql

you will get static query in results

When this dynamic query is is converted in static one at runtime it looks like:

insert into temp_table(id, name)  values(1, 'My_Name' );

You just copy and paste the given query into your SQL window and you can debug if there is any syntax error or logical error and you can change the corresponding things in your dynamic query.