data migration from oracle to postgrsql using scriptella ETL

64 views
Skip to first unread message

maher hattabi

unread,
Aug 25, 2017, 5:51:11 AM8/25/17
to Scriptella ETL
Hello every one , i am new to scriptella , i need your help .I have a table TMP_STRUCTURE in oracle , that have column code.According to the length of the code , i wil decide where to copy it using scriprtella.
I start by selecrting the column code , check the length of the code if it is equal to 2 ,i will check whether it has been alreay copied in the target table in a postgresql database.
if it doesn't exist i insert it , if it already exist , i say it exist .But the problem it doesn't exist i got nothing added in the target table any help would be appreciated  the table TMP_STRUCTURE contains two field code and label , and the target table group_article 
CREATE TABLE public.article_groups
(
  id bigint NOT NULL,
  is_enabled boolean,
  code character varying(255),
  name character varying(255),
  "number" character varying(255),
  CONSTRAINT article_groups_pkey PRIMARY KEY (id)
)
And here what i did 


<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
<description>
test script Pour table article
</description>
<connection id="in" driver="oracle"
url="jdbc:oracle:thin:@localhost:1521:XE" user="IPTECH" password="IPTECH" />

<connection id="out" driver="postgresql"
url="jdbc:postgresql://localhost:5432/gemodb" user="postgres"
password="maher" />
<connection id="janino" driver="janino" />

<query connection-id="in"> selectionner toute les données de la table client pour en faire lextraction
select code ,libelle  from TMP_STRUCTURE;
<query connection-id="janino">
import java.io.*;
Boolean result=false;
String newCode=get("newCode").toString();
int longuerCode =newCode.length();
set("longuerCode",longuerCode);
next();
<query connection-id="out" if="longuerCode==2"> les données seront traités avec la table article_groups
select id from public.article_groups ;
<query connection-id="janino"> comparer avec ce qui existe
import java.io.*;
import java.*;
Boolean test ;
String code=get("code").toString();
String id =get("id").toString();
System.out.println(newCode);
test=newCode.equals(id);
set("test",test);
next();
<script connection-id="janino" if="test"> si l'id de la base client existe en local
import java.io.*;
import java.*;
System.out.println("existe dans la base");
</script>
<script connection-id="out" if="!test"> si le code de la base client n'existe pas on doit l'insérer
INSERT INTO public.article_groups (id,is_enabled,code,name) values
(cast(?newCode as bigint),TRUE,?newCode,?newLibelle);
</script>


</query>
</query>
</query>
</query>
</etl>

Reply all
Reply to author
Forward
0 new messages