Dynamic SQL is very tricky. Since you can get the data outside your application extract and test the dynamic SQL as it is generated. When working with dynamic SQL I find it useful to first generate the SQL text as a string and then if necessary store it in a table (CLOB type) for later reference. Because of the uncertainty of obtaining bind variable values later I prefer to hard-code such values into dynamic SQL - possibly slightly less efficient for Oracle at run time but a lot easier to work with when debugging and tuning (again, apart from the built-in inefficiency of not using bind variables) I usually find the overhead of the hard-coded values not too bad and the ability to know the data values at a glance useful. I also find it useful to write dynamic SQL in such a way so that the resulting statement can be pasted in to any normal SQL tool (SQL*PLUS, SQL*Developer) and run without any editing.
There may be some quirk in the generated dynamic SQL preventing it from finding anything.
Thank you for your reply.
Unfortunately my question is not about dynamic sql in general. Maybe I expressed my concern ambiguously. Sorry for that. That's because of my averaging english skills. It's about the unit testing feature of SQL Developer which you can find under Tools > Unit Test. If I run the test as explained above, I get an error message. In contradiction to this running the statements of the test startup process outside the testing framework and running the function afterwards completes successfully. So I conclude that there is a problem in Dynamic Value Query (part of test definition) with getting data from tables which where filled during startup process. And I would like to know if there is anyone who made similar experiences?