I am planing a function which should return a table name for a given ROWID. The ROWID will be selected from a view. Now I am trying to setup a unit test which consists of Startup and Teardown Process as well as a Dynamic Value Query. During Startup Process one dataset is inserted in a table which is part of a view definition. The dataset should be selected during Dynamic Value Query. However I am getting the following error message: Unable to run the test because no rows were returned by the dynamic query. Is there the possibility that within a Dynamic Value Query I can't make use of datasets which where inserted by a Startup Process? I am asking because the selection outside the unit testing framework supplies the dataset as expected.
SQL Developer: 3.2.20.09 Build MAIN-09.87
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?