PL/SQL Developer is an Integrated Development Environment that is specifically targeted at the development of stored program units for Oracle Databases. Over time we have seen more and more business logic and application logic move into the Oracle Server, so that PL/SQL programming has become a significant part of the total development process. PL/SQL Developer focuses on ease of use, code quality and productivity, key advantages during Oracle application development.
As a worst-case scenario, up to now you might have been working like this:
• You use a text editor to write program units (procedures, triggers, etc.).
• You use Oracle SQL*Plus to compile the source files.
• If there is a compilation error, you have to find out where it is located in the source file, correct it,
switch back to SQL*Plus to recompile it, only to find the next error.
• You use SQL*Plus or the client-part of your application to test the program unit.
• In case of a runtime error, again you have a hard time locating the cause of the problem and
• You use the Explain Plan utility or tkprof to optimize your SQL statements.
• To view or modify other objects and data in your database, you use SQL*Plus or yet another tool.
These tasks – editing, compiling, correcting, testing, debugging, optimizing and querying – can all be performed without leaving PL/SQL Developer’s IDE. Furthermore, PL/SQL Developer provides several deploy them on any platform without using PL/SQL Developer. You can edit many files at once through a standard multiple document interface. The editor offers a wide range of assistance to the programmer. There is context sensitive help on SQL statements and PL/SQLstatements. We’ve all been there: you start typing substr, but have forgotten the exact meaning of the parameters. Now you can simply hit F1 and you’re taken to the appropriate topic in the SQL Reference Manual. Tables, views and program units can be described for you in a roll-up window from within the editor in the same way. A Code Assistant is integrated into the editor that automatically displays information of database objects as you type their name, allowing you to browse and pick elements from this description. For large package or type bodies, the program editor provides a tree view with the code contents for easy navigation, highlights code structures and variable references, allows you to fold/unfold code sections, and provides hyperlink navigation. The Query Builder allows
you to graphically create select statements. PL/SQL Developer’s extensible templates make it easy to insert standard SQL and PL/SQL code into your programs. Al editors use the appropriate SQL, PL/SQL and SQL*Plus syntax highlighting to make your code more readable.
Compiling & correcting
From within the editor you can compile a source file. In case of a compilation error you are automatically taken to the appropriate source line. All compilation errors are reported in a list at the bottom of the editor. This list can include hints for code that may indicate common programming errors
or violations of user-defined naming conventions. Because you can compile a source file without saving it, you can safely explore many alternatives of
solving a problem. The editor keeps track of the fact that you have changed the file without saving or compiling it.
Testing & debugging
To test your program unit, you can write a test script. The body of the test script contains a PL/SQL block in which you can program the test-code. Any variables that you want to use in the PL/SQL block can be declared, assigned a value for input, and viewed after execution. When you execute a test script, a runtime error might occur. In this case, PL/SQL Developer allows you to view the sources of the error stack. Each source line of the error stack that was involved in the
runtime error is highlighted, so you can easily backtrack to the cause of the problem. If you are using Oracle 7.3.4 or later you can use PL/SQL Developer’s integrated debugger. You can step through your code, set breakpoints, view/set variables, view the call stack, and so on. On Oracle8i and later you can additionally use the dbms_trace feature to log selected events of a program run. This can help you analyze the program flow and exceptions. Output from calls to the dbms_output and the PL/SQL Web Toolkit packages are automatically shown in a corresponding tab page of the Test Window. For regression testing you can use the Test Manager to quickly run and verify a set of Test Scripts.
To optimize the SQL statements in your program units, Oracle’s Explain Plan utility can be a big help. Therefore it is integrated into PL/SQL Developer’s IDE. By simply selecting the SQL statement in the source file and pressing F5, the query plan is visually presented to you in a separate Explain Plan window. You can then modify the statement to optimize its query plan outside the source file, and copy it back afterwards. You can also view statistics about executed SQL statements and PL/SQL program units. These statistics can include elapsed time, CPU time, logical reads, physical reads, physical writes, and so on. Oracle8i introduced a PL/SQL Profiler that allows you to profile your PL/SQL code. For each executed line of PL/SQL code you can determine the execution time, and how many times it was executed.
To query the data in the database, you can use a SQL window to execute any SQL statement. All executed statements are kept in a history buffer, so you can easily re-execute them. Any query results are conveniently displayed in a separate grid that you can subsequently use to insert, update, or delete records. The result grid can additionally be used in a Query By Example mode, so that you can easily find the information you need. To query database objects you can use the Object Browser. All relevant properties of database objects such as tables, views, sequences, functions, procedures, packages, types and triggers can be viewed,
including any dependencies between the objects. The Object Browser uses a tree view similar to the explorer in Windows for easy point-and-click browsing.
Running SQL scripts
PL/SQL Developer includes a Command Window that can be used to run SQL scripts or execute SQL statements. You can additionally execute commands that are very similar to the SQL*Plus commands that you may be familiar to.
Creating and modifying table definitions
You can easily create and modify table definitions with using any SQL statement. Just fill in the definition in a dialog window to modify columns, constraints, indexes, privileges, storage information, comments, and so on. You can apply this definition in the database, and view, modify and save the
To visualize the database objects of your application or project and their relations, you can create diagrams. A diagram can be used for documentation purposes, but can also serve as a workspace to work with related objects.
PL/SQL Developer comes with a number of standard reports, which are HTML based. You can view these reports within PL/SQL Developer, print them, or save them as HTML files. You can also create your own custom reports. Reports can be made easily accessible from the reports menu.
The Graph Window can be launched from within a SQL Window or Report Window to obtain a graphical representation of the queried data.
To organize your work you can use PL/SQL Developer’s project concept. A project consists of a number of files and database objects. These objects are easily accessible through the Project Items Window, and can be compiled through a single mouse-click.
PL/SQL Developer provides several tools that can be helpful during development. These tools include a Find Database Object tool, allowing you to search for text in database object sources, a Compile Invalid Objects tool, to quickly compile objects that have become invalid during development, Export and Import tools, a Data Generator tool to create test data, an Export User Objects tool to export the DDL statements of a user’s objects, a Compare User Objects tool to compare the object definitions of 2 users, a Session information tool, an Event monitor, a Compare Table Data tool to compare and equalize table contents, and a DBMS Scheduler tool to view and manage your scheduler objects and jobs. In addition to these standard tools, you can define your own tools and include them in PL/SQL Developer’s tools menu.
Whats New in version 18.104.22.1684:
* Performance and stability improvements
* Export to XLSX did remove leading and trailing spaces from data
* Find and Replace would keep incrementing number on status line when nothing was replaced
* When using date/time selector in the SQL result grid, PL/SQL Developer would lose focus
* The file format encoding preference “Always as UTF8” did not work for the Program Window
* Crash recovery saved per InstanceName parameter value
* Shortcut key assigned to object functions could insert a character in editor
* SQL Window “totals” comment directive did not use “sum” as the default function
* Ctrl-C did not work in HTML viewers when “Use Internal HTML viewer” preference was not set
* Code Assistant Multi-Select now also available for a table alias in a select list
* Crash recovery could lead to a filename error when using a multi monitor setup
* Double-clicking on a packaged function or procedure could lead to a “Function not valid” error message
* Logging off a session did not update the transaction status in the Window List
* Creating new scheduler objects with timestamps could lead to “Could not convert variant” error message
* Command Window addition for batch locking/unlocking of report files:
> REPORT LOCKFORALL <path> <password> [R] > REPORT LOCKFOROTHERS <path> <password> [R] > REPORT UNLOCK <path> <password> [R] > REPORT LIST <path> [R] For the path you can use wildcards or directories.
Add the R parameter for recursive directory scans.
* Report Window option “Locked for other users” always asked for password
* PL/SQL Parser did not process double quoted identifiers with quotes and comments correctly
* Code Assistant did not work for aliases for table names in double quotes
* Right-click on a quoted “OWNER”.”OBJECT” would not show the object functions in the popup menu
* Object popup menu item “Browse” will now display the Object Browser if necessary
* Tnsnames.ora file with alias list on multiple lines was not parsed correctly
* Editor with long lines and highlight edit line enabled would not draw text correctly
* Using Alt-Down to get a list in the SQL Window result grid could insert a character
* Connection indicator added to the Object Browser for main connection
* Object Browser filter text now also works for user-defined query-based folders
* Viewing job classes on Oracle12c could lead to “ORA-01036: illegal variable name/number”
* PL/SQL Beautifier did not format FORALL/MERGE loop correctly
* Test Window now processes trailing slash of a PL/SQL Block
* Fixed visual scaling issues on high DPI screens on Windows 8 and Windows 10
* Comments are now preverved when using QBE in the SQL Window
* Undo/Redo did not work correctly after using QBE in the SQL Window
* Changing a Program Window connection would not always apply compiler preferences
* Test Manager window title would be cleared when logging off
* Compare Table Data tool now ignores virtual columns
* You can now use comma’s in substitution variable lists and checkboxes by specifying 2 comma’s:
&<name=”Exception?” checkbox=”raise_application_eror(-20000,, ‘[#]’), null”>
* Substitution variable checkbox unchecked value now defaults to empty when 1 value is given
* DDL for indexes on virtual columns would specify the column expression instead of the column name
* Fixed Session > Set Main Connection menu icon transparency issue
* Using the Search Next function in a macro not always work correctly