1

Using the answer from the Oracle SQL Developer and PostgreSQL question, I have connected Oracle SQL Developer (4.1.3.20) to my PostgreSQL (9.1.20) database.

But only a weird subset of the objects in the database appears in the SQL Developer "Connections" browser. Specifically:

  1. The public schema (which is the default PG user data schema) doesn't show up. But if I create another schema (e.g. one named schema1 in the screen shots below), the schema does show up.
  2. Within a visible schema (schema1), tables, views, their columns, and their data are visible. So are triggers. There are headings for indexes and functions, but the functions, trigger functions, and indexes that exist are not visible. There aren't even headings for sequences, domains, catalogs, and extensions. Refreshing the display of the schema evokes 'ERROR: syntax error at or near ""INDEX_NAME"" Position: 99'.

Here it is in screen shots:

This is the database as shown in the PgAdminIII object browser

This is the database as shown in the Oracle object browser - also showing the above-mentioned error message

Another weirdness: I can open a SQL window in the Oracle tool and execute both DML (select, insert, etc) and DDL (create table, create function, etc), even in the (invisible) public schema. I can even query the PG catalogs (information_schema and pg_catalog).

So the question is: How can I make the missing objects (most importantly, the public schema) visible in SQL Developer?

~ Thanks, Ken

Community
  • 1
  • 1
Ken
  • 43
  • 2
  • 5
  • Are you really wedded to SQL Developer? While it might be possible to connect to PostgreSQL, it certainly isn't designed with PG in mind. I seriously doubt it will ever really work well. pgAdmin is far more common. – jpmc26 Mar 05 '16 at 05:39
  • Not wedded; not even engaged; just dating ;-) – Ken Mar 05 '16 at 20:19
  • _Sorry, sent off the previous comment prematurely. To continue:_ pgAdmin is a great DB management tool (I love it and have used it for years), but what I also need is a data model diagrammer, and one that is integrated / synchronized with the PG databases that implement the diagrams. I have tried the most promising of those listed at https://wiki.postgresql.org/wiki/GUI_Database_Design_Tools and other places, and they all have fallen short in one way or another. Would welcome suggestions. Maybe this should be in a new thread? Or is there such a thread already? – Ken Mar 05 '16 at 20:34
  • Then I strongly recommend just finding a different tool. It doesn't have to be pgAdmin. Oracle's entire system is extremely quirky and unintuitive (from the nonstandard system views to the lack of `OFFSET`/`LIMIT` or similar to `DBMS_OUTPUT` to `DUAL` to the conflation of users and schemas). It really hasn't changed that much from the 90s... SQL Developer was designed for Oracle, and the differences between it and other databases is so vast that I doubt any tool designed for Oracle first will function well with another DB. – jpmc26 Mar 05 '16 at 20:38
  • You might try [Software Recommendations](http://softwarerecs.stackexchange.com/). See [this](http://meta.softwarerecs.stackexchange.com/q/336/2481) before you post anything there, though. They have very strict standards (by necessity of the nature of the site's target subject). – jpmc26 Mar 05 '16 at 20:44
  • SQLDeveloper can use any JDBC driver to reach an external db, and I don't think it is unreasonable to expect it to work. But I think there are probably some quirky limitations. In your case, was your database name and your db username the same, or different? I had similar issue when they were different. – broc.seib Jan 18 '17 at 19:13

1 Answers1

0

With SQLdeveloper 4.2, public schema is back ;o)

PAscal
  • 11
  • 1