{"id":135,"date":"2016-07-20T14:12:18","date_gmt":"2016-07-20T14:12:18","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=135"},"modified":"2016-07-20T14:12:18","modified_gmt":"2016-07-20T14:12:18","slug":"synthetic-data-nice-little-sql-feature","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=135","title":{"rendered":"Synthetic data &#8211; nice little SQL feature"},"content":{"rendered":"<p>This blog was inspired by a Twitter conversation between Jeffrey Kemp and Franck Pachot about little SQL tricks.<br \/>\nCountless times I&#8217;ve created synthetic data to model a case or just play around in general. For quick and dirty and small volumes I prefer &#8220;CONNECT BY&#8221;. When doing it properly, I use recursive CTEs.<\/p>\n<p>But this puts a smile on my face every time I look at it.<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\nselect\r\n    item.getNumberVal() num\r\nfrom xmltable('1 to 10,20,30,40 to 50') item\r\n;\r\n\r\n       NUM\r\n----------\r\n\t 1\r\n\t 2\r\n\t 3\r\n\t 4\r\n\t 5\r\n\t 6\r\n\t 7\r\n\t 8\r\n\t 9\r\n\t10\r\n\t20\r\n\t30\r\n\t40\r\n\t41\r\n\t42\r\n\t43\r\n\t44\r\n\t45\r\n\t46\r\n\t47\r\n\t48\r\n\t49\r\n\t50\r\n\r\n23 rows selected.\r\n<\/pre>\n<p>Here&#8217;s what I usually start with as a base and then tweak the script to my current needs.<\/p>\n<pre class=\"brush: sql; collapse: false; title: ; wrap-lines: false; notranslate\" title=\"\">\r\ndrop table t1 purge;\r\ncreate table t1 (\r\n    id number\r\n  , num_small number (5, 0)\r\n  , num_big number\r\n  , vc2_short varchar2(200 byte)\r\n  , vc2_long varchar2(4000 byte)\r\n  , dt date\r\n  , cl clob\r\n)\r\n  tablespace users\r\n  lob (cl) store as securefile t1_cl_seg (\r\n    tablespace users\r\n    disable storage in row\r\n    retention auto\r\n    nocache logging\r\n    chunk 32K)\r\n;\r\n \r\ninsert \/*+ append *\/ into t1 (id, num_small, num_big, vc2_short, vc2_long, dt)\r\nwith datagen as (\r\n  select --+ materialize\r\n      rownum as id\r\n  from\r\n      dual\r\n  connect by level &lt;= 1e4\r\n)\r\nselect\r\n    rownum as id\r\n  , trunc(dbms_random.value(1, 100)) as num_small\r\n  , round(dbms_random.value(10000, 1000000), 4) as num_big\r\n  , dbms_random.string('L', trunc(dbms_random.value(10, 200))) as vc2_short\r\n  , dbms_random.string('A', trunc(dbms_random.value(200, 4000))) as vc2_long\r\n  , trunc(sysdate + dbms_random.value(0, 366)) as dt\r\nfrom\r\n    datagen dg1\r\n  , datagen dg2\r\nwhere\r\n    rownum &lt;= 1e4\r\n;\r\n \r\ncommit;\r\n \r\n&lt;&lt;populate_lob&gt;&gt;\r\ndeclare\r\n  num_rows number;\r\n  cl clob;\r\n  chunk_size integer := 1024; -- 1 KB\r\n  max_cl_size integer := 8192; -- 8 KB\r\nbegin\r\n  select count(*) cnt\r\n  into num_rows\r\n  from t1\r\n  ;\r\n  \r\n  for row_num in 1 .. num_rows loop\r\n    dbms_lob.createtemporary(cl, false, dbms_lob.call);\r\n \r\n    for idx in 1 .. trunc(dbms_random.value(1, (max_cl_size \/ chunk_size))) loop\r\n      dbms_lob.writeappend(cl, chunk_size, dbms_random.string('A', chunk_size));\r\n      null;\r\n    end loop;\r\n \r\n    update t1\r\n      set t1.cl = populate_lob.cl\r\n    where id = row_num\r\n    ;\r\n    commit;\r\n \r\n    dbms_lob.freetemporary(cl);\r\n  end loop;\r\nend populate_lob;\r\n\/\r\n \r\n--alter table t1 modify lob (cl) (cache logging);\r\n<\/pre>\n<p>I hope above little trick amuses you as much as it did me \ud83d\ude42<br \/>\nImagine, it made me blog about it!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This blog was inspired by a Twitter conversation between Jeffrey Kemp and Franck Pachot about little SQL tricks. Countless times I&#8217;ve created synthetic data to model a case or just play around in general. For quick and dirty and small volumes I prefer &#8220;CONNECT BY&#8221;. When doing it properly, I use recursive CTEs. But this [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-135","post","type-post","status-publish","format-standard","hentry","category-general"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=135"}],"version-history":[{"count":2,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/135\/revisions"}],"predecessor-version":[{"id":137,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/135\/revisions\/137"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}