how i can insert date stamp on the exported file output, e.g. "c:\temp\xxx."date exported".xlsx"?
this is to prevent from overwriting existing files in same folder.
in my dataset tab, i want to get only the current records on the table dynamically, e.g.
"select * from mytable
where date="current record's date only here"
this is to ensure only the current records minus the old ones that were extracted will be exported.
i have a column in the table for the latest record so would want only to export that latest record's date, e.g. column named like "current_date_record"
what i meant was whenever a cron job successfully completes, it stamps the current date on the table (having this column) so we would know it's the latest record.
so you want to find max value in that column and just export that one row (or rows with the same date)? And use that date...whatever it is....in your filename? or just use current timestamp in filename?
to export rows with the latest timestamp, make your sql like this:
select *
from mytable
where timestamp_column = (select max(timestamp_column) from mytable)
Thank you so much @JohnDorlon, exactly what i need and it's both: I need the output filename to have the current date of the latest record and export only that latest record in excel so you answered them both
One last question please?
I want to automate this task & I understand that I just need to right-click & input necessary info to schedule it, including username/password so my question is: what kind of account or user privilege is needed to run it on schedule? Is there a special privilege the account am using on the DB needs for this or it will have to be a different account to execute the scheduled task?
(Note: am using Toad for Oracle v.10.5 and my output file just needs to be stored on my local system)
The database user will be whatever you use when you create the action in Toad. No special privs there.
I'm not an expert on scheduling, but I think the only thing that you might need to be aware of is some windows accounts might not be able to run scheduled items when you are not logged into windows. Tagging @jbowman to correct me if I'm wrong.
Edit: Toad 10.5 is really old. I'm not sure if the %DATEFILE% trick is going to work in that version. Fingers crossed.
This is noted @JohnDorlon, will try to check these then to see what works. Paging @jbowman to help out please?
Wrt my old Toad, had the latest version before but downgraded to this version since am unable to fix compatibility issues with some of my system's tools.
Just an update:
the %datefile% works and adds the date on the filename output, @JohnDorlon
Now I input the username and password to automate the task in Automation Designer but clicking "Next", I encountered an error with the ff. details (Please note I omitted some info (like Computer details and Network for security reasons):
Sorry, I missed the tagging in the message yesterday afternoon...
what kind of account or user privilege is needed to run it on schedule? Is there a special privilege the account am using on the DB needs for this or it will have to be a different account to execute the scheduled task?
When scheduling a task to run from the Automation Designer, the username and password you'll need to enter is the Windows account for your computer, not the username and password for your database connection. Toad will create a task in the Windows Task Scheduler that will launch Toad and then log into whatever database you've configured for the App in order to do the work. As John mentioned, if you're trying to use a different account other than your own, make sure it has the ability to run scheduled tasks within Windows.
Based on your Eurekalog that you've attached, I'm not entirely sure why you're getting that error. I do see where there's an EOleException error, but I'm not seeing any kind of call stack to show where that error may be happening. Are you using the correct username and password (Windows one) before clicking the "Next" button?
Another thing, as John mentioned and your Eurekalog shows, Toad 10.5 is a very old release that dates back to April 2010. Back then, we were using a third-party component to perform all our task scheduling which really only supported Task Scheduler 1.0 (compatible primarily with Windows XP, Windows Server 2003, and earlier).
If you're trying to use that version of Toad on a newer operating system (i.e. Windows 7+), Task Scheduling from Toad may not work correctly as I don't believe Task Scheduler 1.0 is supported on newer versions of Windows. For that, you'll probably need a newer version of Toad for Oracle. Toad 12.0, I believe, was the first version to support the Task Scheduler 2.0 interfaces.
thanks for further guiding me. that is where my mistake is as am using DB credentials instead of windows account. i will try that and see from there; will also update here.
