Oracle Cloud SQL Client

 Oracle Cloud SQL client - to run adhoc sql queries for OTM/ERP/SCM/HCM cloud to extract data. Currently cloud sql is in beta version

Assumptions:

1) User should be able to login with static user name and password (not single signon)

PreRequisites:

1) Open JDK 8 or Oracle JDK 1.8

2) Importing XDO file into Cloud XML Publisher instance

3) Permissions for user to access XML Publisher

4) CloudSQL Jar file

GitHub Location: https://github.com/csreedhar20/CloudSQL/tree/master

Installation Steps:

JDK Installation is out of scope and you can refer google for installation steps

Importing XDO file into Cloud XML publisher: (One time setup per instance)

1) Download CloudSQLDM.xdoz and CloudSqlReportCSV.xdmz files into desktop from github

2) Create new folder name "Test" inside Custom folder

3) Upload CloudSQLDM.xdoz and CloudSqlReportCSV.xdmz files to xmlpserver (screen shot above once uploaded)
4) In BI Publisher, give full control as shown below for CloudSQLDM and CloudSqlReportCSV

5) Double click on CloudSQL Jar file that was downloaded from GitHub and saved on your computer. Main window will be opened

  

  i) Connections button: 

         Connection Name: Any user identifiable text for connection

         User Name: Cloud instance user name

         Password: Password to connect to cloud instance

         EndPoint URL: https://<instance>.us2.oraclecloud.com/xmlpserver/services/PublicReportService

         ServiceAction: Test

Once details are entered, click on TestConnection and if user name, password, URL are correct then it will connect and give success message

After success message, connection will be saved automatically and click on 'X' icon at top of Connection window

ii) Click on new window and select appropriate instance where you want to execute sql query

 

iii) Type sql query and click on Execute button (Always use mouse/arrows to select and text will be changed to blue color)

iv) Once execute button is clicked, results will be displayed in table

      

For connecting to OTM cloud, user should be created as shown below

Ensure you select OBIEE Non-SSO User, assign Access control lists and BI roles as shown

Note: Tool is still in beta version, if you see any issues try to close it and open again.

All connections will be saved in same file as that of jar file, file name: connectionSetting.xml

Code is completely written in JAVAFX