QDA on tables…

codegen When you are implementing the QDA in an existing application, you may want to know what tables have the QDA implemented and what tables don’t have it implemented yet.

I have created two SQL statements that retrieve this information from the data dictionary.

Tables with QDA packages present:

SELECT uo.object_name FROM user_objects uo WHERE 1=1 AND uo.object_type = 'TABLE' AND EXISTS (SELECT 1 FROM user_objects uo2 WHERE 1=1 AND uo2.object_name = uo.object_name || '_TP' AND uo2.object_type = 'PACKAGE' );

Tables without QDA packages present:

SELECT uo.object_name FROM user_objects uo WHERE 1=1 AND uo.object_type = 'TABLE' AND NOT EXISTS (SELECT 1 FROM user_objects uo2 WHERE 1=1 AND uo2.object_name = uo.object_name || '_TP' AND uo2.object_type = 'PACKAGE' );

These queries can also be used to generate code again that already has the QDA in place or generate code for table that don’t yet have the QDA generated yet. In the run window select the object type you want (in this case ‘TABLE’) and in the And Where (Freeform) you put the following (sub) query. 

object_name IN ( SELECT uo.object_name FROM user_objects uo WHERE 1=1 AND uo.object_type = 'TABLE' AND NOT EXISTS (SELECT 1 FROM user_objects uo2 WHERE 1=1 AND uo2.object_name = uo.object_name || '_TP' AND uo2.object_type = 'PACKAGE' ) )

(If you want to regenerate code, then replace the NOT EXISTS by  EXISTS.)

The second tab of the run window then looks like this (image has been edited):

run_codegen_for_non_existant_qda

One thought on “QDA on tables…


Leave a Reply

Your email address will not be published.