Are there any shareable/public online Oracle instances that have Oracle Spatial enabled?
Use Case:
Mock up a Oracle Spatial SQL problem/solution and easily share it via a public URL.
Add a comment
|
1 Answer
It looks like db<>fiddle (dbfiddle.uk) can be used for this.
Notes:
- The Oracle 18c and 21c versions of db<>fiddle seem to have Oracle Spatial enabled. But 11gR2 threw a few errors, so my best guess is that Spatial isn't enabled in 11gR2.
- In 18c and 21, I tested using this example: Snap a point to a line and get the line ID. Db<>fiddle was able to successfully use the Linear Referencing package, which I believe is only available in Oracle Spatial—proving that Spatial is enabled.
- The only trouble I had was that db<>fiddle didn’t know how to select rows where there was a column with the SDO_GEOMETRY datatype. Often, SQL clients are able to select some basic text from that datatype. But db<>fiddle seemed to just not select any rows at all, which was confusing. I had to explicitly convert the column to text in order to select rows. But once I did that, it worked.
- https://dbfiddle.uk is not to be confused with http://www.db-fiddle.com or https://sqlfiddle.com. The first link is used heavily by the guys on https://dba.stackexchange.com. I don't have any experience with the second or third links. Although it seems like DBAs prefer to use the first one.
- The sessions in https://dbfiddle.uk are permanent, public, and shareable. Just copy the URL to share the work that you did.
- Each line is an individual SQL statement. As far as I can tell, we can't put multiple statements in a single line. But, I think there are tricks for bulk-inserting data like putting a bunch of test data in a single WITH clause (Toad can generate the WITH clause automatically). There are probably other tricks too.
- Sometimes, a SQL statement needs to end with a
/. Similar to SQL Plus. More info here: DB<>Fiddle: Custom type and function (ORA-24344)
Oracle Live SQL is similar, but different.
- I assume Oracle Spatial is also enabled in Oracle Live SQL: https://livesql.oracle.com/
- But I'm guessing those sessions aren't permanent/shareable. So https://dbfiddle.uk might be a better option.