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
Solution
The simplest solution for that is to create tables representing sth1 and sth2 created with WITH clause. Then instead of using WITH truncate those tables and fill with data. Finally query against them in the main SELECT.
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
