SQL Connect – The robust solution for running ad-hoc SQL Queries. This helps to fast-track the SQL Query development without having to go and edit the data model every time.
If you are working on Oracle Cloud and missing the TOAD/SQL Developer from your PeopleSoft or EBS days, then you could try SQL Connect which solves your purpose.
Table of Contents
Features of SQL Connect
- Rapid access to the database from your desktop – both Mac and Windows.
- Secure. Web Service Driven. Real-time data.
- Intellisense. SQL Formatting.
- Supports Single Sign-on
- Export to Excel/CSV
- Database Browser
- Secured by Oracle Cloud Role access.
- Ability to store multiple connections.
- Supports parameters in the query.
- Ability to save, search, find-replace, and manage multiple queries/files.
- On the fly reporting for multiple PODs at the click of a button
The tool is secure as they don’t store any data like POD URL, username, and password on their server. The entire data is retrieved from Web APIs using the provided credentials using the roles the user has.
SQLConnect can be used for any Oracle Cloud pillar like HCM, ERP, SCM, PPM, CX, etc. and it also supports single sign-on capability.
It helps in increasing the productivity of the developers creating/analyzing reports. If you are working on a support project this tool will be a savior for you.
Let’s look into how the tool looks like:
Creating a Connection
Click on + symbol on Connections
Click on Validate Setup.
If the credentials are valid, the setup will be successful.
Click OK and click on Create Connection.
It will show like below:
Click on + icon at the top to create an editor for SQL Query and once you start entering the table names, it will start suggesting the tables that start with the text whatever we have entered.
Once you run the query, we can see the output and we can also extract it into CSV or Excel on the click of a button.
We can also see the timings, rows fetched and the actual query which was run by clicking the “View Sql” option.
This tool also supports the usage of parameters and intelligently detects the parameter type.
We can always toggle an instance within a click and run the query against any instance.
It can also format the SQL Query within a click of a button
It also supports a lot of shortcuts that we can use to make our life easier while using this tool.
Ex: If you select a date column and click Control + D, it will apply MM/DD/YYYY formatting automatically.
Select table name and click F4 for database browser
The database browser shows the entire table structure and also prepares a select all fields from that table query which can be seen by clicking “View Sql” button. This would be very handy if we want to retrieve multiple columns and we had to copy-paste every column.
It is also possible to use the column mode to add text by using the Alt button and selecting the rows. It can be handy when we have to add multiple values in the where clause.
We can bulk comment/uncomment lines by selecting the rows and pressing ctrl + /
It will comment if it has not been commented.. it will uncomment if the rows are already commented.
- SQLConnect does not store any username/password information for your Oracle Cloud environment.
- The username and Password for each connection are encoded and stored locally on your (user’s) desktop. This information does not get stored on our server.
- When you run queries, our application connects to Oracle Cloud and gets data in a standard XML/JSON format, and converts it to show you on the display – no data is stored on our server.
- Users can choose to export the data and save it locally – but OracleCloudTools does not store any data on the server.
- You can also go to http://oracloudquerytool.com/bipublishersecuritysetup/ to find out specific setups required for BI Publisher security setups for accessing Oracle HCM and Oracle ERP cloud data via Adhoc SQLs in SQLConnect.
- When you run a Query in Oracle Cloud Tools SQLConnect, it calls the standard Oracle BI Publisher API and sends your query to the API. The API in turn runs our data model in the BI environment, gets the data required for your query, and sends it back in a specific format. Our application then converts that data into a readable format and presents that on the User Interface where you can copy-paste and work with that data. There is no easier way to query your oracle hcm cloud or oracle ERP cloud data.
They also help developers by posting frequently used sample queries
The team is working on adding further enhancements to this product. They want to make it the best product out there in the market which will help make developers productive with their day-to-day tasks.
Some of the enhancements in pipeline include:
- Fast Formula Editor and DBIs search
- Ability to store quick snippets for ease of use
Check out their FAQ section if you have any questions.
They are currently offering a 15 day trial period for all users.
Differences between Individual/Enterprise plans:
We can download the latest version of SQLConnect tool from here.
Even though this SQL Connect looks a bit pricey from an individual perspective, the organizations can think of this option and reap the benefits with more productive developers.
This tool has lot of features and can do wonders if we explore and utilize it to the best.
Upcoming Webinar on SQL Connect
SplashBI is conducting a webinar on this tool on April 15th, 2021. If you are interested in knowing more details on this tool, click here to register.If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.