Oracle 在包含注释的很长SQL文本字符串中仅提取 “可执行” 选择代码块

你好，

我在表中有一列类型为CLOB的列，其中用户可以复制粘贴他们的整个 “SQL查询文本” 字符串，以便过程稍后将 “处理” 此表中的条目，并使用DBMS_SQL解析出查询字符串，以查看其是否有效以及是否有效，它将继续并使用DBMS_SQL执行它。

作为程序验证检查的一部分，我想确保以下内容:-

1.跳过所有以 '/*' 开头并以 '*/' 结尾的注释代码块，它出现在整个SQL查询文本中的任何位置。

2.在整个SQL查询文本中的任何位置跳过以 '--' 开头的任何注释代码行。

3.跳过/忽略上面的1 & 2后，确保第一个 “可执行” 语句必须以单词 'SELECT' 开头。不接受任何其他单词，例如插入，删除，删除或更新或其他任何单词，并且该过程应向用户显示友好消息并引发异常。

注意: 请记住，用户还可以在注释代码块中包含单词 “SELECT” (在/* 和 */之间或作为-行的一部分)，因此我们确实需要找到一个 “可执行” 选择，而不是出现在任何地方的任何单词 “SELECT”。

4.一旦发现第一个 “可执行文件” 语句确实是 “SELECT” 单词，我将继续用包含附加编译器提示的自定义SELECT字符串替换它。示例: 将 'SELECT c1，c2替换为 'SELECT /* PARALLEL(n) NOLOGGING */ c1，c2...'

5.组装最终字符串后，继续使用DBMS_SQL.EXECUTE或立即执行查询 (不确定哪一个是合适的，为什么？)


该表将用于动态 “处理” 创建表 (仅使用cta)，视图和实体化视图，因为它们都需要SELECT查询来填充这些对象。

任何帮助将不胜感激。

提前谢谢!

像这样的事情应该会让你开始

SQL> create table t (c clob);

Table created.

SQL>
SQL> declare
  2    l_sql clob :=
  3  q'{ /* some initial stuff
  4      which we dont care about
  5      -- and comments in comments
  6   */
  7   select * -- all cols
  8   from   user_tables
  9   where  table_name like 'T%'
 10   -- and num_rows > 0
 11   /* omitting these ones
 12   and nested = 'Y'
 13   and user_stats = 'Y'
 14   */ order by 1}';
 15  begin
 16    insert into t values (l_sql );
 17    commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

C
---------------------------------------------------------------------------------------------------
--------
 /* some initial stuff
    which we dont care about
    -- and comments in comments
 */
 select * -- all cols
 from   user_tables
 where  table_name like 'T%'
 -- and num_rows > 0
 /* omitting these ones
 and nested = 'Y'
 and user_stats = 'Y'
 */ order by 1


1 row selected.

SQL>
SQL> set serverout on
SQL> declare
  2    l_text clob;
  3    l_clean clob;
  4    l_in_comment   boolean := false;
  5    l_in_comment_mode varchar2(10);
  6    pos pls_integer;
  7  begin
  8    for i in ( select * from t )
  9    loop
 10      l_text  := ltrim(i.c);
 11      l_clean := null;
 12      pos := 1;
 13      loop
 14        if substr(l_text,pos,2) = '--'
 15        then
 16          if l_in_comment then
 17            null;
 18          else
 19            l_in_comment := true;
 20            l_in_comment_mode := 'dashes';
 21          end if;
 22          pos := pos + 2;
 23        elsif substr(l_text,pos,2) = '/*'
 24        then
 25          if l_in_comment then
 26            null;
 27          else
 28            l_in_comment := true;
 29            l_in_comment_mode := 'slashes';
 30          end if;
 31          pos := pos + 2;
 32        elsif substr(l_text,pos,1) = chr(10)
 33        then
 34          if l_in_comment and l_in_comment_mode = 'dashes' then
 35            l_in_comment := false;
 36          else
 37            l_clean := l_clean || substr(l_text,pos,1);
 38          end if;
 39          pos := pos + 1;
 40        elsif substr(l_text,pos,2) = '*/'
 41        then
 42          if l_in_comment and l_in_comment_mode = 'slashes' then
 43            l_in_comment := false;
 44          else
 45            l_clean := l_clean || substr(l_text,pos,2);
 46          end if;
 47          pos := pos + 2;
 48        else
 49          if not l_in_comment then
 50            l_clean := l_clean || substr(l_text,pos,1);
 51          end if;
 52          pos := pos + 1;
 53        end if;
 54        exit when pos > length(l_text);
 55      end loop;
 56    end loop;
 57    dbms_output.put_line(l_clean);
 58  end;
 59  /




 select *  from   user_tables
 where  table_name like 'T%'



 order by 1

PL/SQL procedure successfully completed.

但是我必须强调，* 任何 * 从源中获取SQL文本然后可能运行它的东西都需要真正严格地控制，因为风险在安全性方面是巨大的。

无论如何，一旦你有一个清理的sql语句，可以做这样的事情来检查它的有效性:

SQL> declare
  2    l_sql clob := 'select * from tab';
  3    l_cursor number := dbms_sql.open_cursor;
  4  begin
  5    if substr(ltrim(upper(l_sql),' '||chr(13)||chr(10)),1,7) = 'SELECT ' then
  6      dbms_sql.parse( l_cursor,l_sql, dbms_sql.native );
  7    else
  8      raise_application_error(-20000,'SELECT not the leading keyword');
  9    end if;
 10  exception
 11    when others then
 12      dbms_sql.close_cursor( l_cursor );
 13      raise;
 14  end;
 15  /

PL/SQL procedure successfully completed.

如果你期待很多SQL，那么你可能不想冒烟你的共享池，所以你可能会这样:

SQL> declare
  2    l_sql clob := 'select * from tab';
  3    l_cursor number := dbms_sql.open_cursor;
  4  begin
  5    if substr(ltrim(upper(l_sql),' '||chr(13)||chr(10)),1,7) = 'SELECT ' then
  6      execute immediate 'alter session set cursor_sharing=force';
  7      dbms_sql.parse( l_cursor, l_sql, dbms_sql.native );
  8      execute immediate 'alter session set cursor_sharing=exact';
  9    else
 10      raise_application_error(-20000,'SELECT not the leading keyword');
 11    end if;
 12  exception
 13    when others then
 14      execute immediate 'alter session set cursor_sharing=exact';
 15      dbms_sql.close_cursor( l_cursor );
 16      raise;
 17  end;
 18  /

PL/SQL procedure successfully completed.