{"id":3127,"date":"2019-05-03T23:26:28","date_gmt":"2019-05-03T21:26:28","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3127"},"modified":"2019-05-03T23:26:28","modified_gmt":"2019-05-03T21:26:28","slug":"pl-sql-that-creates-a-table","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3127","title":{"rendered":"PL SQL that creates a table"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The situation is simple. We have to create a table that contain data on a certain variable outcome. Normally, one would like to create a field in the table and add a bunch of records that relate to that outcome. However, the request is to create a new table for that. Examples might be a new table for a certain month, certain country etc.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Reluctantly, we agree and we create a procedure that create a new table for each occurance of a value.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The code looks like:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">set serveroutput on<br> create or replace PROCEDURE SP_VEXISTABLA(TAAK IN VARCHAR)<br> IS<br> stmt varchar2(1000);<br> TELLER NUMBER(3);<br> begin<br> SELECT COUNT(*) INTO TELLER FROM USER_TABLES WHERE TABLE_NAME = 'D' || TAAK;<br> IF (TELLER >0) THEN<br>     execute immediate 'DROP TABLE D' || TAAK;<br> END IF;<br> stmt := q'!CREATE TABLE D!' ||taak|| q'! AS<br>         (SELECT *<br>                 FROM (  SELECT DISTINCT  ding, mandant, rest<br>                 FROM  opdeel<br>                 where trim(ding)=trim('!' ||taak||q'!') and mandant=309<br>                 ORDER BY dbms_random.value)<br>                 WHERE<br>                 rownum &lt;= 2)!';<br> dbms_output.put_line(stmt);<br> execute immediate stmt;<br> end;<br> \/<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The code to run this is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">exec HR.SP_VEXISTABLA('CL');<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The situation is simple. We have to create a table that contain data on a certain variable outcome. Normally, one would like to create a field in the table and add a bunch of records that relate to that outcome. However, the request is to create a new table for that. Examples might be a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3127","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3127","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3127"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3127\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3127"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}