How to find the column used in the dynamic query w

问题:

Problem Statement

I have a dynamic SQL which i need to store in a table ,but before storing the sql i need to validate the sql with the list of columns stored in another table. Without executing the query , is it possible to find name of columns in the select ?

Approach1 Only option i can think of is ,try to use explain plan of the query and read the meta data in the data dictionaries table .But unfortunately i am not able to find any table with such data.Please let me know if you know such views?

Approach2 Use DBMS_SQL.DESCRIBE_COLUMNS package to find the column name ,but i believe this will execute the whole query.

回答1:

You don't need to execute the query to get the column names, you just need to parse it; e.g. as a simple example:

set serveroutput on

declare
  l_statement varchar2(4000) := 'select * from employees';
  l_c pls_integer;
  l_col_cnt pls_integer;
  l_desc_t dbms_sql.desc_tab;
begin
  l_c := dbms_sql.open_cursor;
  dbms_sql.parse(c=>l_c, statement=>l_statement, language_flag=>dbms_sql.native);
  dbms_sql.describe_columns(c=>l_c, col_cnt=>l_col_cnt, desc_t=>l_desc_t);

  for i in 1..l_col_cnt loop
    dbms_output.put_line(l_desc_t(i).col_name);
  end loop;

  dbms_sql.close_cursor(l_c);
exception
  when others then
    if (dbms_sql.is_open(l_c)) then
      dbms_sql.close_cursor(l_c);
    end if;
    raise;
end;
/

which outputs:

EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID

PL/SQL procedure successfully completed.

You can do whatever validation you need on the column names inside the loop.

Bear in mind that you'll only see (and validate) the column names or aliases for column expressions, which won't necessarily reflect the data that is actually being retrieved. Someone could craft a query that pulls any data from anywhere it has permission to access, but then gives the columns/expression aliases that are considered valid.

If you're trying to restrict access to specific data then look into other mechanisms like views, virtual private database, etc.

回答2:

DBMS_SQL.PARSE will not execute a SELECT statement but it will execute a DDL statement. If the string 'select * from employees' is replaced by 'drop table employees' the code will fail but the table will still get dropped.

If you're only worried about the performance of retrieving the metadata then Alex Poole's answer will work fine.

If you're worried about running the wrong statement types then you'll want to make some adjustments to Alex Poole's answer.

It is surprisingly difficult to tell if a statement is a SELECT instead of something else. A simple condition checking that the string begins with select will work 99% of the time but getting from 99% to 100% is a huge amount of work. Simple regular expressions cannot keep up with all the different keywords, comments, alternative quoting format, spaces, etc.

/*comment in front -- */ select * from dual
    select * from dual
with asdf as (select * from dual) select * from asdf;
((((((select * from dual))))));

If you need 100% accuracy I recommend you use my open source PLSQL_LEXER. Once installed you can reliably test the command types like this:

select
    statement_classifier.get_command_name('  /*comment*/ ((select * from dual))') test1,
    statement_classifier.get_command_name('alter table asdf move compress') test2
from dual;

TEST1    TEST2
-----    -----
SELECT   ALTER TABLE