How do I find out how frequently my newly created table is refreshing?
I don't necessarily mean refreshing the preview grid in Toad--I want to know how frequently the Oracle data table I created is pulling information from an Excel spreadsheet. My goal is to have a regularly refreshing Oracle database table that captures new people added to my Excel spreadsheet "PeopleList.xlsx." It can refreshing once a day, once a week or once a month--I just want to know it is refreshing.
However, I'm not seeing where I can check how frequently "NewTable" will check "PeopleList.xlsx" for updates and refresh with new data. How do I find this?
For reference, I used Toad for Oracle 17.0 to load data from my Excel spreadsheet "PeopleList.xlsx" onto a newly created table "NewTable." I followed this 2014 Toad tutorial video.
I have put my exact steps that I followed below, since I am using Toad for Oracle 17.0 in March 2024 which means my interface differs slightly from the 2014 tutorial video that I linked.
- Open TOAD for Oracle and log in.
- Schema Browser > Tables > Create Table button
- For my new table "NewTable," add my desired column names and data types
- Database > Import > Import Table Data
- On the "Import table data" screen, I input my newly created table "NewTable" next toObject Name.
- On the "Choose Source" screen I selected "Excel file," I selected "PeopleList.xlsx" from the browser window pop-up, then I clicked Next again.
- On the "Data Details" screen, the default options sufficed for me. I didn't change anything and I clicked "Next."
- On the "Preview Data" screen, I selected the Excel sheet on "PeopleList.xlsx" with the data that I wished to load to my table "NewTable." I clicked next.
- On the "Field Mapping" screen, I confirmed that my column names from step 3 aligned correctly with the PeopleList.xlsx's columns.
- "Preview Result" screen: In both the tutorial video and in my process, I received errors from the first row containing column names in columns with number data types. Following the video, I ignored the errors and clicked next.
- "Finish" This is where I diverge from the tutorial video:
a) Before Import: Because I am only interested in new items added to the Excel spreadsheet, I checked "Delete all rows from table."
b) Import mode: Insert: Add records to destination table from source file.
c) Commit mode: Commit every 200 records.
d) I clicked Run and ignored the error messages. - "NewTable" loads perfectly. I am able to query it.
However, I'm not seeing where I can check how frequently "NewTable" will check "PeopleList.xlsx" for updates and refresh with new data. How do I find this?
EDIT: After asking around my colleagues, it sounds like my table will never refresh or pull in new information after the initial import. After clicking around some more, I was able to find the Automation Designer, which is represented by a lightning bolt.
Within Toad's Automation Designer interface, I set up an "App" to repeat the above 12-step process once a month on the first of the month.
If you look at my below screenshot, I'd like you to note the following:
- The lefthand pane, which has a folder "My Apps", a default folder, and a list of apps. These are automations or sample automations in Toad. I have blacked out the actual name for my app that I created. Note that I have selected an app.
- The middle pane shows the app, and the action it is based on (Importing Table Data), the name I chose (which I blacked out).
- the Palette pane to the right, where I have highlighted "Import Table Data."
To set up a repeatedly refreshing schedule, I right-clicked my new automation in the left-hand pane, which brought up the below menu. I selected Schedule. From that point, you can set up a recurring schedule for your Import Table Data automation:
I am open to more elegant ways of doing this, just thought I would post my work-around.
My user would like for my table to update every time he updates the Excel spreadsheet, so I will be investigating other avenues outside of Toad since I'm not seeing a way that Toad can detect when the spreadsheet was updated.