Hi
We have Toad App Designer as part of our TOAD install
We use Toad for Oracle 9.7.2.5 version
My question is can we automate to run a SQL Script which can be triggered off when an email is received and the parameters passed on to the SQL
Basically the SQL does a simple delete for some duplicate records and we pass on primary and duplicate no. as argument.
Its a routine thing and which comes to service desk and i was thinking if we can automate it if possible.
Thanks
Sri
Toad can send emails, but it can’t monitor incoming email, sorry.
Thanks very much
So if we get a request like that, we have to open TOAD and run the script manually
I was thinking if we could automate it some how 
You can automate that, just not with Toad. You will need to parse your email text somehow and extract the data of interest. There are many hits on Google for writing VBA scripts and what not to get to email text from Outlook. You will need to get the data out of your email through a script or software of your own writing. Once you have that I’d take Toad out of the loop and just construct your SQL, save it to a temp file, and pass the file to SQL*Plus for execution.
Toad’s Automation Designer can automate many things, but it cannot monitor your email system for you and extract what you’d like from emails.
Michael
Thanks Mike for that,
As it comes to service desk and they raise a ticket saying is a duplicate record request. It then gets assigned to 2nd line and usually they log into TOAD and sort it out.
I was just thinking rather than spending some time on this kind of repeat things, could we automate.
Anyway really appreciate your response on this
S
Maybe you could use DBMS_SCHEDULER to create a job that runs a query daily (or however often needed) to find duplicates, then deletes them, perhaps with a log table that saves the info deleted and date it was deleted for future reference.
Maybe this is a stupid suggestion, but… have you considered the simple option of adding a unique constraint to the table, to prevent duplicates from happening? You mention in one of the previous posts that this duplication happens quite frequently and someone has to run a script to deduplicate the data.
Just wondering.
Cheers,
Norm.
–
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Hi
These tickets get logged at service desk with a primary reference number and the duplicates associated with it which then gets passed on to the second line.
I don’t think it will be feasible to use dbms_scheduler for this kind of scenario. I have used it for other scenarios where a scheduled job runs at a set time.
Thanks
S
Hi Norm
This is a third party system and the duplicates happen at different locations like one person with different names (surname, middlename etc) so there is no way of knowing when a data entry happens.
S
Hi Norm
This is a third party system and the duplicates happen at different locations like one person with different names (surname, middlename etc) so there is no way of knowing when a data entry happens.
S
Ok, thanks.
If this were a system I was looking after, I would be raising support calls with the third party to get them to:
fix the database's obvious lack of constraints;
fix the application to cater for newly added constraints.
You may wish to consider doing the same, every time you get another support call. Raise one call for each area you get problems with.
Good luck!
On 26 March 2018 10:29:26 BST, "srinivas.sikha" bounce-srinivassikha@toadworld.com wrote:
Reply by srinivas.sikha
Hi Norm
This is a third party system and the duplicates happen at different locations like one person with different names (surname, middlename etc) so there is no way of knowing when a data entry happens.
S
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
I second Norm’s suggestions.
Another approach is to provide some front end UI to handle the delete/merge of duplicates - either the 3rd party you mention or some in-house developers if you have them.