How to get schema name from result set in Oracle using jdbc?

Question

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

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

However, this returns an empty string. Is there any work 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 this issue. Then we create a query that returns only rows that are informative for the given question. For example, if we had a customer database and we wanted to find out which customers are most likely to stop serving, our tool can create a query that returns 5% of customer records, which can then be executed using powerful analysis algorithms. The advantage is that we conduct our analysis only with a subset of the data, which, of course, will save time. It also turns out that high-performance analytics algorithms now work better, because the first step was basically filtering out noise from our data.

Thus, in response to OMG Ponies, the user indicates information about connecting to the database and the request as a contribution to our tool. Since they can specify any query that they like, they could connect to the foo schema connection and then run the following query:

SELECT* FROM bar.customer;

If, for some reason, eye color and gender were predictors of people stopping 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' 

, , . , , , , 99% . , 1% - , , .

Answer 1

:

OracleResultSetMetaData getSchemaName() getTableName(), .

, ResultSetMetaData Oracle, , Oracle. ( OCI , , , , ).

WebLogic 8 , , , 4 . , , getSchemaName() Oracle, .

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