Search

Top 60 Oracle Blogs

Recent comments

Using DbVisualizer to work with #Oracle, #PostgreSQL and #Exasol

As a Database Developer or Database Administrator, it becomes increasingly unlikely that you will work with only one platform.

It’s quite useful to have one single tool to handle multiple different database platforms. And that’s exactly the ambition of DbVisualizer.

As a hypothetical scenario, let’s assume you are a database admin who works on a project to migrate from Oracle to EDB Postgres and Exasol.

The goal might be to replace the corporate Oracle database landscape, moving the OLTP part to EDB Postgres and the DWH / Analytics part to Exasol.

Instead of having to switch constantly between say SQL Developer, psql and EXAplus, a more efficient approach would be using DbVisualizer for all three.

I created one connection for each of the three databases here for my demo:https://uhesse.files.wordpress.com/2019/07/dbviz1.png?w=150&h=71 150w, https://uhesse.files.wordpress.com/2019/07/dbviz1.png?w=300&h=143 300w, https://uhesse.files.wordpress.com/2019/07/dbviz1.png?w=768&h=365 768w, https://uhesse.files.wordpress.com/2019/07/dbviz1.png 809w" sizes="(max-width: 620px) 100vw, 620px" />Now let’s see if statements I do in Oracle also work in EDB Postgres and in Exasol:

https://uhesse.files.wordpress.com/2019/07/dbviz2.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2019/07/dbviz2.png?w=300&h=118 300w, https://uhesse.files.wordpress.com/2019/07/dbviz2.png?w=768&h=302 768w, https://uhesse.files.wordpress.com/2019/07/dbviz2.png 956w" sizes="(max-width: 620px) 100vw, 620px" />

Oracle

https://uhesse.files.wordpress.com/2019/07/dbviz3.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2019/07/dbviz3.png?w=300&h=116 300w, https://uhesse.files.wordpress.com/2019/07/dbviz3.png?w=768&h=298 768w, https://uhesse.files.wordpress.com/2019/07/dbviz3.png 951w" sizes="(max-width: 620px) 100vw, 620px" />

EDB

https://uhesse.files.wordpress.com/2019/07/dbviz4.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2019/07/dbviz4.png?w=300&h=116 300w, https://uhesse.files.wordpress.com/2019/07/dbviz4.png?w=768&h=296 768w, https://uhesse.files.wordpress.com/2019/07/dbviz4.png 954w" sizes="(max-width: 620px) 100vw, 620px" />

Exasol

Works the same for all three! The convenient thing here is that I just had to select the Database Connection from the pull down menu while leaving the statement as it is. No need to copy & paste even.

What about schemas and tables?

https://uhesse.files.wordpress.com/2019/07/dbviz5.png?w=150&h=57 150w, https://uhesse.files.wordpress.com/2019/07/dbviz5.png?w=300&h=114 300w, https://uhesse.files.wordpress.com/2019/07/dbviz5.png?w=768&h=292 768w, https://uhesse.files.wordpress.com/2019/07/dbviz5.png 818w" sizes="(max-width: 620px) 100vw, 620px" />

Oracle

In EDB, I need to create a schema accordingly:

https://uhesse.files.wordpress.com/2019/07/dbviz6.png?w=150&h=67 150w, https://uhesse.files.wordpress.com/2019/07/dbviz6.png?w=300&h=134 300w, https://uhesse.files.wordpress.com/2019/07/dbviz6.png 696w" sizes="(max-width: 620px) 100vw, 620px" />

EDB

 

In Exasol, schema and table can be created in the same way:

https://uhesse.files.wordpress.com/2019/07/dbviz7.png?w=150&h=72 150w, https://uhesse.files.wordpress.com/2019/07/dbviz7.png?w=300&h=143 300w, https://uhesse.files.wordpress.com/2019/07/dbviz7.png 740w" sizes="(max-width: 620px) 100vw, 620px" />

Exasol

Notice that the data types got silently translated into the proper Exasol data types:

https://uhesse.files.wordpress.com/2019/07/dbviz8.png?w=150 150w, https://uhesse.files.wordpress.com/2019/07/dbviz8.png?w=300 300w" sizes="(max-width: 608px) 100vw, 608px" />

Exasol

There is no DBA_TABLES in Exasol, though:

https://uhesse.files.wordpress.com/2019/07/dbviz9.png?w=150&h=44 150w, https://uhesse.files.wordpress.com/2019/07/dbviz9.png?w=300&h=88 300w, https://uhesse.files.wordpress.com/2019/07/dbviz9.png?w=768&h=225 768w, https://uhesse.files.wordpress.com/2019/07/dbviz9.png 801w" sizes="(max-width: 620px) 100vw, 620px" />

Exasol

Of course, there’s much more to check and test upon migration, but I think you got an idea how a universal SQL Client like DbVisualizer might help for such purposes.