Accessing the tablename in the from clause of a query from a PL/SQL function

Supposing I fire a query:

select MyProc(id) from tableName;

Is there a way I can access the tableName used in the from clause in the above query, from the procedure: MyProc()?

I would then be able to开发者_StackOverflow社区 dynamically use the 'from table' in the procedure MyProc().'

Thanks.

No, you can't do that. You could send the table as a parameter of the procedure and use dynamic SQL inside your procedure:

SELECT MyProc( id, 'tablename' ) FROM dual;

But even that is awkward and has all the limitations of dynamic SQL. Why does your procedure need the table name? What is it that you're trying to do?

You'll have to use a dynamically built query to do what you're attempting. Try something like

strSQL  VARCHAR2(32767);
csr     SYS_REFCURSOR;
nVal1    NUMBER;
nVal2    NUMBER;
strVal3  VARCHAR2(2000);

strSQL := 'SELECT val1, val2, val3 FROM ' || tableName || ' WHERE whatever = somethingelse';

OPEN csr FOR strSQL;
FETCH csr INTO nVal1, nVal2, strVal3;
CLOSE csr;

Share and enjoy.

create or replace function myProc(p_id number) return varchar2 is
    v_sql_id varchar2(13);
    v_table_name varchar2(100);
begin
    --Get the SQL used to call this function
    select sql_id into v_sql_id
    from v$sql
    where lower(sql_text) like 'select myproc(id)%'
        and users_executing > 0;

    --Get the table name
    select object_name into v_table_name
    from v$sql_plan
    where sql_id = v_sql_id
        and operation = 'TABLE ACCESS';

    --For testing, return the table name.
    return v_table_name;
end;
/

create table test1(id number);
create table test2(id number);
insert into test1 values(1);
insert into test2 values(2);
commit;

--Returns TEST1 (careful, your IDE may add this comment to the SQL!)
select MyProc(id) from test1;

--Returns TEST2
select MyProc(id) from test2;

The idea here is to find the currently executing SQL, and then find the table used by that SQL. But there are a lot of potential problems.

Getting the SQL_ID

There are lots of ways of finding the SQL_ID, but none of them work well. Here are the approaches I tried, maybe someone can figure out how to make one of them work better.

For example, in v$session the SQL_ID will refer to itself, and PREV_SQL_ID refers to some useless transaction query (at least on my system).

select sql_id, prev_sql_id from v$session where sid = sys_context('USERENV', 'SID');

Finding the query in v$sql and ordering the LAST_LOAD_TIME doesn't always work, LAST_LOAD_TIME is not always updated.

select sql_id from v$sql
where lower(sql_text) like 'select myproc(id)%'
order by last_load_time desc;

Using the SQL_TEXT and USERS_EXECUTING > 0 will work, but only if only one session is executing this query at a time. And searching for text like this is very dangerous. Some environments might put text before the select, such as spaces or comments. But you can't search for '%select...' because then the query would return itself.

select sql_id into v_sql_id
from v$sql
where lower(sql_text) like 'select myproc(id)%'
    and users_executing > 0;

Finding the table

With the SQL_ID we can easily get the text of the query from v$sql.sql_text or v$sql.sql_fulltext. It might be possible for you to parse that query, but in general I'd recommend you avoid parsing SQL. It's much more difficult than most people think. If you're absolutely sure that only a specific, simple query will be used, then maybe that approach will work.

A more realistic approach is probably to use v$sql_plan to find the tables used. This will work for the query you have, but you'll have to do more work if your query can have multiple tables, or if there are views, or indexes (you'll have to join to user_index to find the actual table), etc.

select object_name
from v$sql_plan
where sql_id = <SQL_ID>
    and operation = 'TABLE ACCESS'

You'll probably need to grant select on v_$sql and v_$sql_plan to the user. Oh, and this will be really slow. @Eaolson's idea of passing in the table name as a parameter is much better, if it works.