In this article we will look into a standalone tool which can connect to the Oracle Cloud Instances and run SQL queries against them. I know most of you know that such tools are available on the market at some cost with advanced features.
This is not a paid post nor a promotion of any kind. I am going to share a FREE tool which can be downloaded and used by anyone. Of course it is still in nascent stages, but the developer is working on adding new features to it.
This tool (FusionSQLite) has been created by Anand Vamsi Krishna Vegaraju and he was kind enough to share it to everyone. You can reach out to him if you have any questions or want to say to thanks for this tool.
This is a third party tool and not a tool provided by Oracle. So do your due diligence before using it by reading through the documentation.
Download Link for Tool – https://github.com/anandvegaraju/FusionSQLite
If you open this page, you will see the below info which will help you through the steps to use this tool.
Click on the above link to download the executable file.
Once the file is downloaded, you can double click it to open the tool UI. This tool is like a portable software which doesn’t need any installation. If you don’t have admin access on your office laptop, even then you can use this tool as it doesn’t require installation.
This is how the tool UI will look like:
The UI looks self-explanatory..
We need to input the parameters like below:
POD URL – https://abcd-dev.fa.us2.oraclecloud.com (Remember to include the https:// too and copy the POD link till oraclecloud.com)
Username – <Username> (Ensure you have the Human Capital Management Integration Specialist role)
Password – <Password of user>
After entering these parameters, click on “Initialize environment” which will import a catalog file to the POD using which the SQL Queries would be run from the tool. This is a one time activity for each POD that you use on this tool. You will get a successful message once the catalog file has been copied to the POD. The Catalog file will be copied to “/shared/Custom/Human Capital Management/FusionSQLtoolTest1” folder.
Click OK.
Now you are ready to run the SQL queries using this tool.
Let’s run a sample query..
select * from per_all_people_f where rownum < 10
If you click on Export to Excel option, you can get the data downloaded into the excel sheet. It will ask for a file name and will get saved to the folder in which the exe file is saved.
As seen above it’s very simple to use this tool. Anand Vamsi Krishna Vegaraju is working on adding additional features to this tool. Kudos to his efforts in building this and sharing it to the community for FREE.
Tip: Even though this tool works for bigger data, it might get struck for a while before showing results. It’s better to use the query with rownum condition to restrict the number of rows. Although this cannot be used as efficiently as SQL Developer tool, this is a good starting point to use for smaller queries.
Hope you like this article. You can reach out to Anand on LinkedIn @ https://www.linkedin.com/in/anand-vamsi-krishna-vegaraju-084718b7/
This is useful. In this tool we cant keep multiple queries and run one after one like SQL developer by keeping ; for each query.
Yes Suresh. Anand is working on enhancing this tool. For something you get for FREE, you should not complain unless it doesn’t work at all or you have a better alternative. You can provide your feedback to the developer on Linkedin page.
Suresh, You can use SQLConnect (Download for FREE from http://www.oraclecloudtools.com) for running multiple queries, and using Multiple Pods at the same time. It has a lot more features, and ofcourse that is why its a paid tool ($9.99 / month).
I am getting below error for PROD, can you help?
oracle.xdo.webservice.exception.OperationFailedException: PublicReportService::generateReport failed: user: xxx.yyy does not have permission to run the report: /Custom/Human Capital Management/FusionSQLtoolTest1/FSTreport.xdo
Hi
I am getting the below error. Can you please help?
oracle.xdo.webservice.exception.OperationFailedException: PublicReportService::generateReport for reportAbsolutePath [/Custom/Human Capital Management/FusionSQLtoolTest1/FSTreport.xdo] failed: due to oracle.xdo.server.ServerException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: SQLInjection Error: Invalid parameter value SELECT *
FROM per_all_people_f
WHERE ROWNUM < 10
Hi Charan,
Looks like an issue with the POD related to some permissions.. Check other POD.
Thanks,
Sricharan
Hi Vamshi,
Looks like a permissions issue. Check other POD or try using some super user.
Thanks,
Sricharan
Hi, Thanks for the tool. The tools disappears as soon as I click on “Initialize environment”. Please advise.
Hi Swetha,
try to input the url like “https://abcd-dev.fa.us2.oraclecloud.com”.. if you use “https://abcd-dev.login.us2.oraclecloud.com” then the window is getting closed..
Thanks,
Sricharan
Hi,
is this tools works when you have SSO configured? I did everything explained here and the tool closes when I cllick on “Initialize enviroment”. By the way, I´m using a user with admin roles.
Regards,
Ricardo
Hi Ricardo,
I am not sure if it works with SSO. Can you check with original developer by reaching out to him on linkedin Anand Vamsi Krishna Vegaraju.
Thanks,
Sricharan