MaximIf 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 CommandThe 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.

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.

Example of a supported directive IF in the DECLARE section

1

2

3

4

5

6

7

8

9

10

CREATEORREPLACEPACKAGEmy_pkgAS

$IFDBMS_DB_VERSION.VERSION<10$THEN

SUBTYPEmy_realISNUMBER;

$ELSE

SUBTYPEmy_realISBINARY_DOUBLE;

$END

my_pimy_real;

my_emy_real;

ENDmy_pkg;

/

Example of an unsupported directive IF in the DECLARE section

1

2

3

4

5

6

7

8

9

10

11

CREATEORREPLACEPACKAGEmy_pkgAS

SUBTYPEmy_realIS

$IFDBMS_DB_VERSION.VERSION<10$THEN

NUMBER;

$ELSE

BINARY_DOUBLE;

$END

my_pimy_real;

my_emy_real;

ENDmy_pkg;

/

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.

Example of supported INSERT statement with an error_logging_clause

1

2

3

4

INSERTINTOdeptsal(dept_no,dept_name,salary)

SELECTdept_no,dept_name,salary

FROMsource_syns

LOGERRORSINTOdeptsal_errREJECTLIMIT10;

Example of unsupported INSERT statement with an error_logging_clause (source_syn has no alias)

1

2

3

4

INSERTINTOdeptsal(dept_no,dept_name,salary)

SELECTdept_no,dept_name,salary

FROMsource_syn

LOGERRORSINTOdeptsal_errREJECTLIMIT10;

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:

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:

MaximIf 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 CommandThe 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.

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.

Example of a supported directive IF in the DECLARE section

1

2

3

4

5

6

7

8

9

10

CREATEORREPLACEPACKAGEmy_pkgAS

$IFDBMS_DB_VERSION.VERSION<10$THEN

SUBTYPEmy_realISNUMBER;

$ELSE

SUBTYPEmy_realISBINARY_DOUBLE;

$END

my_pimy_real;

my_emy_real;

ENDmy_pkg;

/

Example of an unsupported directive IF in the DECLARE section

1

2

3

4

5

6

7

8

9

10

11

CREATEORREPLACEPACKAGEmy_pkgAS

SUBTYPEmy_realIS

$IFDBMS_DB_VERSION.VERSION<10$THEN

NUMBER;

$ELSE

BINARY_DOUBLE;

$END

my_pimy_real;

my_emy_real;

ENDmy_pkg;

/

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.

Example of supported INSERT statement with an error_logging_clause

1

2

3

4

INSERTINTOdeptsal(dept_no,dept_name,salary)

SELECTdept_no,dept_name,salary

FROMsource_syns

LOGERRORSINTOdeptsal_errREJECTLIMIT10;

Example of unsupported INSERT statement with an error_logging_clause (source_syn has no alias)

1

2

3

4

INSERTINTOdeptsal(dept_no,dept_name,salary)

SELECTdept_no,dept_name,salary

FROMsource_syn

LOGERRORSINTOdeptsal_errREJECTLIMIT10;

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:

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.3.

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 the Trivadis PL/SQL & SQL Coding Guidelines Version 3.3 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:

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:

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.

MaximIf 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 CommandThe 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.

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.

Example of a supported directive IF in the DECLARE section

1

2

3

4

5

6

7

8

9

10

CREATEORREPLACEPACKAGEmy_pkgAS

$IFDBMS_DB_VERSION.VERSION<10$THEN

SUBTYPEmy_realISNUMBER;

$ELSE

SUBTYPEmy_realISBINARY_DOUBLE;

$END

my_pimy_real;

my_emy_real;

ENDmy_pkg;

/

Example of an unsupported directive IF in the DECLARE section

1

2

3

4

5

6

7

8

9

10

11

CREATEORREPLACEPACKAGEmy_pkgAS

SUBTYPEmy_realIS

$IFDBMS_DB_VERSION.VERSION<10$THEN

NUMBER;

$ELSE

BINARY_DOUBLE;

$END

my_pimy_real;

my_emy_real;

ENDmy_pkg;

/

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.

Example of supported INSERT statement with an error_logging_clause

1

2

3

4

INSERTINTOdeptsal(dept_no,dept_name,salary)

SELECTdept_no,dept_name,salary

FROMsource_syns

LOGERRORSINTOdeptsal_errREJECTLIMIT10;

Example of unsupported INSERT statement with an error_logging_clause (source_syn has no alias)

1

2

3

4

INSERTINTOdeptsal(dept_no,dept_name,salary)

SELECTdept_no,dept_name,salary

FROMsource_syn

LOGERRORSINTOdeptsal_errREJECTLIMIT10;

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:

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.3.

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 the Trivadis PL/SQL & SQL Coding Guidelines Version 3.3 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.

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:

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.