5

This won't work in Postgres since it doesn't have any DUALs.

SELECT 'Ok' AS RESULT FROM DUAL

On the other hand, this won't work in Oracle since it requires a FROM clause

SELECT 'Ok' AS RESULT;

The idea is to execute some dummy SQL merely to check the connection.

How do I do it in a way that is supported by both RDBMSes?

Ideally it should work with versions dating back to:

  • Postgres 13.14
  • Oracle 11.2.0.3.0

but I'm interested in more modern solutions as well if there is nothing compatible.

Improve this question
16
  • 2
    Why do you need it? Since Java 11 JDBC standard supports Connection.isValid(). So it is up to JDBC drivers to provide platform independent Connection check. Commented Jun 23 at 15:14
  • 1
    @ibre5041 oh, I forgot to tell. We have Java 8. Edited the question Commented Jun 23 at 17:54
  • If the statements caused an error that is not connection related, would that be a problem? Commented Jun 24 at 6:22
  • 1
    @ibre5041 hmm, the javadoc says it's available since Java 6. It may be a way to go, after all, may it not? Commented Jun 24 at 7:04
  • 1
    @SergeyZolotarev Then phrase it as something like "Ideally it should work with versions dating back to X, but I'm interested in more modern solutions as well if there is nothing compatible". It's still better than you having to comment on every answer that it doesn't work for you… Commented Jun 24 at 8:44

5 Answers 5

Reset to default
8

This appears to work on both Oracle SQL and PostgreSQL (latest versions):

SELECT IsStillAlive
FROM (values(1)) AS TheConnection (IsStillAlive);

This uses the VALUES constructor feature.

Improve this answer
3
  • 2
    Oh nice, they finally implemented it. Commented Jun 23 at 20:45
  • We still use Oracle 11, though Commented Jun 24 at 6:59
  • 2
    The versions of Oracle supporting VALUES also don't require a FROM Commented Jun 24 at 17:29
7

I don't think there is such a statement.

As a workaround, you could create a view in Postgres

CREATE VIEW dual AS SELECT 'X' AS dummy

and add it to your template database.

Improve this answer
6

With recent enough versions of Oracle (18c or later) and Postgres (17 or later) you could use json_table():

select j.*
from 
json_table('
    {"t":[
        {"txt":"ok"}
        ]}', 
    '$.t[*]' 
    columns(txt varchar(10) path '$.txt')
) j;

Tested in dbfiddle:
Oracle 18c
PostgreSQL 17

Improve this answer
2
  • Sadly, both our databases are well below versions that support this Commented Jun 24 at 7:01
  • If you want to use a single approach for older versions of both Oracle and Postgres, then the view answer by mustachio might be your best bet. Commented Jun 24 at 7:26
3

Oracle Database 23ai has become very developer friendly. No need for 'dual' any longer. Utilizing SQLcl aliases you can even list your tables the PostgreSQL psql way!

ADMIN@DEMODB_HIGH🍻🍺 > @version

VERSION
_______________________________________________________________________________________________________
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems

ADMIN@DEMODB_HIGH🍻🍺 > select 1;

   1
____
   1


ADMIN@DEMODB_HIGH🍻🍺 > \dt

PL/SQL procedure successfully completed.

'tables only'

USER||'.'||TABLE_NAME
__________________________________
ADMIN.OBJECT_STAGE
Improve this answer
1
  • We are way below that version, see my last comment Commented Jun 24 at 6:57
2

Both RDBMSes should be able to create empty tables if you use a data type that both can use.

CREATE TABLE compatibility_test
( string_column VARCHAR(5)
) ;

You will need to drop the table though.

Improve this answer
4
  • Yeah, I though about it. Though, I wasn't totally comfortable creating any DB objects, even if they are DROPed eventually Commented Jun 24 at 8:36
  • What if you ensure both connections lack the privileges - will you get a consistent error? Commented Jun 24 at 8:45
  • Idk, it's kind of feels off. I should not rely on external state for that Commented Jun 24 at 11:08
  • 2
    Fair enough! I see both systems support DROP DATABASE, which I would not recommend using as a test command under any circumstances. :) Commented Jun 24 at 17:06

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.