Hi Experts,
I am using Delete statement in Before SQL option to delete few records and write mode=insert in Oracle connector stage.
Job completed successfully.
But Only it inserting the records. Deletions are not taking place.
1)Why did it fail to delete records?
2)Any alternate methods to delete some records from table then insert new records?
---------------------------------------------------
Thanks in advance,
Phani
Oracle Connector Before SQL not deleting records
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I've just encountered something similar and it was a rather frustrating discovery process as our BEFORE and AFTER SQL statements were being parsed but seemingly not executed; this occurred after a fixpack install that we ended up rolling back because we could figure out what the problem was (and were under time pressure, as usual).
The cause in our case was the "Autocommit" setting, defaulting to "off" in our SQL-Server connectors. The version we developed on would let this "off" setting still commit the before- and after-sql stages but the fixpack update, and also 9.1, doesn't. One needs to specify "on" or explicitly put a COMMIT into.
Note that we had this in our stages where we read data, I haven't tested this on the write stages but assume that the commit frequency and array size would take care of that when writing.
The cause in our case was the "Autocommit" setting, defaulting to "off" in our SQL-Server connectors. The version we developed on would let this "off" setting still commit the before- and after-sql stages but the fixpack update, and also 9.1, doesn't. One needs to specify "on" or explicitly put a COMMIT into.
Note that we had this in our stages where we read data, I haven't tested this on the write stages but assume that the commit frequency and array size would take care of that when writing.
</a>
Ok, so we are dealing with a different problem.
If you intentionally make a syntax error in your DELETE before-SQL, does the job produce an error? If yes, then the before-sql is being parsed. Add ";COMMIT" to the before-SQL and see if any messages or warnings are in the log if the rows aren't being deleted. You can turn on as much logging as possible in your parameters to add to runtime output. I think that either OSH_DUMP or OSH_SCORE will echo the actual SQL commands used at runtime so you can manually run them to see if they work as you expect.
If you intentionally make a syntax error in your DELETE before-SQL, does the job produce an error? If yes, then the before-sql is being parsed. Add ";COMMIT" to the before-SQL and see if any messages or warnings are in the log if the rows aren't being deleted. You can turn on as much logging as possible in your parameters to add to runtime output. I think that either OSH_DUMP or OSH_SCORE will echo the actual SQL commands used at runtime so you can manually run them to see if they work as you expect.