Sign up ×

I want to run a query on an Oracle database and for each column in the result set, I want to know the schema that the column came from. I tried the following:

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

However, this returns an empty string. Is there any work around to get the schema name?

Edit in response to OMG Ponies:

The tool we are developing takes data from a database and analyzes the data to find the most informative subset for a given question. We then create a query which returns only the rows that are informative for the given question. For instance if we had a customer database and wanted to know which customers are most likely to discontinue their service, our tool can create a query which returns 5% of the customer records which can then be run through high powered analytics algorithms. The benefit is that we run our analysis on only a subset of the data which of course will save time. It turns out as well that the high powered analytics algorithms work better now because the first step was essentially filtering the noise out of our data.

So in response to OMG Ponies, the user specifies the database connection information and query as inputs to our tool. Because they can specify any query they like, it would be possible for a user to connect to connect to schema foo, and then run the following query:

SELECT* FROM bar.customer;

If for some reason eye color and gender were predictors of people discontinuing their service, the resulting query that our system generates might look like this:

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

It would be nice to know the schema for each column in the results set so we can make sure our query will run correctly. We could assume the schema is the same as the schema used in the database connection and that should be fine 99% of the time. I'm just concerned for that 1% of the time a user might do something unexpected like run a query against another schema.

share|improve this question
Just need to look at the table(s) to get the schema, but that'll give you grief if you are dealing with a view... It begs the question - why don't you have access to the schema? –  OMG Ponies Nov 2 '10 at 20:58
Good question! The short answer is that the user can specify any query they like which means they could in theory refer to any schema. I edited the question with the more complete answer. –  Jay Askren Nov 3 '10 at 18:47
Can't the query manipulator verify that the schemas and tables are valid? It sounds like you're executing the modified query and passing the results back, so you already know it works. If you're actually passing the modified query back to be executed later, and really want the information, can you extract and pass back a list of the schemas/tables involved as well? I'm still not clear why it would be a problem, unless you're modifying and executing the query while connected as different users. –  Alex Poole Nov 9 '10 at 19:31

2 Answers 2

up vote 2 down vote accepted

According to an old Oracle code sample:

OracleResultSetMetaData interface does not implement the getSchemaName() and getTableName() methods because underlying protocol does not make this feasible.

That implies to me that ResultSetMetaData will not have those methods for an Oracle either, at least when using an Oracle driver. (I tried with the OCI driver to see if that made a difference, but apparently not).

There's a WebLogic 8 document that suggests it could be done, but that type 4 driver has been deprecated in later releases. So it's possible you may still be able to find a third-party driver that supports getSchemaName() against Oracle, but it seems unlikely.

share|improve this answer

You may be able to get this information with some custom functions and queries. Basically, find the columns used by a query with DBMS_SQL and then match that to the tables referenced in v$sql_plan. There are potentially a large number of issues with this approach though; possible ambiguities between which column came from which table, and different objects in the explain plan, etc.

--#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 =;

--#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 =';
--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;
  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;

--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 ='));

--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
  --Find all the columns for the relevant tables
  select all_tab_cols.owner, all_tab_cols.table_name, all_tab_cols.column_name
    --Find the relevant tables from the plans (may need to find the table behind an index)
      nvl(all_indexes.table_owner, plan_objects.object_owner) owner,
      nvl(all_indexes.table_name, plan_objects.object_name) table_name
      select object_owner, object_name, object_type
      from v$sql_plan
      where sql_id = '0k2t2y1d312j8'
          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 =')) all_possible_columns
  on relevant_tab_cols.column_name = all_possible_columns.column_value;
share|improve this answer

Your Answer


