{"id":137,"date":"2014-04-08T13:02:23","date_gmt":"2014-04-08T06:02:23","guid":{"rendered":"https:\/\/sanmaharjan.com\/?p=137"},"modified":"2014-04-09T10:53:22","modified_gmt":"2014-04-09T03:53:22","slug":"convert-blob-into-insert-statment","status":"publish","type":"post","link":"https:\/\/sanmaharjan.com\/convert-blob-into-insert-statment\/","title":{"rendered":"Convert Blob into Insert Statment"},"content":{"rendered":"<p>There are many ways to copy Blob column from one server to Another like,<\/p>\n<p>1. Generate file from blob in source server, and import file into destination server using Tools like SQL developer, TOAD.<\/p>\n<p>2. Using DBLink,<br \/>\n<code>INSERT INTO BLOB_TEST SELECT BLOB_COL1 FROM TEST_BLOB@SOURCEDB;<\/code><br \/>\n3.\u00a0 I have recently discovered, you can make dynamic insert statement and run as normal insert statement in destination server.<\/p>\n<p>Example here: <a href=\"https:\/\/sanmaharjan.com\/uploads\/2014\/04\/sample_blob.txt\">sample_blob<\/a><\/p>\n<p>Scripts to make dynamic script can be written as<\/p>\n<pre>Set serveroutput on\r\nDECLARE\r\nM BLOB;\r\nn NUMBER:=1;\r\nc blob;\r\nl_limit NUMBER:=4000;\r\nvm RAW(32767);\r\nBEGIN\r\nDbms_Output.Put_Line('DECLARE\r\nc BLOB;\r\nBEGIN\r\ndbms_lob.createtemporary(c,true);');\r\nSELECT  BLOB_COL INTO M FROM TEST_BLOB;\r\n--Dbms_Output.Put_Line(Length(M));\r\nwhile (n+l_limit&lt;=length(m)) loop\r\n     vm:=(dbms_lob.substr(M,l_limit,n));\r\n     Dbms_Output.Put_Line('dbms_lob.writeappend(c,'||Length(vm)\/2||','''||VM||''');');\r\n     n:=n+l_limit;\r\nEND LOOP;\r\nvm:=(dbms_lob.substr(M,length(m)-n+999,n));\r\nDbms_Output.Put_Line('dbms_lob.writeappend(c,'||Length(vm)\/2||','''||VM||''');');\r\nDbms_Output.Put_Line('INSERT INTO blob_test VALUES (c,9);\r\nEND;\r\n\/');\r\nEND;\r\n\/<\/pre>\n<p><a href=\"https:\/\/sanmaharjan.com\/uploads\/2014\/04\/B_LOB_TABLE.txt\">Download Here<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are many ways to copy Blob column from one server to Another like, 1. Generate file from blob in source server, and import file into destination server using Tools like SQL developer, TOAD. 2. Using DBLink, INSERT INTO BLOB_TEST SELECT BLOB_COL1 FROM TEST_BLOB@SOURCEDB; 3.\u00a0 I have recently discovered, you can make dynamic insert statement [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[31],"tags":[51,52,50,12,53],"_links":{"self":[{"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/posts\/137"}],"collection":[{"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/comments?post=137"}],"version-history":[{"count":6,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/posts\/137\/revisions"}],"predecessor-version":[{"id":149,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/posts\/137\/revisions\/149"}],"wp:attachment":[{"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/media?parent=137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/categories?post=137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sanmaharjan.com\/wp-json\/wp\/v2\/tags?post=137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}