Oracle Application Express - Build SQL command workshop

0
votes

My environment :

  • Oracle Database 12.2
  • Oracle Application Express 5.1.4

I am having a lot of issues with users running queries with SQL Developer, a problem I already described in this question some days ago

Oracle Parallel Query behaviour with IDE tools as SQL Developer or Toad

As a intermediate solution, I was wondering on how Oracle did build the SQL Worksheet in Oracle Live SQL. You might have a look here

Live SQL

My idea would be to build a small apex application that might provide a similar functionality, thereby the users can access to a SQL editor of some kind and run queries. With this method, I can use the Java pool of ORDS through Tomcat to take care of reuse existing sessions and close ones with no use, avoiding my current issue with so many active parallel slaves in status active when their query coordinators are inactive in SQL Developer. I know for a fact that with a Java Pool I don't suffer the problem of slaves in status active when their parent processes are inactive.

I know that I might create a user with only access to the SQL Workshop, removing it from accessing the other components of the workspace, but that implies that the user must have access to the workspace itself, which I can't do in Production, unless I don't find any other way.

enter image description here

Does anyone know of any plugin or any other way that I can have the same in-built functionality but inside an application ?

Thank you

1
oracleoracle-apexoracle12coracle-apex-5.1
your problem isn't with the tools or the database per se, but your users they open queries and don't grab all the data, hence the processes are left idle/waiting on the server. have you considered a resource consumer group? you could set idle periods that would throw their sessions away...or prevent them from consuming more than X parallel sessions if using a query tool like sqldev. – thatjeffsmith
comment two, you don't need to build anything, we already did, it's called SQL Developer Web, runs out of ORDS, is like the SQL Workshop in APEX, only more complete/modern. The queries runs out of it are paged, so even though they do select * from, we only really get the first 25 records, and we're DONE - nothing left hanging around on the server to worry about – thatjeffsmith
@thatjeffsmith, regarding you point 1, indeed I tried resource manager, but at the end I ran in more problems than the ones it could have solved. I know now why those slaves remained active, so I am looking for a solution to avoid using SQL Developer , or any other IDE for that matter, in Production – Roberto Hernandez
@thatjeffsmith, regarding your second point, I know about SQL Developer Web, but as far as I know, it is not compatible with APEX 5.1.4, and even more, I though it was only available for Oracle Cloud. Do you know otherwise ? can it be installed on 12.2 with Apex 5.1 and ORDS 3.0.2 ? – Roberto Hernandez
Sure its compatible with apex 5.1 and it's available on prem...ords 302 isn't appropriate for ANY environment... upgrade your ords pronto – thatjeffsmith

1 Answers

1
votes

I have created an application in Oracle Apex 20.2. It is the same as the option SQL Commands in SQL Workshop. You can download it from GitHub via the following link:

https://github.com/devvinish/sql-command

Below is the screenshot of the app:

enter image description here