Formatting is one of the software development features that always steers controversy. To put it bluntly, everybody wants to format the code their own way. In previous SQL Dev versions the answer to that challenge was having elaborate tree-table widget spiced with several dozens of various options.

Arguably, the combined SQL and PL/SQL grammar is the most cumbersome programming language in existence, with total number of grammar productions exceeding 20000. From that perspective, the list of “Advanced Format” options offered by SQL Dev feels inadequate. To address this mismatch, the latest release introduced “Custom Format”. It offers to a developer means to specify formal conditions which SQL and PL/SQL grammatical constructs to indent,  align, pad, add extra line breaks and so on.

As usual, the best way to demonstrate a new feature is an example. Consider the following enhancement request by Stefan Poschenrieder to remove the indent of the NOT NULL ENABLE inline constraint in the following code

CREATE TABLE dim_aspect (
    dim_aspectid       NUMBER(11,0)
    source             VARCHAR2(50),
    hier1_desc         VARCHAR2(50),
    hierarchy1         VARCHAR2(50),
    dwh_inserteddate   DATE

The first step is identifying the target grammar symbol in the Code Outline panel. Here is screen snapshot illustrating it:
code outline.png
As illustrated there, positioning a cursor over the NOT keyword highlights the corresponding node in the parse tree. Therefore, the grammar symbol of interest is probably “inline_constraint“. Clicking onto the node labeled inline_constraint to double check this guess witnesses it highlighting “NOT NULL ENABLE” in the code editor. (Please be aware that clicking onto a hyperlinked looking grammar symbol has side effect of opening documentation page featuring that symbol railroad diagram definition).

As soon as the grammar symbol of interest is known, lets find it at Custom Format preferences panel. Admittedly, glaring omission there is the search widget. Yet, grammar symbols are arranged in lexicographic order, so that a reader would have no trouble spotting inline_constraint in the following formatting rule:

| [node) index_subpartition_clause[69,119)# 
| [node) inline_constraint & ![node) inline_constraint[14,67)
| [node) inline_ref_constraint

This fragment of rather convoluted disjunctive condition instructs the formatter to indent the nodes with payload “index_subpartition_clause[69,119)#“, or the nodes labeled with “inline_constraint” (but not those that also labeled as “inline_constraint[14,67)“), or the nodes labeled “inline_ref_constraint“.  Therefore, commenting out the middle conjunction

| [node) index_subpartition_clause[69,119)# 
--| [node) inline_constraint & ![node) inline_constraint[14,67)
| [node) inline_ref_constraint

is likely a solution to the problem. After making this change, a reader is advised to test what effect do modified formatting rules have on a SQL or PL/SQL code sample in the preview editor. This action is performed with the bottom-left “Run” button. The two neighbors “Import…” and “Export…” are for saving and retrieving your work. By default the modified “format.arbori” file is kept in the SQLDev product preferences directory.

Let’s reinforce this custom formatting idea with two more examples, both from Steven Feuerstein. In both cases we’ll learn some additional syntax of that formal formatting rules specification. The first example is just a bug witnessed by the following test case:

   FOR indx IN 1..100000 LOOP
         indx       --<--- ???
      ) := 'String ' || indx;

The rule responsible for that unwanted indentation of the “indx” parameter is

| [node) pls_expr & [node-1) '(' & ![node) numeric_literal

It requires the parse node to be labeled with “pls_expr” but not “numeric_literal“. Also, it stipulates that the prior sibling node (formally, “node-1“) is labeled with open parenthesis. Adding the condition for the node successor not to be the closing parenthesis

| [node) pls_expr & [node-1) '(' & ![node) numeric_literal & ![node+1) ')' 

is all it takes to fix this snag.

