如何使用 jdbc 从 Oracle 结果集中获取架构名称？

Question

我想在 Oracle 数据库上运行查询，对于结果集中的每一列，我想知道该列来自的架构。我尝试了以下操作：

ResultSetMetaData rsMetadata = rs.getMetaData();
String schemaName = rsMetadata.getSchemaName(1)

但是，这会返回一个空字符串。有什么办法可以获取架构名称吗？

编辑回应 OMG Ponies：

我们正在开发的工具从数据库中获取数据并分析数据，以找到给定问题的信息最丰富的子集。然后，我们创建一个查询，该查询仅返回对给定问题提供信息的行。例如，如果我们有一个客户数据库，并且想知道哪些客户最有可能停止其服务，我们的工具可以创建一个查询，返回 5% 的客户记录，然后可以通过高性能分析算法运行该查询。好处是我们只对数据的子集进行分析，这当然会节省时间。事实证明，高性能分析算法现在效果更好，因为第一步本质上是从数据中过滤掉噪音。

因此，为了响应 OMG Ponies，用户指定数据库连接信息和查询作为我们工具的输入。因为他们可以指定他们喜欢的任何查询，所以用户可以连接到模式 foo，然后运行以下查询：

SELECT* FROM bar.customer;

如果由于某种原因眼睛颜色和性别是人们停止服务的预测因素，则生成的查询我们的系统生成的结果可能如下所示：

SELECT * FROM bar.customer WHERE bar.customer.eye_color='blue' 
                                 AND bar.customer.gender='M' 

最好知道结果集中每一列的架构，这样我们就可以确保我们的查询能够正确运行。我们可以假设该架构与数据库连接中使用的架构相同，并且 99% 的情况下都应该没问题。我只是担心 1% 的时间用户可能会做一些意想不到的事情，比如针对另一个模式运行查询。

Answer 1

根据旧的 Oracle 代码示例：

OracleResultSetMetaData 接口不实现 getSchemaName() 和 getTableName() 方法，因为底层协议无法实现此操作。

这对我来说意味着 ResultSetMetaData 也不会为 Oracle 提供这些方法，至少在使用 Oracle 驱动程序时是这样。 （我尝试使用 OCI 驱动程序看看这是否有影响，但显然没有）。

有一个 WebLogic 8 文档 建议这是可以做到的，但类型 4 驱动程序已在以后的版本中被弃用。因此，您仍然有可能找到支持 Oracle 的 getSchemaName() 的第三方驱动程序，但这似乎不太可能。

Answer 2

您可以通过一些自定义函数和查询来获取此信息。基本上，使用 DBMS_SQL 查找查询使用的列，然后将其与 v$sql_plan 中引用的表进行匹配。但这种方法可能存在很多问题；哪一列来自哪个表以及解释计划中的不同对象等之间可能存在歧义。

--#1: Create some test data
create table employee (id number primary key, name varchar2(100), department_id number);
create table department(id number primary key, name varchar2(100), test number);
insert into department select level, 'department test', level from dual connect by level <= 100;
insert into employee select level, 'employee test', level from dual connect by level <= 100;

--Actually run the query for this example so there will be data in the data dictionary.
select employee.* from employee inner join department on department_id = department.id;

--#2: The first difficult part is to find the sql_id of the query.  Can you get this directly from the
--    result set?  If not not you'll have to find it.
--    I'm not exactly sure how you'll want to do this, here are some options:
--Look at the last loaded query in v$sql (I don't think this always works, especially if the query has run multiple times)
select * from v$sql where v$sql.parsing_schema_name = user order by first_load_time desc;
--Compare the query text (sql_text removes newlines, sql_fulltext is a clob)
select * from v$sql where sql_text like 'select employee.* from employee inner join department on department_id = department.id%';
--Find the last sql_id for this session.  This doesn't work for me, maybe it's just an express edition bug?
select prev_sql_id, v$session.* from v$session where sid = sys_context('USERENV', 'SID');

--Look at the plan.  Note that there may be an index instead of a table.
--(On my system the sql_id is 0k2t2y1d312j8, but it will probably be different on yours)
select * from v$sql_plan where sql_id = '0k2t2y1d312j8';

--3: Create a type and a function to return all of the columns from a specific query.
--It'd be more consistent to use the SQL_ID here, but then there are permission issues if we
--have to get the text from v$sql.

create or replace type varchar2_tab is table of varchar2(30);
/

create or replace function get_columns(sql_text in varchar2) return varchar2_tab
authid current_user pipelined is
  my_cursor    number;
  column_count number;
  my_columns   DBMS_SQL.DESC_TAB;
begin
  select count(*) into column_count from v$sql;
  my_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(my_cursor, sql_text, dbms_sql.native); 
  dbms_sql.describe_columns(my_cursor, column_count, my_columns);
  for i in 1 .. my_columns.count loop
    pipe row(my_columns(i).col_name);
  end loop;
  dbms_sql.close_cursor(my_cursor);
end;
/

--Test queries.  Note that it's possible for a column to be listed twice.
select * from table(get_columns('select employee.* from employee inner join department on department_id = department.id'));

--4: Find the columns and their tables and schemas that are used in a query.
--Currently this only works for tables and indexes in the explain plan.
--There's probably a large number of items that won't work - materialized views, clusters(?), pipelined functiions, etc.
--You'll need to add each object type as necessary.
--(Remember to replace the SQL_ID and the query text with the real values)
select distinct owner, table_name, column_name
from
(
  --Find all the columns for the relevant tables
  select all_tab_cols.owner, all_tab_cols.table_name, all_tab_cols.column_name
  from
  (
    --Find the relevant tables from the plans (may need to find the table behind an index)
    select
      nvl(all_indexes.table_owner, plan_objects.object_owner) owner,
      nvl(all_indexes.table_name, plan_objects.object_name) table_name
    from
    (
      select object_owner, object_name, object_type
      from v$sql_plan
      where sql_id = '0k2t2y1d312j8'
        and
        (
          object_type = 'TABLE'
          or object_type like 'INDEX%'
        )
    ) plan_objects
    left outer join all_indexes
      on plan_objects.object_name = all_indexes.index_name
        and plan_objects.object_type like 'INDEX%'
  ) relevant_tables
  inner join all_tab_cols
    on relevant_tables.owner = all_tab_cols.owner
      and relevant_tables.table_name = all_tab_cols.table_name
) relevant_tab_cols
--It would be more 
inner join table(get_columns('select employee.* from employee inner join department on department_id = department.id')) all_possible_columns
  on relevant_tab_cols.column_name = all_possible_columns.column_value;