{"id":3225,"date":"2020-01-07T22:05:01","date_gmt":"2020-01-07T21:05:01","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3225"},"modified":"2020-01-07T22:05:01","modified_gmt":"2020-01-07T21:05:01","slug":"change-strings-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3225","title":{"rendered":"Change strings in Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">PL\/SQL with the utl_file package allows us to manipulate strings in Oracle. One may read a string from a file, modify this and subsequently write it to another file. This opens nice possibilities.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let me show some code how to do this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DECLARE\nv_file_ptr utl_file.file_type;\nv_file_handler utl_file.file_type;\nv_line varchar2(2000);\nBEGIN\nv_file_ptr := utl_file.fopen('EXTERN','namen.txt','r');\nv_file_handler:= utl_file.fopen('EXTERN','uitvoer.txt','w');\nLOOP \nBEGIN\nutl_file.get_line(v_file_ptr,v_line);\nutl_file.put_line(v_file_handler,'uitvoer &gt;&gt; ' || v_line);\ndbms_output.put_line(v_line);\nEXCEPTION WHEN NO_DATA_FOUND THEN EXIT; \nEND ;\nEND LOOP;\nutl_file.fclose(v_file_ptr);\nutl_file.fclose(v_file_handler);\nEXCEPTION WHEN OTHERS THEN\ndbms_output.put_line(sqlcode||sqlerrm);\nEND;\n\/<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">First, we open two file handlers: one to file from which we read and another to which some will be written. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Subsequently, a loop starts that reads a file, line by line. Each line is manipulated and subsequently written to another file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Finally the file handlers are closed.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This technique can also be used to create a simple HTML report:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2020\/01\/Capture-17-1-1-1.png\" alt=\"\" class=\"wp-image-3228\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The actual query is &#8220;select first_name, last_name from employees&#8221;. The results are outputted is a for i loop. The results are written to file with HTML mark ups.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For more information, see  <a href=\"https:\/\/docs.oracle.com\/database\/121\/ARPLS\/u_file.htm#ARPLS70913\">https:\/\/docs.oracle.com\/database\/121\/ARPLS\/u_file.htm#ARPLS70913<\/a> <\/p>\n","protected":false},"excerpt":{"rendered":"<p>PL\/SQL with the utl_file package allows us to manipulate strings in Oracle. One may read a string from a file, modify this and subsequently write it to another file. This opens nice possibilities. Let me show some code how to do this: DECLARE v_file_ptr utl_file.file_type; v_file_handler utl_file.file_type; v_line varchar2(2000); BEGIN v_file_ptr := utl_file.fopen(&#8216;EXTERN&#8217;,&#8217;namen.txt&#8217;,&#8217;r&#8217;); v_file_handler:= utl_file.fopen(&#8216;EXTERN&#8217;,&#8217;uitvoer.txt&#8217;,&#8217;w&#8217;); [&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-3225","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\/3225","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=3225"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3225\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3225"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3225"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3225"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}