In the second example

    PROCEDURE create_checklist (
        user_id_in       IN INTEGER,
        question_id_in   IN INTEGER
    --<-- wanted extra line break 
    PROCEDURE remove_checklist (
        checklist_id_in   IN INTEGER

    PROCEDURE remove_checklist (
        user_id_in       IN INTEGER,
        question_id_in   IN INTEGER

END fmt;

the formatted code have only single line breaks after each package member. The formatting rule responsible for this functionality is “extraLines“. It formally a disjunction of the two rules “sql_stmts” and “significant_statements“. In the later rule we see the following conjunctive condition:

| [node) basic_decl_item & [node+20 < node)

The syntax of first part is familiar: it requires the node to be labeled as “basic_decl_item“.

The second part is little more cumbersome. The “[ node” and “node )” refer to the node beginning and ending position, correspondingly. Each node in the parse tree recognizes a sequence of tokens. In our example nodes labeled as “basic_decl_item” recognizes a sequence of tokens beginning with the PROCEDURE keyword and ending with semicolon. The beginning position is the offset of the PROCEDURE keyword, while the ending position is the offset of the semicolon. The condition  “[node+20 < node)” requires more than 20 tokens between the node beginning and ending position, or informally the package member to be of “sufficient length”.

Please note that package data members are recognized as “basic_decl_item” as well. Therefore, the proper fix depends on if a user wants to inset double line breaks after data members or not. The length condition was a naive attempt on distinguishing data and procedures, but more elaborate condition, distinguishing data and procedures is possible too.

An adventurous reader is encouraged to experiment with the formatting rules in the Arbori query panel that I have described earlier. For example, after copying-and-pasting the entire isolatedNodes rule into Arbori panel, what nodes does it query at the parse tree of the above CREATE TABLE example?

Reshaping Parse Trees

September 22, 2016


Parsing and internal parse structures — trees — are not something that development tools readily expose. It is assumed that most users are comfortable with numerous features that depend on parsing, such as formatting, code block collapsing/expanding, code advisors and so on, but aren’t really interested learning the formal code structure. In SQL Developer 4.2 we have attempted to encourage users to experiment with parse trees.

The location, where the new feature is introduced, is somewhat obscure — it is the former 4.2 “Outline” panel, which is was accessible via “Quick Outline” context menu item. Minor name change reflects the amended functionality


Perhaps some explanation of what you see at the left panel is warranted. Oracle PL/SQL language is ADA derivative, which formal grammar can be found here. In the above example, the top most node labeled subprg_spec has been recognized as matching the first production for the rule

subprg_spec	: PROCEDURE_ IDENTIFIER .fml_part.
		| FUNCTION_  designator .fml_part. RETURN_ ty_mk

Consequently, it has 3 children labeled PROCEDURE, TEST (which is identifier) and , fml_part.

For some obscure reason, there is no trace of this genuine formal grammar in oracle PL/SQL documentation, and “railroad” syntax diagrams are different. The good news, however, is that oracle documentation SQL grammar matches almost verbatim to the parse trees that SQL Developer exposes.

Playing with parse trees

Selecting a node on the tree highlights the recognized portion of the code in the editorreshapingparsetrees2

Some nodes, such as those labeled with fml_part in the example above, are collapsed. This is because parse trees for even moderately long code become overwhelming. Expanding tree branch manually is quite laborious; this is why there are 2 more effective ways to accomplish it.

The first alternative is to click “expand branch” context menu item

Even though the root node is not collapsed, this context menu item is still there, and this action will expand all the collapsed descendant branches.

Selectively collapsing/expanding tree branches

The second alternative is little more sophisticated. Suppose, in our working example you would like to have all the nodes with payload fml_part expanded. Then, you click onto the second button on the toolbar (it is marked with plus symbol and “Collapse nodes with payload” tooltip).


If you check the fml_part menu item, then all the nodes with this payload become expanded.

A sharp eyed reader might have noticed that, by default, select is expanded, while seq_of_stmts is collapsed. This is because code outline is working not only for PL/SQL editor but also SQL Worksheet.  In SQL worksheet queries — select statements — are the main laborers, this is why their structure is expanded. However, if you work mostly in PL/SQL editor, then those select statements are less interesting. For example, a select query which is a part of a cursor declaration can be collapsed, unless a user decides to investigate it. As for seq_of_stmts, again, PL/SQL package bodies are frequently very large, so having collapsed sequence of statements — the bulk of procedure or function — is handy.

Selectively hiding the nodes

Another way to bring a parse tree to manageable size is hiding the nodes. In general, if node disappears, something must be done about this node children. Naturally, orphans become attached to node’s parent. For example, deleting node B, reattaches grand children C and D to node A.


Which nodes are not so much interesting? Consider a typical SQL query


A chain of tree nodes marked with select_list faithfully reflects SQL grammar, but a user might want to see more concise picture.  The 3rd (“Hide nodes with payload”) button on toolbar serves this purpose


Unchecking the select_list menu item (it is unchecked by default) reshapes the tree like this



There is only so much that tree reshaping can do. For large trees it is laborious to search for interesting information. In the next installment we’ll describe an approach which is more satisfying for a reader with database background — querying.

People invent really complicated ways to achieve simple tasks such as copying a table from one database to another. With database link (which is arguably RDBMS’s the most under appreciated feature) it is just one SQL command:

create table emp as select * from scott.emp@dblink1;

But who has time to create database links? In SQLDeveloper 4.0 you don’t have to. Assuming that you have the right connection the script

set autodblink on
create table emp as select * from scott.emp@connection1; -- assuming there is connection &quot;connection1&quot;

would create temporary db link, then run the statement and, finally, drop the link.

Another example, which benefits from this functionality is comparing a table in two different databases:

set autodblink on
(select * from employees@connection1
select * from employees)
(select * from employees
select * from employees@connection1);

In releases prior to SQLDeveloper 4.1, however, there was a  major omission — a failure to run individual query which outputs result set into a grid (as opposed to running it as a script). Admittedly, there was a technical culprit for that. When a query is executed into a grid, the contract between the server (RDBMS) and client (SQLDeveloper) is somewhat loosely defined. Specifically, the client asks the server to execute the query, then fetches 50 records. The cursor remains open “just in case” if user might want to scroll down beyond the first batch of records. In other words, the end of sql execution is not precisely demarcated. How does this affect SQLDeveloper automatic DB link feature? Well, SQLDeveloper needs to know when to delete temporary database links, and the only solution is attaching listener to the event of grid closure. Hence, after you you set dblinks on, and execute a query into a grid, but before closing the grid, you’ll witness database link under the links node in the navigator. Please be aware that deleting temporary links from the client is not bulletproof; for example, server process going down with ORA-600 and taking user session with it, would result in leftover database links.  If your database development work revolves around environment with strict security requirements, it is advisable to reevaluate using this feature.

In releases prior to SQLDeveloper 4.1 there were also smaller snags. For example, PL/SQL anonymous blocks execution was not accompanied by database link creation.  Also, database link names with identifiers separated by dots were not recognized. Regarding the second issue, please keep in mind that SQLDeveloper’s syntax for connection names is less restricted than that of database links. For example, something like is legitimate connection alias, but is not a database link.

With these bug fixes behind, let’s venture into applications. How about copying [remote] database schema?  In introductory computer science class students learn that there are two kinds of objects in programming: data and code. Here is the command which copies the data:

set autodblink on;

   FOR rec IN (SELECT TABLE_NAME FROM user_tables@gbr30060_DB11GR24_hr) LOOP
         DBMS_OUTPUT.PUT_LINE('Processing Table: ' || rec.TABLE_NAME );
         execute immediate 'create table ' || rec.TABLE_NAME
                  || ' as select * from ' || rec.TABLE_NAME || '@gbr30060_DB11GR24_hr';
      exception when others then
         DBMS_OUTPUT.PUT_LINE('Skipped Table: ' || rec.TABLE_NAME ||' -&amp;amp;gt; -ERROR- '||SQLERRM);

and the code

   FOR rec IN (SELECT name, type, LISTAGG(text, 'chr(13)') WITHIN GROUP (ORDER BY line) AS code
               FROM   user_source@gbr30060_DB11GR24_hr
               GROUP BY name, type ) LOOP
         DBMS_OUTPUT.PUT_LINE('Processing Object: ' || rec.NAME );
         execute immediate 'create ' || rec.code;
      exception when others then
         DBMS_OUTPUT.PUT_LINE('Skipped : ' || rec.type || '' || ||' --ERROR-- '||SQLERRM);



Documentation Code Snippets

October 11, 2011

Code templates/snippets is one of modern IDE facilities accelerating programming. Some time ago I used to complain about cumbersome main() function signature in Java which escaped my limited memorization abilities until learning that there is convenient code template offered by code assist, so the syntax doesn’t matter. Sometimes the answer is just ctrl-space away!

SQL Developer 3.1 preview release is out and offers improvements in snippet/template arena. First, the old user-programmed templates and snippets are demoted from code assist/insight. You still can invoke them traditional way. The SQL&PL/SQL fragments shown in code insight are fetched from documentation. That’s right, Oracle documentation contains hundreds if not thousands of code snippets, therefore why insist on entering them manually?

Let’s go through examples. Starting anonymous PL/SQL block prompts code insight like this:

The first entry here is an individual keyword suggestion. The three next listings are code fragments captured from SQL history. Then there is "..." prompting that the list can be expanded, followed by the three entries from documentation. Selecting the first one outputs:

Code insight is syntax driven; the code snippet is suggested only in the appropriate context. For example, a standalone "exc" prefix won’t bring any exception snippets, while within our anonymous PL/SQL block it would:

If you are wondering where particular snippet is coming from, you can press the button with library icon. It will open new browser window pointing to the relevant documentation page:

This feature may compete with Google for the fastest way to a forgotten corner of oracle documentation. Try it: REGEXP signature, recursive WITH, every other clause from humongous SQL syntax is right there at you keystroke.