topic Re: Multiple update sql statement in tOracleRow in Talend Studio

Multiple update sql statement in tOracleRow

Anonymous — Sat, 16 Nov 2024 13:44:47 GMT

I'm trying to use tOracleRow to run multiple update statement. In SQL developer , I'm able to update using query like this:
"UPDATE PPB SET REFERENCE_YEAR=2010 WHERE RECORD_ID=21;UPDATE PPB SET REFERENCE_YEAR=2010 WHERE RECORD_ID=22;"
But when I input this to tOracleRow , it shows me invalid character. I need help on this matter. Can anyone explain to me how to update or
insert multiple sql in tOracleRow ?

Re: Multiple update sql statement in tOracleRow

Anonymous — Thu, 01 Oct 2009 12:06:22 GMT

hi,
i would have thought that you would use tOracleRow per statement and chain as many as you want (one after another).
did it help?

Re: Multiple update sql statement in tOracleRow

Anonymous — Thu, 12 Aug 2010 11:20:57 GMT

You have to do this : "BEGIN UPDATE PPB SET REFERENCE_YEAR=2010 WHERE RECORD_ID=21;UPDATE PPB SET REFERENCE_YEAR=2010 WHERE RECORD_ID=22; COMMIT; END;"

Re: Multiple update sql statement in tOracleRow

Anonymous — Tue, 28 Sep 2010 05:58:20 GMT

Hi,
I'm trying to do something similar. I need to run a stored procedure (that sets the user id for auditing) with inputs before I run update/insert/delete statements. But when I run both SQL in one tOracleRow component, I get the following error:
Starting job multipleSqlTest at 23:53 27/09/2010.
connecting to socket on port 4028
connected
ORA-06550: line 1, column 13:
PLS-00103: Encountered the symbol "COMP_AUDIT" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "COMP_AUDIT" to continue.
disconnected
Job multipleSqlTest ended at 23:53 27/09/2010.
I have the component query as follows:
"begin
call comp_audit.set_user(3, 'A55555');
Delete from CMPADM.SP_CMPO_ELIG where CMPO_ELIG_KEY=34007;
end;"
I have tried running the two sql statements in different tOracleRow components. I do not get the error and job completes, but the record is not deleted.
Any ideas what I might be doing wrong?
Thanks.

Re: Multiple update sql statement in tOracleRow

Anonymous — Tue, 28 Sep 2010 07:36:20 GMT

Hello
Try to add commit keyword in your query:
"begin
call comp_audit.set_user(3, 'A55555');
Delete from CMPADM.SP_CMPO_ELIG where CMPO_ELIG_KEY=34007;
commit;
end;"

but the record is not deleted.

Are you using a tOracleConnection to create a db connection? If so, don't forget to use a tOracleCommit to commit the transaction.
Best regards
Shong

Re: Multiple update sql statement in tOracleRow

Anonymous — Tue, 28 Sep 2010 13:58:34 GMT

Hi,
I'm not using a tOracleConnection component. I tried that, but the same error. I don't think the process is moving beyond the 'call' statement. I tried using execute instead of call as well.
I also tried using two seperate tOracleRow components. One for each SQL. Had a commit for the second component with the delete statement.
First Component SQL:
"call comp_audit.set_user(3, 'A55555')"
Second Component SQL:
"begin
Delete from CMPADM.SP_CMPO_ELIG where CMPO_ELIG_KEY=34007;
commit;
end;"
This time the first component runs without errors. But the record is still not deleted, due to the user not being set in the same transation (I think).
Now I get an error from the database because the user is not set.
Starting job Copy_of_multipleSqlTest at 07:55 28/09/2010.
connecting to socket on port 3536
connected
ORA-31495: error in synchronous change table on "CMPADM"."SP_CMPO_ELIG"
ORA-01400: cannot insert NULL into ("CMPADM"."CD$AUD_INFO"."AUD_USR_SPC")
ORA-06512: at "CMPADM.COMP_AUDIT", line 51
ORA-06512: at "CMPADM.TR$CMPO_ELIG", line 1
ORA-04088: error during execution of trigger 'CMPADM.TR$CMPO_ELIG'
ORA-06512: at line 1
disconnected
Job Copy_of_multipleSqlTest ended at 07:55 28/09/2010.
Ultimately I want to run both sql, so I wont get this errror.
Thanks.

Re: Multiple update sql statement in tOracleRow

_AnonymousUser — Mon, 16 May 2011 00:15:48 GMT

You have to do this : "BEGIN UPDATE PPB SET REFERENCE_YEAR=2010 WHERE RECORD_ID=21;UPDATE PPB SET REFERENCE_YEAR=2010 WHERE RECORD_ID=22; COMMIT; END;"

This does'nt work when used in a DROP table command.
I need to execute the following sql commands using tOracleRow:
drop table amp
drop table amp1
truncate table emp_tgt
Any idea pls.
Cheers,
Ram