You are not logged in.
Below are the steps that we were performing using PLSQL (Oracle) and Perl respectively. Now, we are thinking of using Talend Data Integration. I would request to please provide your valuable suggestions.
1) We use reports which involves fetching data from multiple tables and the output is logged into a File. The output from one select statement is fed as input to next SQL Statement (and it involves Looping in case of multiple rows fetched from a Select statement)
2) This report is then FTP'd to external system (we can used tFTPPut).
For First point, we query multiple tables and the output of every select is the input for next select statements. So multiple records from a select statement is provided as input to next select statement (we go through the loop here). So, I Feel we need to use tJava component for the above operation. Please correct me if am wrong here.
For Second point, we can use tFilePut.
Please suggest us in case we can actually use only the built in components instead of coding in Java, because its already done in PLSQL.
Thanks ton for your time.
Thanks & BR,
For your first point, you might create part of job like this.
Now here comes questions. You have to use dynamic schema for tOracleInput2.
Could you show me an example of your next select statement? If it is too complex or contains some SQL function, the dynamic schema may not work.
Besides, I don't think tJava is helpful here.
For the second point, use tFTPPut.
Thanks a ton for your reply..
Below is the code that was written in PLSQL and we need to implement the same in Talend.
I will go through step by step on the implementation. We have exception handling for all the Select statements. Please throw some light if the same can be implemented..
1) From the below Select statement we will get the Order handler PK and Handle Name
BEGIN SELECT fim_order_handler_pk,handle_name INTO v_fim_order_handler_SX_pk, v_handle_SX_name FROM NI_FIM_ORDER_HANDLER WHERE HANDLE_NAME = 'SFCOM_FIMEx'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('FIMEX Pack handler not set'); GOTO the_end; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Exception occured during Fetch on Handlers, please check the data set on the NI_FIM_ORDER_HANDLER and Continue...'); RAISE_APPLICATION_ERROR(-20001,'Error - '||SQLCODE||' -ERROR Message- '||SQLERRM); GOTO the_end; END;
2) The Order handler PK and Handle Name obtained from previous SQL is used in below SQL Statement now :
Below SQL is using Bulk collect -->
BEGIN SELECT fim_profile_pk,profile_name BULK COLLECT INTO va_fim_profile_SX_pk, va_profile_SX_name FROM NI_FIM_PROFILE WHERE fim_order_handler_fk = v_fim_order_handler_SX_pk; IF va_fim_profile_SX_pk.count = 0 THEN DBMS_OUTPUT.PUT_LINE('No FIM Profiles Present for FIMEX'); GOTO the_end; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Exception occured during Fetch on profiles, please check the data set on the NI_FIM_PROFILE and Continue...'); RAISE_APPLICATION_ERROR(-20001,'Error - '||SQLCODE||' -ERROR Message- '||SQLERRM); GOTO the_end; END
3) Now, for every FIM Profile PK (va_fim_profile_SX_pk) collected before, Collect all the Batch IDs from ni_fim_order and call the Procedure fim_order_details_fetch_sx …
FOR idx_profile IN va_fim_profile_SX_pk.first..va_fim_profile_SX_pk.last LOOP BEGIN SELECT SUBSTR(order_number,INSTR(order_number,'_',1,1)+1,LENGTH(order_number)) BULK COLLECT INTO va_batch_id_sx FROM ni_fim_order WHERE fim_profile_fk = va_fim_profile_SX_pk(idx_profile) AND parent_order_fk IS NOT NULL; fim_order_details_fetch_sx( v_fim_fimord_udl_pk, v_fmsi_fimord_udl_pk, va_batch_id_sx, va_fim_profile_SX_pk(idx_profile), va_profile_SX_name(idx_profile)); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Exception occured during Fetch For Batch IDs for FIMEX Orders, please check the data set on the NI_FIM_ORDER and Continue...'); RAISE_APPLICATION_ERROR(-20001,'Error - '||SQLCODE||' -ERROR Message- '||SQLERRM); DBMS_OUTPUT.PUT_LINE(''); CONTINUE; END; END LOOP;
4) The Procedure fim_order_details_fetch_sx will have the below SQL Statement which will run through the FOR Loop for every va_batch_id bulk collected from before SQL Statement. This Output is Spooled into an File and FTP’d to external system (we use Perl for this) :
FOR idx_order in 1..pa_batch_id.count loop BEGIN SELECT s.iccid, s.current_state, i.imsi_number, i.current_state, ud1.data_value as FIMORDER BULK COLLECT INTO va_iccid, va_iccid_current_state, va_imsi_number, va_imsi_current_state,va_fim_order FROM ni_imsi i, ni_fim s, ni_user_data ud1, ni_user_data ud2 WHERE ud1.related_user_data_link = p_iccid_udl_pk AND ud1.parent_key = imsi_pk AND ud1.data_value = va_batch_id(idx_order) AND ud2.related_user_data_link = v_imsi_udl_pk AND ud2.parent_key = fim_pk AND ud2.data_value = va_batch_id(idx_order) AND s.fim_pk = i.related_fim; END LOOP;
Now, the output that displayed is FTP’ed to the External System.
Please suggest if the above can be implemented. Thanks in Advance..
Thanks & BR,