Insert into .. select… na oraclu gubi dane.

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.

Na serwerze zdalnym mamy tabelę z danymi do zaczytania.

Dla ustalenia uwagi niech nazywa się tab_1 i będzie utworzona poleceniem:

CREATE TABLE tab_1
(ID NUMBER,
param_id NUMBER,
param_value VARCHAR2(4000));

Na systemie do którego chcę pobrać z niej dane tworzę perspektywę:

create or replace view v_tab1 as select * from tab_1 @db_link;

Po wykonaniu polecenia select * from v_tab1; widzimy wszystkie dane.

Tworzymy teraz w systemie docelowym tabelę tymczasową tmp_data:

CREATE GLOBAL temporary TABLE tmp_data
(ID NUMBER,
param_id NUMBER,
param_value VARCHAR2(4000))  ON COMMIT PRESERVE ROWS;

i próbujemy wykonać polecenie

insert into tmp_data select  id,param_id,  param_value from v_tab1 where id BETWEEN :1 and :2;

i od razu sprawdzamy wynik

select * from tmp_data ;

I niestety w ostatniej kolumnie zamiast spodziewanych danych są same nulle.

Cofam transakcję:

Rollback;

Zachowanie dziwne, ale jakoś trzeba sobie poradzić i te dane pobrać.

Robię prawie to samo, ale pobierając dane wykonuję konkatenację ostatniej kolumny z pustym stringiem (czyli nullem).

insert into tmp_data select id,param_id, '' || param_value as param_value  from v_tab1 where id BETWEEN :1 and :2;
select * from tmp_data ;
rollback;

Tym razem dane w tabelce tymczasowej są prawidłowe.

Podobny efekt zaobserwowałem z inną tablą, gdzie ostatnia kolumna była typu number

W rzeczywistym systemie na którym zaobserwowałem problem.

You May Also Like

Control your bandwidth using ntop

I was looking for tool which could help me check who is using my bandwidth. Here are requirements which I want from this kind of tool:local hosts bandwidth distribution - it is helpful when you are loosing your bandwidth and don't know who abuse it in ...

Distributed scans with HBase

HBase is by design a columnar store, that is optimized for random reads. You just ask for a row using rowId as an identifier and you get your data instantaneously. Performing a scan on part or whole table is a completely different thing. First of all, it is sequential. Meaning it is rather slow, because it doesn't use all the RegionServers at the same time. It is implemented that way to realize the contract of Scan command - which has to return results sorted by key. So, how to do this efficiently?HBase is by design a columnar store, that is optimized for random reads. You just ask for a row using rowId as an identifier and you get your data instantaneously. Performing a scan on part or whole table is a completely different thing. First of all, it is sequential. Meaning it is rather slow, because it doesn't use all the RegionServers at the same time. It is implemented that way to realize the contract of Scan command - which has to return results sorted by key. So, how to do this efficiently?

Confitura 2013 afterthoughts

Confitura, the biggest free-of-charge Java conference in Europe, took place on the 6th of July in Warsaw. TouK's presence was heavy, with 5 separate talks, all chosen in call for papers, no sponsored bullshit. We were sponsoring deck chairs during the...Confitura, the biggest free-of-charge Java conference in Europe, took place on the 6th of July in Warsaw. TouK's presence was heavy, with 5 separate talks, all chosen in call for papers, no sponsored bullshit. We were sponsoring deck chairs during the...