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 ?
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;"
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.
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
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.
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
