topic Re: Oracle block execution in Talend Studio

Oracle block execution

Anonymous — Sat, 16 Nov 2024 11:58:03 GMT

Hy everybody,
I have the following situation :
I am trying to replicate data in real-time from ORACLE -> VERTICA using data extracted with oracle log miner packages and Talend as the job manager.
So i am creating a dynamic script every 10 minutes and i need to execute it using Talend tools.
The script looks like this :
#########################################################################
begin
DBMS_LOGMNR.ADD_LOGFILE (LogFileName => '/u01/app/oracle/product/11.2.0/xe/dbs/arch1_2_820601368.dbf',options => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE (LogFileName => '/u01/app/oracle/product/11.2.0/xe/dbs/arch1_3_820601368.dbf',options => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE (LogFileName => '/u01/app/oracle/product/11.2.0/xe/dbs/arch1_4_820601368.dbf',options => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE (LogFileName => '/u01/app/oracle/product/11.2.0/xe/dbs/arch1_5_820601368.dbf',options => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE (LogFileName => '/u01/app/oracle/product/11.2.0/xe/dbs/arch1_6_820601368.dbf',options => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE (LogFileName => '/u01/app/oracle/product/11.2.0/xe/dbs/arch1_7_820601368.dbf',options => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.ADD_LOGFILE (LogFileName => '/u01/app/oracle/product/11.2.0/xe/dbs/arch1_8_820601368.dbf',options => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.START_LOGMNR (DictFileName => '/tmp/utl/dictionary.ora');
end;
/
create table stage as SELECT * FROM v$logmnr_contents;
begin
DBMS_LOGMNR.END_LOGMNR();
end;
/
#########################################################################
Just need to say that all of this must be done in one session , otherwise the log miner will not work!!
How can i run this block of code using Talend tools !!!
Thx all.

Re: Oracle block execution

Anonymous — Thu, 18 Jul 2013 09:33:23 GMT

Hi,
You can do only one plsql block, try to do all in one block:
DECLARE
BEGIN
......
.
.
.
.
END

Re: Oracle block execution

Anonymous — Fri, 19 Jul 2013 16:51:24 GMT

The problem is that the process is required to run in one oracle session!!
So and i can not query the data createt inside the pl/block

Re: Oracle block execution

Anonymous — Sat, 20 Jul 2013 20:50:54 GMT

I had a similar problem and solved it with a user component tSQLScriptParser. This component will extract 3 statements from your script and provide the SQL as return value STATEMENT_SQL.
I would do this:
tOracleConnection (autocommit=false)-> tSQLScriptParser -(iterate)->tOracleRow (using the tOracleConnection)
From tSQLScriptParser - (OnSubjobOk)-> tOracleCommit
The tOracleRow gets as SQL this: ((String)globalMap.get("tSQLScriptParser_1_STATEMENT_SQL")) without any "
The tSQLScriptParser is a user component and available via Talend Exchange.
http://www.talendforge.org/exchange/index.php?eid=724&product=tos&action=view&nav=1,1,1
This component recognize blocks and also the / as end of complex statements.

Re: Oracle block execution

Anonymous — Fri, 22 Jul 2016 15:07:03 GMT

Hi,
I am experiencing the same issue with tOracleRow component.
I would like to execute a pl/sql block.
But Talend seems not execute the block.
No errors are issued.
Here what I wrote in my tOracle component:

"
BEGIN
  FOR c IN
    (SELECT t.table_name, c.column_name
       FROM user_tables t, user_tab_columns c
       WHERE c.table_name = t.table_name
         AND data_type='VARCHAR2'
		AND lower (t.table_name) in('intervention' )
        )
  LOOP
    execute immediate(
                      'UPDATE '||c.table_name||
                      ' SET '||c.column_name||' = TRIM('||c.column_name||') WHERE '||
                      c.column_name||' <> TRIM('||c.column_name||') OR ('||
                      c.column_name||' IS NOT NULL AND TRIM('||c.column_name||') IS NULL)'
                     ); 
  END LOOP;
END;
"