{"id":591,"date":"2011-01-28T17:29:12","date_gmt":"2011-01-28T15:29:12","guid":{"rendered":"http:\/\/touk.pl\/blog\/?p=591"},"modified":"2023-03-23T13:20:17","modified_gmt":"2023-03-23T12:20:17","slug":"insert-into-select-na-oraclu-gubi-dane","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2011\/01\/28\/insert-into-select-na-oraclu-gubi-dane\/","title":{"rendered":"Insert into .. select… na oraclu gubi dane."},"content":{"rendered":"

Problem dotyczy serwra w wersji Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bi i pobrania danych z serwera zdalnego w wersji Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production.<\/p>\n

Na serwerze zdalnym mamy tabel\u0119 z danymi do zaczytania.<\/p>\n

Dla ustalenia uwagi niech nazywa si\u0119 tab_1 i b\u0119dzie utworzona poleceniem:
\n
\nCREATE\u00a0TABLE\u00a0tab_1
\n(ID NUMBER,
\nparam_id NUMBER,
\nparam_value VARCHAR2(4000));<\/code><\/p>\n

Na systemie do kt\u00f3rego chc\u0119 pobra\u0107 z niej dane tworz\u0119 perspektyw\u0119:
\n
\ncreate or replace view v_tab1 as select * from\u00a0tab_1 @db_link;
\n<\/code><\/p>\n

Po wykonaniu polecenia select * from\u00a0v_tab1;<\/code> widzimy wszystkie dane.<\/p>\n

Tworzymy teraz w systemie docelowym tabel\u0119 tymczasow\u0105 tmp_data:
\n
\nCREATE GLOBAL temporary TABLE tmp_data
\n(ID NUMBER,
\nparam_id NUMBER,
\nparam_value VARCHAR2(4000)) \u00a0ON COMMIT PRESERVE ROWS;
\n<\/code>
\ni pr\u00f3bujemy wykona\u0107 polecenie
\n
\ninsert into\u00a0tmp_data select\u00a0\u00a0id,param_id, \u00a0param_value\u00a0from\u00a0v_tab1 where id BETWEEN :1 and :2;
\n<\/code>
\ni od razu sprawdzamy wynik
\n
\nselect * from\u00a0tmp_data ;
\n<\/code>
\nI niestety w ostatniej kolumnie zamiast spodziewanych danych s\u0105 same nulle.<\/p>\n

Cofam transakcj\u0119:
\n
\nRollback;
\n<\/code><\/p>\n

Zachowanie dziwne, ale jako\u015b trzeba sobie poradzi\u0107 i te dane pobra\u0107.<\/p>\n

Robi\u0119 prawie to samo, ale pobieraj\u0105c dane wykonuj\u0119 konkatenacj\u0119 ostatniej kolumny z pustym stringiem (czyli nullem).
\n
\ninsert into\u00a0tmp_data select id,param_id, '' || param_value as\u00a0param_value\u00a0 from\u00a0v_tab1 where id BETWEEN :1 and :2;
\nselect * from\u00a0tmp_data ;
\nrollback;
\n<\/code><\/p>\n

Tym razem dane w tabelce tymczasowej s\u0105 prawid\u0142owe.<\/p>\n

Podobny efekt zaobserwowa\u0142em z inn\u0105 tabl\u0105, gdzie ostatnia kolumna by\u0142a typu number<\/p>\n

W rzeczywistym systemie na kt\u00f3rym zaobserwowa\u0142em problem.<\/p>\n","protected":false},"excerpt":{"rendered":"Problem dotyczy serwra w wersji Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bi i pobrania danych z…\n","protected":false},"author":19,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"class_list":{"0":"post-591","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-development-design"},"_links":{"self":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/591","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/comments?post=591"}],"version-history":[{"count":12,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/591\/revisions"}],"predecessor-version":[{"id":15623,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/591\/revisions\/15623"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=591"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=591"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=591"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}