topic Re: [resolved] integration data from a temporary table to anther table via a proc in Talend Studio

[resolved] integration data from a temporary table to anther table via a proc

Anonymous — Sat, 16 Nov 2024 11:36:01 GMT

hi talend pro,
I would integrate data from a csv file to a temporary table and then transfrom data via a procédure then insert data to another table exple
I have table http_ods
CREATE TABLE KPI.HTTP_ODS
(
HOST VARCHAR2(500 BYTE),
DATE_HTTP DATE,
NBYTEDN INTEGER,
NBSESSTCP INTEGER,
AVG_SYN_SYNACK NUMBER,
AVG_SYNACK_ACK NUMBER,
DELAYREQANS NUMBER,
NBGET INTEGER,
NBPOST INTEGER,
OTHERREQ INTEGER,
NB1XX INTEGER,
NB2XX INTEGER,
NB3XX INTEGER,
NB4XX INTEGER,
NB5XX INTEGER,
NBOTHERANS INTEGER
)
then
I have a procedure
CREATE OR REPLACE procedure KPI.http_modif is
CURSOR c_http is
select
HOST
,DATE_HTTP
,NBYTEDN
,NBSESSTCP
,kpi.unite_s(AVG_SYN_SYNACK)
,kpi.unite_s(AVG_SYNACK_ACK)
,kpi.sum_s(AVG_SYN_SYNACK, AVG_SYNACK_ACK)
,kpi.unite_s(DELAYREQANS)
,NBGET
,NBPOST
,OTHERREQ
,NB1XX
,NB2XX
,NB3XX
,NB4XX
,NB5XX
,NBOTHERANS
from kpi.http_ods;
TYPE array_http is table of c_http%rowtype;
http_data array_http;
begin
open c_http;
loop
fetch c_http
bulk collect into http_data
limit 1000;

begin
forall i in 1..http_data.count
insert into kpi.http
values http_data(i);
end;

exit when c_http%notfound;
end loop;
close c_http;
end;
/
and table http :
CREATE TABLE KPI.HTTP
(
HOST VARCHAR2(500 BYTE),
DATE_HTTP DATE,
NBYTEDN INTEGER,
NBSESSTCP INTEGER,
AVG_SYN_SYNACK NUMBER,
AVG_SYNACK_ACK NUMBER,
RTT_HTTP NUMBER,
DELAYREQANS NUMBER,
NBGET INTEGER,
NBPOST INTEGER,
OTHERREQ INTEGER,
NB1XX INTEGER,
NB2XX INTEGER,
NB3XX INTEGER,
NB4XX INTEGER,
NB5XX INTEGER,
NBOTHERANS INTEGER
)

functions:
CREATE OR REPLACE function KPI.unite_s ( x in number )
return number
is
y number;
begin
y := x / 1000 ;
return y ;
end;
CREATE OR REPLACE function KPI.sum_s ( x in number , y in number )
return number
is
z number;
begin
z := (x+y) / 1000 ;
return z ;
end;
my sgbd is oracle
so I don't know how do it sad then i would extract date from the name of csv.file to put in date_http
thanks a lot for your help appreciated

Re: [resolved] integration data from a temporary table to anther table via a proc

Anonymous — Mon, 30 Jun 2014 12:50:07 GMT

tfileinputdelimited-->tdatabaseoutput (temptable)
|
onsubjobok
|
tMySQLRow (Call your stored procedure here )
|
OnSubJobOk
|
tDBInput(temptable)-->tDBOutput (final table)
Try this..

Re: [resolved] integration data from a temporary table to anther table via a proc

Anonymous — Mon, 30 Jun 2014 13:00:29 GMT

Thanks sanvaibhav for your reply
tfileinputdelimited-->tdatabaseoutput (temptable) : for insert data of csv file to toracleoutput table temporary via tmap ?
|
onsubjobok
|
tMySQLRow (Call your stored procedure here )
|
OnSubJobOk
|
tDBInput(temptable)-->tDBOutput (final table) : I dont understand how do that after appel proc , why you met composant tdbinput after you appel temporary table

Re: [resolved] integration data from a temporary table to anther table via a proc

Anonymous — Mon, 30 Jun 2014 13:05:44 GMT

>>
I dont understand how do that after appel proc , why you met composant tdbinput after you appel temporary table
Your stored procedure is going to make changes to temp table... After making changes you want to insert that data into the target table, that is the reason tdbInput...
Vaibhav

Re: [resolved] integration data from a temporary table to anther table via a proc

Anonymous — Mon, 30 Jun 2014 13:30:43 GMT

I dont think so because the proc insert data in final table !!
also tha data is big I use toraclebulkexec or toracle row ?

Re: [resolved] integration data from a temporary table to anther table via a proc

Anonymous — Tue, 01 Jul 2014 04:31:43 GMT

I dont think so because the proc insert data in final table !!
>> you can remove the last step...
also tha data is big I use toraclebulkexec or toracle row ?
>> Bulk execute is better than toraclerow...
But, as said above, if the stored procedure is inserting data, then why do you need bulkexecute or toraclerow?
In your first description
a csv file to a temporary table and then transform data via a procedure then insert data to another table example
>> there is an additional step for inserting data...
Note- I have provided guidelines... you can take whatever is suitable, rest you can ignore...
Thanks
Vaibhav
Vaibhav

Re: [resolved] integration data from a temporary table to anther table via a proc

Anonymous — Tue, 01 Jul 2014 13:40:02 GMT

thanx for your help Vaibhav