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.
