Frequently Asked Questions

 

PL/SQL Analyzer

I get the error “Could not reserve enough space for …KB object heap”. What’s wrong?

You are probably using a 32-bit java version and trying to configure more than 1GB of heap space, e.g. “-Xmx2048m”.

Define either “-Xmx1024m” or use a 64-bit java version.

I get the error “The system cannot find the path specified”. What’s wrong?

Check if you have defined the correct JAVA_HOME environment variable in the .cmd/.sh file.

On Windows the java.exe is located in the %JAVA_HOME%\bin directory.

What are the licensing terms?

The preview/trial version of PL/SQL Analyzer is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.

CC-BY-NC-ND

What are the limitations?

Maxim
If your SQL*Plus script runs successfully against an Oracle database but PL/SQL Cop or PL/SQL Analyzer reports an error, this is usually considered a bug. However, there are some known exceptions to this basic principle, which are documented below.

Block Terminator
Other block terminators than a dot (.) are not supported. This means the “SET BLOCKTERMINATOR” command is ignored.

Command Separator
Other command separators than semicolon (;) are not supported. This means that the “SET CMDSEP” command is ignored.

SQL Terminator
Other SQL terminators than semicolon (;) are not supported. This means that the “SET SQLTERMINATOR” command is ignored. Tailing whitespaces after a SQL terminator are not supported.

Line Continuation Character
Tailing whitespaces after a line continuation character (-) are not supported.

Slash Command
Tailing whitespaces after the slash command (/) are not supported.

Execute Command
The execute command must end on semicolon (;) if the last token is an expression.

Remark Command
The remark must not contain unterminated single or double quotes.

Prompt Command
The prompt must not contain unterminated single or double quotes.

Use of Keywords
The use of PL/SQL and SQL keywords as unquoted identifiers are generally not supported, due to the fact, that every single keyword needs to be treated as an exception. Oracle is quite gracious in that area and therefore we are eager to support more and more keywords as unquoted identifiers with every release, but the following keywords are causing conflicts in certain parts of the grammar and the use as literals should be therefore avoided: CROSS, CURSOR, END, FULL, FUNCTION, INNER, JOIN, LEFT, MODEL, OUTER, RIGHT, ROWTYPE, TYPE.

Quote Delimiter Characters
Oracle supports various quote literal characters except space, tab and return. The following example uses the [] quote character pair:

The following quote characters-pairs are supported: $$, ##, @@, ££, ||, (), {}, [], <>, !!, ++, ~~. All other quote characters lead to parse errors.

Conditional Compilation

Up until PL/SQL Cop version 1.0.16, PL/SQL Cop for SQL Developer 1.0.12, PL/SQL Analyzer 1.0.7 conditional compilation blocks have been fully analysed in the PL/SQL body, but were not supported in the PL/SQL DECLARE section.

Since it is possible to store non-PL/SQL code within conditional compilation blocks, e.g. generation templates as used in FTLDB or tePSQL, the full-fletched analysis support of directive IF statements has been dropped. The “$IF … $END” and the “$ERROR … $END” code blocks are still recognised as statements/expressions including conditions, but the rest of the code is just parsed as a series of tokens. As a side effect, metrics such as the number of statements might change.

The current PL/SQL parser supports conditional compilation within the DECLARE section as ITEM_LIST_1 or ITEM_LIST_2.

Error Logging Clause
The keyword “log” is supported as table name and table alias. As a side effect DELETE and INSERT statements with an error_logging_clause but without a where_clause and without table alias cannot be supported.

PL/SQL Source Text Wrapping
Since PL/SQL Cop and PL/SQL Analyzer do not include a PL/SQL unwrap utility, the use of wrapped PL/SQL code is not supported.

Supported Oracle Versions
The PL/SQL and SQL grammars from Oracle version 7.0 until version 12.2 are supported. The language is based on the following documentation:

  • Oracle SQL*Plus User’s Guide and Reference, 12c Release 2 (12.2), E50028-08, January 2017
  • Oracle SQL Language Reference, 12c Release 2 (12.2), E49448-12, January 2017
  • Oracle PL/SQL Language Reference, 12c Release 2 (12.2), E49633-15, January 2017

Grammar extensions made in newer versions are not yet covered.

What has changed in the latest version?

The changes in the recent versions are documented in the PL/SQL Analyzer Changelog.

PL/SQL Cop

How are values with dual meaning identified?

When you are using only “Y” and “N” values for a PL/SQL variable, PL/SQL Cop produces the following message: “Guideline 24 : Try to use boolean data type for values with dual meaning.”. But if you are using other values such as “Da” and “Net”, you do not get such a message. PL/SQL Cop tries to minimize the number of false positives, which results sometimes in false negatives. Consider a variable containing a value for sex. Valid values are “male” and “female”. Even if this is a variable with dual meaning you do not want to represent the sex variable as a boolean type. So PL/SQL Cop checks if exactly two of the following values are stored in a variable:

