{"id":749,"date":"2022-09-15T10:40:59","date_gmt":"2022-09-15T10:40:59","guid":{"rendered":"http:\/\/wp.chaoyu.nl\/?p=749"},"modified":"2022-09-16T14:06:42","modified_gmt":"2022-09-16T14:06:42","slug":"chunk-blob-column-into-string-associative-array-for-data-deployment","status":"publish","type":"post","link":"https:\/\/chaoyu.nl\/?p=749","title":{"rendered":"CHUNK BLOB column into String Associative Array for data deployment"},"content":{"rendered":"\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">Declare\n   Cursor r_sdvx Is\n      Select t.xsd_name\n            ,t.xsd_content_blob\n        From cms_setup_declaration_version_xsds_cu t\n       Where t.xsd_name = 'DMS.Declaration_1p10.xsd';\n   l_rec      r_sdvx%Rowtype;\n   l_lsize    Number;\n   l_bsize    Number := 1005;\n   t_varchars dbms_sql.varchar2_table;\n   Procedure pl##(i_what In Varchar2) Is\n   Begin\n      dbms_output.put_line(i_what);\n   End pl##;\nBegin\n   Open r_sdvx;\n   Fetch r_sdvx\n      Into l_rec;\n   Close r_sdvx;\n   l_lsize := dbms_lob.getlength(l_rec.xsd_content_blob);\n   For ii In 0 .. ceil((l_lsize \/ l_bsize) - 1)\n   Loop\n      t_varchars(ii + 1) := dbms_lob.substr(l_rec.xsd_content_blob\n                                           ,l_bsize\n                                           ,ii * l_bsize + 1);\n   End Loop;\n   -- add template blob to script\n   pl##('wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;');\n   For ii In 1 .. t_varchars.count\n   Loop\n      pl##('  wwv_flow_api.g_varchar2_table(' || ii || ') := ''' || t_varchars(ii) || ''';');\n   End Loop;\nEnd;\n\/<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code has-small-font-size\"><code lang=\"sql\" class=\"language-sql line-numbers\">Declare l_blob Blob;\nBegin\n   wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;\n   wwv_flow_api.g_varchar2_table(1) := '3C3F786D6C2076657273696F6E3D22312E302220656E ';\n   l_blob := wwv_flow_api.varchar2_to_blob(p_varchar2s =&gt; wwv_flow_api.g_varchar2_table);\n   Insert Into cms_setup_declaration_version_xsds_cu\n      (xsd_name\n      ,sdve_cu_id\n      ,created_on\n      ,created_by\n      ,xsd_content_blob)\n   Values\n      ('ISO3AlphaCurrencyCode_2012-08-31.xsd'\n      ,1\n      ,current_timestamp\n      ,User\n      ,l_blob);\n   Commit;\nEnd;\n\n\/<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":752,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-749","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pl-sql"],"_links":{"self":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/749","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=749"}],"version-history":[{"count":3,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/749\/revisions"}],"predecessor-version":[{"id":753,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/posts\/749\/revisions\/753"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=\/wp\/v2\/media\/752"}],"wp:attachment":[{"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=749"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=749"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chaoyu.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=749"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}