Oracle SQL Developer is an Integrated Development Environment (IDE). It has been used worldwide for many years, but other IDEs emerged over time. They came with more features, facilities, and improvements, and made database development, administration, and SQL coding much less painful.
dbForge Studio for Oracle is one of those IDEs. It offers a simple and intuitive interface, practicality in managing databases and schemas, ease of export and import (expdp and impdp) of schemas, agility in writing PL/SQL, and many more features.
Oracle SQL Developer and dbForge Studio for Oracle share some similar features, but there are also big differences. For example, Oracle SQL Developer does not require installation – it can be downloaded and run. dbForge for Oracle requires installation, but it is very practical, fast, and intuitive. dbForge Studio for Oracle has advantages making it the best option in many cases, whatever your purposes are – from database administration to SQL development.
Let’s get familiar with the essential differences.
In both IDEs, we can set the desired schema using the command change session set current_schema = SCHEMA_NAME.
In Oracle SQL Developer, when we define the schema this way and start writing a query, the IDE will not bring us suggestions of information based exclusively on that specific schema. Nor will it offer the possibility of using a hint in writing. The thing is, it does not understand that the schema has already been defined previously. As a result, we have to code everything manually.
In dbForge Studio for Oracle, with the same command executed to define the schema, a range of options and opportunities occur for writing the query. For example, when we insert a table during query writing, the IDE suggests relevant hints automatically. It could hint to us to use a simple JOIN or to choose from all possible JOIN options (and it provides those options). To view all the schemas, select Show all Objects in the top corner.
The Query Builder allows us to create queries visually without the need to write them manually. Both IDEs have this option.
To use it, select all the desired tables (multiple tables) in the left menu and drag them to the field used by the Query Builder (open in New Query). However, Oracle SQL Developer requires that we create relations between tables and necessary columns of each table and then create the SQL script. dbForge Studio for Oracle creates these relations between selected tables automatically. You only need to define desired fields in each table to create the script.
Have a look at the below illustration with three tables. Two of them correlate, and the third one does not.
To explore the schema objects in SQL Developer, right-click on a table on the left. You can rename, change, drop objects, and perform other actions. It also applies to columns and indexes. In addition, by clicking the left button on a table, you launch a new window with tabs containing various information related to tables (also columns, data, models, constraints, grants, indexes, etc.). And you can modify these tabs.
dbForge Studio for Oracle provides the same options as SQL Developer and plenty of additional options. For instance, it allows you to create a Relationship Diagram. Go to the menu on the left and choose to create the Diagram from the Schema tables. Select all the necessary tables, right-click, and choose to Send To > Database Diagram. After that, the Studio will display the screen allowing us to observe all the relations, primary keys (each arrow of the diagram visually corresponds to a primary key), foreign keys, table fields, data types, and much more data.
In SQL Developer, the type of script formatting comprises SQL Standard. When inserting or writing a query, you can format it according to the SQL standard – ensure correct indentation, capitalization, etc. To do this, press CTRL + F7 and select the option to edit a query. Or, go to the top menu, click Tools > Preferences > Code Editor > Format > Advanced Format. There you can choose several options, such as alignment, spaces of indentation, line breakers, etc.
In dbForge Studio for Oracle, you format queries easier. Press CTRL + K + D or click Tools > Options > Text Editor > Formatting > Profiles > Edit/New Profile. This section offers diverse formatting options. For example, you can format a specific command automatically, such as the SELECT command. Choose it from the menu on the left and configure the formatting style for it by enabling and disabling options. The preview window on the right helps you estimate how that look suits your preferences.
Code Snippets are reusable templates containing a basic or generalist structure of an SQL statement or PL-SQL blocks.
To access this functionality in SQL Developer, go to the top menu > Tools > Preferences > Code Editor. From here, you can choose Advanced Format. That section allows you to change the default query format according to your needs or personal or professional preferences. Another option of choice is Code Templates where it is possible to change, add, or remove code templates. In SQL Developer, the Code Template has an ID used to “call” the template when writing SQL. For example, if there is an ID called ins, write “ins” in the worksheet and select the template that will appear. The script corresponding to that template will appear in the worksheet for you to use it.
In dbForge Studio for Oracle, press CTRL + W + T to display the Code Snippets menu on the right. The last icon of this menu refers to the Snippets Manager where you can customize, add, and remove SQL templates. Assume you want to create templates for specific DML statements. Then click Snippets Manager > SQL Syntax, and proceed to the DML Statements folder.
To use an existing template in dbForge Studio for Oracle, simply drag the block name to the “.sql” script window, and it will appear on the screen.
Debugging is essential for any SQL developer or database administrator as it helps identify possible execution problems of an SQL statement or PL-SQL block.
In SQL Developer, to debug, for example, a procedure, you must select the procedure on the left, right-click on it, select Compile for Debug, insert the Break Points, Run, insert the parameter values of that procedure (if necessary), and click OK. After that, it is possible to see the values of the fields by hovering the mouse over the object in the SQL text.
dbForge Studio for Oracle allows you to debug a procedure simpler. Just select the desired procedure, right-click on it, and choose Compile > Compile for Debugging > select the desired Object Name > Compile. Once this is done, select the Break Points, run, and insert the parameter values. Variables can be added to the WatchList (see the bottom of the menu). Values are changed in real-time at each step (right-click on the desired object > Add Watch).
It is often necessary to update one or more schemas in Testing or Staging environments to validate code changes, applications, etc. This operation suggests using export and import functions.
SQL Developer allows you to carry out the Export option. Go to the top menu > Database Export. A new Export Wizard window will open for you to insert, select, or deselect the desired options (objects, data, etc.).
With dbForge Studio for Oracle, it is possible to generate a schema script by simply right-clicking on the desired database > Export & Import > Schema Export. Then you should select or modify the necessary information. Moreover, It is possible to save the Export Schema script in Project format (Save Project) or Command Line format (Save Command Line), and later use the SQL script in a .bat file.
The image below shows the Schema Export Wizard. On the General tab, we choose the database information, schema, etc. And on the Export Content tab, we can check the options that will be included in the Export.
SQL Developer does not have a facilitator for interactions with Invalid Objects in the Oracle database. In dbForge Studio for Oracle, we can use the Invalid Object Manager, which is a tool making it very easy to compile Invalid Objects. Right-click on the database in question and select Invalid Object Manager. After that, choose Action Required or the necessary objects > Compile. The Status and Result of each compilation will follow. In the end, you can click Reanalyze to redo the validation and check if any object remains invalid.
The image below shows that the analyzed environment does not have Invalid Objects, only Valid Objects.
A powerful and very useful tool from dbForge Studio for Oracle allows us to compare schemas and data in different environments quickly and safely.
To access this functionality, go to the top menu > Comparison > New schema comparison. Then select the source database and destination for the comparison. It checks these databases on the connections created in dbForge Studio for Oracle. After that, select Source and Target, and all schemas will be shown in Schema Mapping. The option not to show the “systems schemas” (Hide System Schemas) is already enabled by default.
First, it displays what is or is not already different between schemas. Then we select the desired schema in the source and destination, click Compare, and the tool performs all the necessary comparisons.
If differences are detected, it generates the DDL of the table.
And we can execute it manually or automatically directly in the synchronization tool.
As exclusive to dbForger Studio for Oracle, Schema/Data Comparison helps DBAs to verify, for example, how updated the test environment is and if the application team can use it (they need an environment as close as possible to production). This activity is performed almost daily, so to avoid updating the entire environment, I use the Schema/Data Comparison tool and only update desired objects.
To sum it up, dbForge Studio for Oracle is often superior both in functionality and applicability. After getting familiar with dbForge for Oracle, I use it constantly, in all possible activities. For those looking for agility in PL/SQL writing, practicality in DBA activities, and security, dbForge Studio for Oracle is for you!