“TRUE”, “FALSE”, “T”, “F”, “0”, “1”, “2”, “YES”, “NO”“Y”, “N”, “JA”, “NEIN”, “J”, “SI”, “S”, “OUI”, “NON”, “O”“L_TRUE”, “L_FALSE”, “CO_TRUE”, “CO_FALSE”

If other values are detected the message “Guideline 24 : Try to use boolean data type for values with dual meaning.” will not be shown.

I get the error “Could not reserve enough space for …KB object heap”. What’s wrong?

You are probably using a 32-bit java version and trying to configure more than 1GB of heap space, e.g. “-Xmx2048m”.

Define either “-Xmx1024m” or use a 64-bit java version.

I get the error “The system cannot find the path specified”. What’s wrong?

Check if you have defined the correct JAVA_HOME environment variable in the .cmd/.sh file.

On Windows the java.exe is located in the %JAVA_HOME%\bin directory.

What are the licensing terms?

The preview/trial version of PL/SQL Cop is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.

CC-BY-NC-ND

What are the limitations?

Maxim
If your SQL*Plus script runs successfully against an Oracle database but PL/SQL Cop or PL/SQL Analyzer reports an error, this is usually considered a bug. However, there are some known exceptions to this basic principle, which are documented below.

Block Terminator
Other block terminators than a dot (.) are not supported. This means the “SET BLOCKTERMINATOR” command is ignored.

Command Separator
Other command separators than semicolon (;) are not supported. This means that the “SET CMDSEP” command is ignored.

SQL Terminator
Other SQL terminators than semicolon (;) are not supported. This means that the “SET SQLTERMINATOR” command is ignored. Tailing whitespaces after a SQL terminator are not supported.

Line Continuation Character
Tailing whitespaces after a line continuation character (-) are not supported.

Slash Command
Tailing whitespaces after the slash command (/) are not supported.

Execute Command
The execute command must end on semicolon (;) if the last token is an expression.

Remark Command
The remark must not contain unterminated single or double quotes.

Prompt Command
The prompt must not contain unterminated single or double quotes.

Use of Keywords
The use of PL/SQL and SQL keywords as unquoted identifiers are generally not supported, due to the fact, that every single keyword needs to be treated as an exception. Oracle is quite gracious in that area and therefore we are eager to support more and more keywords as unquoted identifiers with every release, but the following keywords are causing conflicts in certain parts of the grammar and the use as literals should be therefore avoided: CROSS, CURSOR, END, FULL, FUNCTION, INNER, JOIN, LEFT, MODEL, OUTER, RIGHT, ROWTYPE, TYPE.

Quote Delimiter Characters
Oracle supports various quote literal characters except space, tab and return. The following example uses the [] quote character pair:

The following quote characters-pairs are supported: $$, ##, @@, ££, ||, (), {}, [], <>, !!, ++, ~~. All other quote characters lead to parse errors.

Conditional Compilation

Up until PL/SQL Cop version 1.0.16, PL/SQL Cop for SQL Developer 1.0.12, PL/SQL Analyzer 1.0.7 conditional compilation blocks have been fully analysed in the PL/SQL body, but were not supported in the PL/SQL DECLARE section.

Since it is possible to store non-PL/SQL code within conditional compilation blocks, e.g. generation templates as used in FTLDB or tePSQL, the full-fletched analysis support of directive IF statements has been dropped. The “$IF … $END” and the “$ERROR … $END” code blocks are still recognised as statements/expressions including conditions, but the rest of the code is just parsed as a series of tokens. As a side effect, metrics such as the number of statements might change.

The current PL/SQL parser supports conditional compilation within the DECLARE section as ITEM_LIST_1 or ITEM_LIST_2.

Error Logging Clause
The keyword “log” is supported as table name and table alias. As a side effect DELETE and INSERT statements with an error_logging_clause but without a where_clause and without table alias cannot be supported.

PL/SQL Source Text Wrapping
Since PL/SQL Cop and PL/SQL Analyzer do not include a PL/SQL unwrap utility, the use of wrapped PL/SQL code is not supported.

Supported Oracle Versions
The PL/SQL and SQL grammars from Oracle version 7.0 until version 12.2 are supported. The language is based on the following documentation:

  • Oracle SQL*Plus User’s Guide and Reference, 12c Release 2 (12.2), E50028-08, January 2017
  • Oracle SQL Language Reference, 12c Release 2 (12.2), E49448-12, January 2017
  • Oracle PL/SQL Language Reference, 12c Release 2 (12.2), E49633-15, January 2017

Grammar extensions made in newer versions are not yet covered.

What has changed in the latest version?

