{"id":172,"date":"2007-08-31T14:14:56","date_gmt":"2007-08-31T13:14:56","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=172"},"modified":"2007-08-31T14:14:56","modified_gmt":"2007-08-31T13:14:56","slug":"qda-on-tables","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=172","title":{"rendered":"QDA on tables…"},"content":{"rendered":"

\"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.<\/p>\n

I have created two SQL statements that retrieve this information from the data dictionary.<\/p>\n

<\/p>\n

Tables with<\/strong> QDA packages present:<\/p>\n

\n
SELECT<\/span> uo.<\/span>object_name<\/span> <\/span>FROM<\/span> user_objects uo\n<\/span>WHERE<\/span> <\/span>1<\/span>=<\/span>1<\/span>\n  <\/span>AND<\/span> uo.object_type <\/span>=<\/span> <\/span>'<\/span>TABLE<\/span>'<\/span>\n  <\/span>AND<\/span> <\/span>EXISTS<\/span> (<\/span>SELECT<\/span> <\/span>1<\/span>\n                <\/span>FROM<\/span> user_objects uo2\n               <\/span>WHERE<\/span> <\/span>1<\/span>=<\/span>1<\/span>\n                 <\/span>AND<\/span> uo2.<\/span>object_name<\/span> <\/span>=<\/span> uo.<\/span>object_name<\/span> <\/span>||<\/span> <\/span>'<\/span>_TP<\/span>'<\/span>\n                 <\/span>AND<\/span> uo2.object_type <\/span>=<\/span> <\/span>'<\/span>PACKAGE<\/span>'<\/span>\n             );<\/span><\/div><\/pre>\n
<\/div>\n
Tables without<\/strong> QDA packages present:<\/p>\n<\/p>\n
\n
SELECT<\/span> uo.<\/span>object_name<\/span> <\/span>FROM<\/span> user_objects uo\n<\/span>WHERE<\/span> <\/span>1<\/span>=<\/span>1<\/span>\n  <\/span>AND<\/span> uo.object_type <\/span>=<\/span> <\/span>'<\/span>TABLE<\/span>'<\/span>\n  <\/span>AND<\/span> <\/span>NOT<\/span> <\/span>EXISTS<\/span> (<\/span>SELECT<\/span> <\/span>1<\/span>\n                    <\/span>FROM<\/span> user_objects uo2\n                   <\/span>WHERE<\/span> <\/span>1<\/span>=<\/span>1<\/span>\n                     <\/span>AND<\/span> uo2.<\/span>object_name<\/span> <\/span>=<\/span> uo.<\/span>object_name<\/span> <\/span>||<\/span> <\/span>'<\/span>_TP<\/span>'<\/span>\n                     <\/span>AND<\/span> uo2.object_type <\/span>=<\/span> <\/span>'<\/span>PACKAGE<\/span>'<\/span>\n                 );<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
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)<\/em> you put the following (sub) query. <\/em><\/p>\n<\/p>\n
\n
object_name<\/span> <\/span>IN<\/span> (\n<\/span>SELECT<\/span> uo.<\/span>object_name<\/span> <\/span>FROM<\/span> user_objects uo\n<\/span>WHERE<\/span> <\/span>1<\/span>=<\/span>1<\/span>\n  <\/span>AND<\/span> uo.object_type <\/span>=<\/span> <\/span>'<\/span>TABLE<\/span>'<\/span>\n  <\/span>AND<\/span> <\/span>NOT<\/span> <\/span>EXISTS<\/span> (<\/span>SELECT<\/span> <\/span>1<\/span>\n                    <\/span>FROM<\/span> user_objects uo2\n                   <\/span>WHERE<\/span> <\/span>1<\/span>=<\/span>1<\/span>\n                     <\/span>AND<\/span> uo2.<\/span>object_name<\/span> <\/span>=<\/span> uo.<\/span>object_name<\/span> <\/span>||<\/span> <\/span>'<\/span>_TP<\/span>'<\/span>\n                     <\/span>AND<\/span> uo2.object_type <\/span>=<\/span> <\/span>'<\/span>PACKAGE<\/span>'<\/span>\n                 )\n               )\n<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
(If you want to regenerate code, then replace the NOT EXISTS<\/em> by  EXISTS<\/em>.)<\/font><\/p>\n
The second tab of the run window then looks like this (image has been edited):<\/p>\n
\"run_codegen_for_non_existant_qda\"<\/a><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,2,9],"tags":[],"class_list":["post-172","post","type-post","status-publish","format-standard","hentry","category-codegen","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=172"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/172\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}