Toad World® Forums

How to test simple SQL steps in code tester ?

Hi All,

About [80-90]% of my code comprises of simple SQL statements [ Loading data into a destination table from group of source tables ] and anonymous PLSQL’s . Can I use toad code tester for testing out my scripts ?

As i see , toad code tester can only be used to test Named PLSQL. T

Thanks,

Venu

Hi,

Actually Code Tester is made for regular testing of the stored source code as you found out. However, Code Tester also can be used in some additional cases with exploiting the customization options.

I was able to test this SQL statement as a proof of concept:

create table local_assertions as select * from QU_ASSERTION;

As a test I chose the "Specified object exists in the database?" I will explain how to do it in following steps.

  1. I created a stored procedure which is required by Code Tester. The source code does nothing, we only need this procedure to create a test definition. CREATE OR REPLACE PROCEDURE PRC_TESTER_ANON
    IS
    BEGIN
    NULL;
    END PRC_TESTER_ANON;
    /
  2. I created a test definition to run tests lately.
  3. In the customization section of the test case, we are allowed to write the SQL statement. However, it is required to be wrapped in the PL/SQL test code, so we need to transform it to an executable statement. I used execute immediate:
  4. Now I have a test of the SQL statement in the test definition for procedure PRC_TESTER_ANON and I can run it whenever I want.
    If you want to change the tested statement or the test, you can edit the test definition - test case. Or you can add another test case and thus be able to test your old code repeatedly.

If you are interested and need help with a specific statement using this work around, please don't hesitate to write again.

Best regards,

Ferdinand

Thanks Ferdinand !