The changes in the recent versions are documented in the PL/SQL Cop Changelog.

What is the difference between “PL/SQL Cop” and “PL/SQL Cop for SQL Developer”?

PL/SQL Cop is a standalone command line utility. It processes SQL*Plus scripts within a directory tree and produces an HTML report and an Excel workbook. It reports metrics on file and PL/SQL unit level as well as violations of the Trivadis PL/SQL & SQL Coding Guidelines Version 3.2.

PL/SQL Cop for SQL Developer is a cost-free extension to Oracle’s SQL Developer 4.x. It processes the content of an editor window and produces a HTML report which is similar to the one of PL/SQL Cop but reduced and adapted to the scope of a single editor window. Additionally the violations of theTrivadis PL/SQL & SQL Coding Guidelines Version 3.2 are presented in a dedicated tab pane linked to the editor window to support quick navigation to the corresponding code.

PL/SQL Cop for SQL Developer

How are values with dual meaning identified?

When you are using only “Y” and “N” values for a PL/SQL variable, PL/SQL Cop produces the following message: “Guideline 24 : Try to use boolean data type for values with dual meaning.”. But if you are using other values such as “Da” and “Net”, you do not get such a message. PL/SQL Cop tries to minimize the number of false positives, which results sometimes in false negatives. Consider a variable containing a value for sex. Valid values are “male” and “female”. Even if this is a variable with dual meaning you do not want to represent the sex variable as a boolean type. So PL/SQL Cop checks if exactly two of the following values are stored in a variable:

“TRUE”, “FALSE”, “T”, “F”, “0”, “1”, “2”, “YES”, “NO”“Y”, “N”, “JA”, “NEIN”, “J”, “SI”, “S”, “OUI”, “NON”, “O”“L_TRUE”, “L_FALSE”, “CO_TRUE”, “CO_FALSE”

If other values are detected the message “Guideline 24 : Try to use boolean data type for values with dual meaning.” will not be shown.

I cannot install any extension on Mac OS X

Maybe you’ve run into a SQL Developer bug on Mac OS X. See this blog post for problem details and solution.

I cannot install any extension on Windows

Have you installed SQL Developer in “C:\Program Files” or “C:\Program Files(x86)”? In this case the files are read-only for a standard Windows user.

To solve the problem start SQL Developer as Administrator or install SQL Developer in an user folder. While the former requires local administrator rights, the latter is always feasible.

I’ve got errors in the extensions log after installation. What’s wrong?

You are probably using an outdated version of SQL Developer. In this case you see after a restart an error message in the Extensions-Log similar to the following:

Extensions_Log

SQL Developer 1.x, 2.x and 3.x are based on the Extension Software Development Kit (ESDK) version 1. SQL Developer 4.x requires the use of ESDK version 2. Unfortunately these versions are not compatible. So we decided to support the newest SQL Developer version only.

Please update to SQL Developer 4.x since this is a prerequisite for this extension.

What are the licensing terms?

PL/SQL Cop for SQL Developer is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.

CC-BY-NC-ND

What are the limitations?

Maxim
If your SQL*Plus script runs successfully against an Oracle database but PL/SQL Cop or PL/SQL Analyzer reports an error, this is usually considered a bug. However, there are some known exceptions to this basic principle, which are documented below.

Block Terminator
Other block terminators than a dot (.) are not supported. This means the “SET BLOCKTERMINATOR” command is ignored.

Command Separator
Other command separators than semicolon (;) are not supported. This means that the “SET CMDSEP” command is ignored.

SQL Terminator
Other SQL terminators than semicolon (;) are not supported. This means that the “SET SQLTERMINATOR” command is ignored. Tailing whitespaces after a SQL terminator are not supported.

Line Continuation Character
Tailing whitespaces after a line continuation character (-) are not supported.

Slash Command
Tailing whitespaces after the slash command (/) are not supported.

Execute Command
The execute command must end on semicolon (;) if the last token is an expression.

Remark Command
The remark must not contain unterminated single or double quotes.

Prompt Command
The prompt must not contain unterminated single or double quotes.

Use of Keywords
The use of PL/SQL and SQL keywords as unquoted identifiers are generally not supported, due to the fact, that every single keyword needs to be treated as an exception. Oracle is quite gracious in that area and therefore we are eager to support more and more keywords as unquoted identifiers with every release, but the following keywords are causing conflicts in certain parts of the grammar and the use as literals should be therefore avoided: CROSS, CURSOR, END, FULL, FUNCTION, INNER, JOIN, LEFT, MODEL, OUTER, RIGHT, ROWTYPE, TYPE.

Quote Delimiter Characters
Oracle supports various quote literal characters except space, tab and return. The following example uses the [] quote character pair:

