Aug 16, 2017 1:25:42 PM by Deepak Vohra
Toad Data Point is a relational data source access, query, and analytics tool that may be used to create a table, add table data, query a table, and prepare reports; all from a graphical user interface (GUI). Toad Data Point supports several data sources including file data sources (CSV, Excel and Access), relational data sources (including DB2, Oracle, MySQL, PostgreSQL, SQL Server), and Business Intelligence data sources (including Google Analytics and Oracle BI Enterprise). In this article we shall take a slightly different approach by connecting to a data source running on a managed service on the cloud. We shall be using Oracle Database on Amazon Web Services (AWS) Relational Data Service (RDS) as a data source.
This article has the following sections.
Setting the Environment
Creating an Oracle Database Instance on RDS
Creating a Connection with Oracle Database on RDS
Creating a Database Table
Adding Database Table Data
Committing Database Table Changes
Using Query Builder
Using SQL Editor
The only prerequisites are the following:
Create an Amazon Web Services account at https://aws.amazon.com/resources/create-account/.
Download and install Toad Data Point from https://www.quest.com/products/toad-data-point/.
The Toad Data Point dashboard is shown in Figure 1.
Figure 1. Toad Data Point Dashboard
First, we need to create an Oracle Database instance on RDS; for which, access the RDS service on AWS in the Database category or at https://aws.amazon.com/rds/. Click on Get Started Now to create a RDS instance as shown in Figure 2.
Figure 2. Starting RDS
In Select Engine select the Oracle SE DB Engine with Select as shown in Figure 3.
Figure 3. Selecting Oracle SE DB Engine
Select Oracle SE in Dev/Test in the Production? page. Click on Next Step as shown in Figure 4.
Figure 4. Selecting Dev/Test
In Specify DB Details select the checkbox “Only show options that are eligible for RDS Free Tier”. Select the default settings as shown in Figure 5 or customize the settings.
Figure 5. Specify DB Details
Specify a DB Instance Identifier (oradb) and specify the Master Username and Password as shown in Figure 6. Click on Next Step.
Figure 6. Specifying DB Instance Identifier
In Configure Advanced Settings specify the advanced options as shown in Figure 7. In Network & Security settings for VPC select Create new VPC and for Subnet Group select Create new DB Subnet Group. For Publicly Accessible select Yes. For Availability Zone select No Preference. For VPC Security Group (s) select Create new Security Group. Specify Database Name (ORCL), Database Port (1521) and keep the default settings for DB Parameter Group and Option Group.
Figure 7. Specifying Advanced Options
Select the Character Set Name and Enable Encryption. Keep the default settings for Backup, Monitoring and Maintenance. Click on Launch DB Instance as shown in Figure 8.
Figure 8. Launch DB Instance
The message “Your DB Instance is being created” gets displayed to indicate that the DB instance is starting. Click on View Your DB Instances as shown in Figure 9.
Figure 9. View Your DB Instances
The DB Instance for Oracle Database gets created and an Endpoint gets displayed, as shown in Figure 10.
Figure 10. DB Instance oradb Created
Having created a data source, in this section we shall we shall create a connection with the data source. Click on Create a new connection as shown in Figure 11.
Figure 11. Create a new connection
In the list of data sources select Oracle as shown in Figure 12.
Figure 12. Selecting Oracle as Data Source
In the Create New Connection dialog the Group is pre-selected as Oracle because in the previous screen the Oracle database was selected as the data source. In the Login tab specify Database name as XE and specify a User name and Password, which must be the same as the Username and Password specified when creating the Oracle Database Instance on RDS. Select Connect as: Default. In the Oracle Client click on TNSName Editor for the TNSNames.ora file as shown in Figure 13.
Figure 13. Create New Connection
We shall use the configuration values required in the TNSName Editor from the Configuration Details. The DB Name ORCL indicated in Figure 14 is the SID for the CONNECT_DATA in the TNSName Editor.
Figure 14. DB Name
The Endpoint indicated in Figure 15 is the HOST in the TNSName Editor. The Port is listed as 1521 and is to be used for the PORT in the TNSName Editor.
Figure 15. Endpoint and Port
The connect string for the XE database which was selected in Create New Connection is shown in Figure 16.
Figure 16. Connect String for the XE Database with HOST, PORT and SID specified
Click on File>Save to save the TNSNames.ora file as shown in Figure 17.
In the Create New Connection click on Connect to connect to the data source, which is Oracle Database, as shown in Figure 18.
Figure 18. Connect
A new connection Oracle>XE gets created and added to the Navigation Manager, as shown in Figure 19.
Figure 19. New Connection for Oracle Data Source
Right-click on the XE connection to display the different options as shown in Figure 20.
Figure 20. Connection Options
To create a database table right-click on the Oracle>XE connection and select Operations>Create Table as shown in Figure 21.
Figure 21. Selecting Operations>Create Table
The Create Table wizard gets displayed, as shown in Figure 22.
Figure 22. Create Table Wizard
Specify the table name in the Name field as WLSLOG as shown in Figure 23. Click on Add to add column names as shown in Figure 23. Specify 1st Column Name as ID, Type as VARCHAR(255), and select the Not Null checkbox. Click on the Set Primary Key to make the ID column as the primary key column. Click on Add to add other columns.
Figure 23. Adding the Primary Key Column
Add columns ID, TIME_STAMP, CATEGORY, TYPE, SERVERNAME, CODE, MSG as shown in Figure 24. Click on Show Script to list the script.
Figure 24. WLSLOG Table Columns
The SQL Script gets displayed, as shown in Figure 25. Click on Execute to run the SQL script.
Figure 25. SQL Script for creating a table
A database table gets created, as indicated by the message in Figure 26.
Figure 26. Database Table created
The WLSLOG table and the structure get listed in the Tables tab, as shown in Figure 27.
Figure 27. WLSLOG Table
In this section we shall add data to the WLSLOG table. We shall run the following SQL statements.
INSERT INTO WLSLOG (ID, TIME_STAMP, CATEGORY, TYPE, SERVERNAME, CODE, MSG) values ('logentry1','Apr-8-2014-7:06:16-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STANDBY');
INSERT INTO WLSLOG (ID, TIME_STAMP, CATEGORY, TYPE, SERVERNAME, CODE, MSG) values ('logentry2','Apr-8-2014-7:06:17-PM-PDT',
'Notice','WebLogicServer','AdminServer','BEA-000365','Server state changed to STARTING');
INSERT INTO WLSLOG (ID,TIME_STAMP, CATEGORY, TYPE, SERVERNAME, CODE, MSG) values ('logentry3','Apr-8-2014-7:06:18-PM-PDT',
'Notice', 'WebLogicServer', 'AdminServer', 'BEA-000365', 'Server state changed to ADMIN');
INSERT INTO WLSLOG (ID,TIME_STAMP, CATEGORY, TYPE, SERVERNAME, CODE, MSG) values ('logentry4','Apr-8-2014-7:06:19-PM-PDT',
'Notice', 'WebLogicServer', 'AdminServer', 'BEA-000365', 'Server state changed to RESUMING');
INSERT INTO WLSLOG (ID,TIME_STAMP, CATEGORY, TYPE, SERVERNAME, CODE, MSG) values ('logentry5','Apr-8-2014-7:06:20-PM-PDT',
'Notice', 'WebLogicServer', 'AdminServer', 'BEA-000361', 'Started WebLogic AdminServer');
INSERT INTO WLSLOG (ID,TIME_STAMP, CATEGORY, TYPE, SERVERNAME, CODE, MSG) values ('logentry6','Apr-8-2014-7:06:21-PM-PDT',
'Notice', 'WebLogicServer', 'AdminServer', 'BEA-000365', 'Server state changed to RUNNING');
INSERT INTO WLSLOG (ID,TIME_STAMP, CATEGORY, TYPE, SERVERNAME, CODE, MSG) values ('logentry7','Apr-8-2014-7:06:22-PM-PDT',
'Notice', 'WebLogicServer', 'AdminServer', 'BEA-000360', 'Server started in RUNNING mode');
To run an Insert SQL statement right-click on the WLSLOG table and select Generate SQL>Insert Statement>To Editor as shown in Figure 28.
Figure 28. Generate SQ>Insert Statement>To Editor
An SQL Editor gets displayed for an INSERT INTO statement, as shown in Figure 29. The column names are pre-specified in the INSERT INTO statement and only the column values need to specified.
Figure 29. SQL Editor for Insert Statement
Copy the values from the first INSERT INTO statement listed earlier in this section to the statement in the SQL Editor and click on Run SQL as shown in Figure 30.
Figure 30. Run SQL Statement
A row of data gets added, as indicated by the “1 rows affected” message in Figure 31.
Figure 31. One data row added to WLSLOG Table
Similarly add other data rows by creating another SQL Editor or using the same one. A SQL Editor script may be saved with Save File As as shown in Figure 32.
Figure 32. Save File As
Specify a File name (wlslog3) and click on Save as shown in Figure 33.
Figure 33. Saving a SQL Script
Click on Close to close a SQL script as shown in Figure 34.
Figure 34. Closing a SQL Script
Click on Format SQL to format the SQL script as shown in Figure 35.
Figure 35. Format SQL
Similarly, run the other SQL statements as shown in Figure 36.
Figure 36. Running SQL Statements to add Table Data
The data added with INSERT INTO SQL statements is not added to the database table till the connection is committed. A connection is committed or rolled back with two buttons: one with an upward blue arrow (for rollback) and the other with a downward red arrow (for commit), as shown in Figure 37. Click on the button to commit the connection.
Figure 37. Buttons for Commit and Rollback
As indicated by the message “Connection committed” in Figure 38, the connection gets committed.
Figure 38. Connection committed
If a connection is to be rolled back, click on the button with the upward blue arrow as shown in Figure 39.
Figure 39. Rollback Button
As indicated by the message “Connection rolled back” in Figure 40, the connection gets rolled back.
Figure 40. Connection Rolled Back
To auto commit connection, select Turn On as shown in Figure 41.
Figure 41. Setting AutoCommit On
AutoCommit gets set to On, as shown in Figure 42.
Figure 42. AutoCommit On
If the AutoCommit is Off and some changes have not been committed yet and the connection is reconnected with Reconnect, as shown in Figure 43, a dialog is displayed to either commit or rollback the pending changes before the connection is reconnected.
Figure 43. Reconnecting a Connection
Toad Data Point provides a Query Builder to query a data source. To use the Query Builder, right-click on the table WLSLOG and select Query Builder as shown in Figure 44.
Figure 44. Selecting Query Builder for a Data Source
The Query Builder may also be selected from the New Query Builder option, shown in Figure 20 displayed from the XE connection. Another option is to select Build>Query Builder as shown in Figure 45.
Figure 45. Build>Query Builder
The Query Builder gets displayed as shown in Figure 46.
Figure 46. Query Builder
Select Add All Columns to select all columns for a query as shown in Figure 47. Click on Execute SQL to run the SQL query.
Figure 47. Selecting all columns
Select the Results tab for the results as shown in Figure 48. If data is added/removed from a table the Execute SQL must be run again to display a modified result set.
Figure 48. Query Results
The Query Results may be saved with Save File As, as shown in Figure 49.
Figure 49. Saving Query Results
Click on Yes in the Save Results dialog as shown in Figure 50.
Figure 50. Save Results dialog
Specify the file name to save the query results and click on Save as shown in Figure 51.
Figure 51. Saving Query Results
A SQL query may also be run with the SQL Editor. From the WLSLOG select Generate SQL>Select statement>To Editor as shown in Figure 52.
Figure 52. Selecting Generate SQL>Select statement>To Editor
The SQL Editor gets displayed with the default SQL Select statement. as shown in Figure 53. Click on Run SQL to display a result set.
Figure 53. Select Statement in SQL Editor
The SQL Editor may also be selected from the Oracle>XE connection, as shown in Figure 54.
Figure 54. Selecting New SQL Editor
Specify a Select statement and click on Run SQL as shown in Figure 55.
Figure 55. Running a Select Statement in SQL Editor
The result set gets displayed, as shown in Figure 56.
Figure 56. Result Set
The SQL Editor may also be selected from Edit, as shown in Figure 57.
Figure 57. Edit>SQL Editor
In this article we discussed connecting to Oracle Database on RDS from Toad Data Point. Toad Data Point simplifies Oracle database development and management. In a subsequent article we shall discuss creating reports in Toad Data Point with Oracle database on AWS RDS as the data source.
Written by Deepak Vohra
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.