如何在不执行整个查询的情况下查找动态查询中使用的列 [英] How to find the column used in the dynamic query without executing whole query

问题描述

问题陈述

我有一个动态SQL，需要将其存储在表中，但在此之前 存储SQL，我需要使用列列表来验证SQL 存储在另一个表中. 如果不执行查询，是否有可能在select中找到列名?

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 ?

方法1 我能想到的唯一选择是，尝试使用查询的解释计划并读取数据字典表中的元数据.但是很遗憾，我无法找到包含此类数据的任何表.如果您知道此类视图，请告诉我?

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?

方法2 使用DBMS_SQL.DESCRIBE_COLUMNS包查找列名，但我相信这将执行整个查询.

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

推荐答案

您无需执行查询即可获取列名，只需解析它即可.例如举一个简单的例子:

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;
/

输出:

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.

这篇关于如何在不执行整个查询的情况下查找动态查询中使用的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！