The following quote characters-pairs are supported: $$, ##, @@, ££, ||, (), {}, [], <>, !!, ++, ~~. All other quote characters lead to parse errors.

Conditional Compilation

Up until PL/SQL Cop version 1.0.16, PL/SQL Cop for SQL Developer 1.0.12, PL/SQL Analyzer 1.0.7 conditional compilation blocks have been fully analysed in the PL/SQL body, but were not supported in the PL/SQL DECLARE section.

Since it is possible to store non-PL/SQL code within conditional compilation blocks, e.g. generation templates as used in FTLDB or tePSQL, the full-fletched analysis support of directive IF statements has been dropped. The “$IF … $END” and the “$ERROR … $END” code blocks are still recognised as statements/expressions including conditions, but the rest of the code is just parsed as a series of tokens. As a side effect, metrics such as the number of statements might change.

The current PL/SQL parser supports conditional compilation within the DECLARE section as ITEM_LIST_1 or ITEM_LIST_2.

Error Logging Clause
The keyword “log” is supported as table name and table alias. As a side effect DELETE and INSERT statements with an error_logging_clause but without a where_clause and without table alias cannot be supported.

PL/SQL Source Text Wrapping
Since PL/SQL Cop and PL/SQL Analyzer do not include a PL/SQL unwrap utility, the use of wrapped PL/SQL code is not supported.

Supported Oracle Versions
The PL/SQL and SQL grammars from Oracle version 7.0 until version 12.2 are supported. The language is based on the following documentation:

  • Oracle SQL*Plus User’s Guide and Reference, 12c Release 2 (12.2), E50028-08, January 2017
  • Oracle SQL Language Reference, 12c Release 2 (12.2), E49448-12, January 2017
  • Oracle PL/SQL Language Reference, 12c Release 2 (12.2), E49633-15, January 2017

Grammar extensions made in newer versions are not yet covered.

What has changed in the latest version?

The changes in the recent versions are documented in the PL/SQL Cop for SQL Developer Changelog.

What is the difference between “PL/SQL Cop” and “PL/SQL Cop for SQL Developer”?

PL/SQL Cop is a standalone command line utility. It processes SQL*Plus scripts within a directory tree and produces an HTML report and an Excel workbook. It reports metrics on file and PL/SQL unit level as well as violations of the Trivadis PL/SQL & SQL Coding Guidelines Version 3.2.

PL/SQL Cop for SQL Developer is a cost-free extension to Oracle’s SQL Developer 4.x. It processes the content of an editor window and produces a HTML report which is similar to the one of PL/SQL Cop but reduced and adapted to the scope of a single editor window. Additionally the violations of theTrivadis PL/SQL & SQL Coding Guidelines Version 3.2 are presented in a dedicated tab pane linked to the editor window to support quick navigation to the corresponding code.

PL/SQL Unwrapper for SQL Developer

I cannot install any extension on Mac OS X

Maybe you’ve run into a SQL Developer bug on Mac OS X. See this blog post for problem details and solution.

I cannot install any extension on Windows

Have you installed SQL Developer in “C:\Program Files” or “C:\Program Files(x86)”? In this case the files are read-only for a standard Windows user.

To solve the problem start SQL Developer as Administrator or install SQL Developer in an user folder. While the former requires local administrator rights, the latter is always feasible.

I’ve got errors in the extensions log after installation. What’s wrong?

You are probably using an outdated version of SQL Developer. In this case you see after a restart an error message in the Extensions-Log similar to the following:

Extensions_Log

SQL Developer 1.x, 2.x and 3.x are based on the Extension Software Development Kit (ESDK) version 1. SQL Developer 4.x requires the use of ESDK version 2. Unfortunately these versions are not compatible. So we decided to support the newest SQL Developer version only.

Please update to SQL Developer 4.x since this is a prerequisite for this extension.

Is unwrapping code processed by the PL/SQL Wrapper of Oracle7, Oracle8, Oracle8i or Oracle9i supported?

No. Beginning with Oracle Database 10g, the algorithm of the PL/SQL Wrapper has changed. PL/SQL Unwrapper currently supports only the latest algorithm used in the PL/SQL Wrapper of Oracle Database 10g, 11g and 12c.

Unwrap does nothing. What’s wrong?

Please check the Logging Page in the Logging window. One of the following error messages is expected:

  • Could not unwrap this code. Most probably it was not wrapped with the Oracle 10g, 11g or 12c wrap utility.
  • SHA-1 hash values do not match. Expected ‘…’  but got ‘…’. Cannot unwrap code.

If you’ve got the latter message, the wrapped code has most probably been modified.

What are the licensing terms?

PL/SQL Unwrapper for SQL Developer is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.

CC-BY-NC-ND

What has changed in the latest version?

The changes in the recent versions are documented in the PL/SQL Unwrapper for SQL Developer Changelog.