In my application data were collected from database (Oracle) using web services that called stored procedures. One of the stored procedure had
SELECT defined similar to the one below:
WITH sth1 as ( SELECT ... ), sth2 as ( SELECT ... ) SELECT ...
This construction was the reason why in my application I was getting the following exception while invoking that web service:
Exception – ORA-00604: error occurred at recursive SQL level 1 ORA-01003: no statement parsed
The simplest solution for that is to create tables representing
sth2 created with
WITH clause. Then instead of using
WITH truncate those tables and fill with data. Finally query against them in the main
Updated, 2008-10-22 It turns out if you use a regular table, you may have some problems with multiple calls to the stored procedure – the second call could overwrite the first call’s data with a second call to truncate.
Therefore use either:
- temporary tables
- inline views