PL/SQL Cop Meets oddgen

存储架构 2016-04-27

Until August 2015 it never occurred to me that one could use non-PL/SQL code within conditional compilation blocks. Back than we discussed various template engine options as foundation for oddgen – the Oracle community’s dictionary-driven code generator.

oddgen supports nowadays the in-database template engines FTLDB and tePLSQL . Both tools may access templates stored in PL/SQL packages using a selection directive. Here’s the package body of a generator using FTLDB:


  sys.dbms_output.put_line('Hello ${object_type} ${object_name}!');
--%end generate_ftl
  FUNCTION generate(in_object_typeIN VARCHAR2,
                    in_object_nameIN VARCHAR2) RETURN CLOBIS
      l_args := NEW varchar2_nt(in_object_type, in_object_name);
      l_result := ftldb_api.process_to_clob(in_templ_name => $$PLSQL_UNIT || '%generate_ftl',
                                            in_templ_args => l_args);
      RETURN l_result;
  END generate;
END ftldb_hello_world;

The template is stored within the lines 4 to 11. It’s easy to see that the target code is PL/SQL, but the template itself contains various parts which do not comply with the PL/SQL language. The $IF on line 3 ensures that the template is compiled only when the condition is met. Never, in this case. You may be surprised, but yes, this trick really works.

However, if I check this code with PL/SQL Cop for SQL Developer 1.0.12 I get the following result:

Bad. This version of PL/SQL Cop cannot parse this code successfully, since it expects valid PL/SQL code within the conditional compilation blocks. While it has some advantages to include conditional PL/SQL code in a code analysis, it is simply worthless if the code cannot be parsed at all.

Therefore I released today new versions of all PL/SQL parser based products supporting non-PL/SQL code within conditional compilation blocks. And the result in PL/SQL Cop for SQL Developer 1.0.13 is:

Good. You see, this version parses such code without problems. There are still somelimitationsregarding the support of conditional compilation in DECLARE sections, but I’m glad that the parser is becoming more and more complete.

So it is time to updatePL/SQL Analyzer,PL/SQL Cop and PL/SQL Cop for SQL Developer .

Thanks oddgen for driving this improvement.

责编内容by:Philipp Salvisberg's Blog (源链)。感谢您的支持!


More Than one Way to Load a Moose So from my last post I had to figure out a way to enforce these four rules ...
Roles Required to Run the Stored Procedure in SQL ... I need to connect to SQL Server 2008 R2 database from a C# application. I need t...
基于裸数据的异地数据库性能诊断与优化... 影响数据库性能的因素有很多,从大的方面可以分为硬件和软件。硬件包括CPU、内存、存储、网络设备等,软件方面包括操作系统版本、操作系统参数、数据库版本、数据库参数...
SQL Statement in Oracle 11g I have a table in the below format: ID code status ----- ----- -----...
SQL ‘Where’ With linq to sql, where are database connection informat...