DDevelopment & Design

Insert into .. select… na oraclu gubi dane.

  • byradek
  • January 28, 2011
  • 1 minute read

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.

— Previous article

Hibernate hbm2ddl won't create schema before creating tables

Next article —

Chaining job execution in Quartz

You May Also Like
DDevelopment & Design

Inconsistent Dependency Injection to domains with Grails

I've encountered strange behavior with a domain class in my project: services that should be injected were null. I've became suspicious as why is that? Services are injected properly in other domain classes so why this one is different?

Constructors experiment

I've created an experiment. I've created empty LibraryService that should be injected and Book domain class like this:

class Book {
    def libraryService

    String author
    String title
    int pageCount

    Book() {
        println("Finished constructor Book()")
    }

    Book(String author) {
        this()
        this.@author = author
        println("Finished constructor Book(String author)")
    }

    Book(String author, String title) {
        super()
        this.@author = author
        this.@title = title
        println("Finished constructor Book(String author, String title)")
    }

    Book(String author, String title, int pageCount) {
        this.@author = author
        this.@title = title
        this.@pageCount = pageCount
        println("Finished constructor Book(String author, String title, int pageCount)")
    }

    void logInjectedService() {
        println("    Service libraryService is injected? -> $libraryService")
    }
}
class LibraryService {
    def serviceMethod() {
    }
}

Book has 4 explicit constructors. I want to check which constructor is injecting dependecies. This is my method that constructs Book objects and I called it in controller: 

class BookController {
    def index() {
        constructAndExamineBooks()
    }

    static constructAndExamineBooks() {
        println("Started constructAndExamineBooks")
        Book book1 = new Book().logInjectedService()
        Book book2 = new Book("foo").logInjectedService()
        Book book3 = new Book("foo", 'bar').logInjectedService()
        Book book4 = new Book("foo", 'bar', 100).logInjectedService()
        Book book5 = new Book(author: "foo", title: 'bar')
        println("Finished constructor Book(Map params)")
        book5.logInjectedService()
    }
}

Analysis

Output looks like this:

Started constructAndExamineBooks
Finished constructor Book()
    Service libraryService is injected? -> eu.spoonman.refaktor.LibraryService@2affcce2
Finished constructor Book()
Finished constructor Book(String author)
    Service libraryService is injected? -> eu.spoonman.refaktor.LibraryService@2affcce2
Finished constructor Book(String author, String title)
    Service libraryService is injected? -> null
Finished constructor Book(String author, String title, int pageCount)
    Service libraryService is injected? -> null
Finished constructor Book()
Finished constructor Book(Map params)
    Service libraryService is injected? -> eu.spoonman.refaktor.LibraryService@2affcce2

What do we see?

  1. ✔

    Empty constructor injects dependencies.

  2. ✔

    Constructor that invokes empty constructor explicitly injects dependencies.

  3. ✘

    Constructor that invokes parent's constructor explicitly does not inject dependencies.

  4. ✘

    Constructor without any explicit call declared does not call empty constructor thus it does not inject dependencies.

  5. ✔

    Constructor provied by Grails with a map as a parameter invokes empty constructor and injects dependencies.

Conclusion

Always explicitily invoke empty constructor in your Grail domain classes to ensure Dependency Injection! I didn't know until today either!