If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Oracle > passing table name as parameter in stored procedure

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Jul 2004
Posts: 1
passing table name as parameter in stored procedure

Hi guys,

Can anyone of u help me on how to pass a table name as parameter to a stored procedure.I should use this inside the procedure to run a query.

eg:If i pass employee table as parameter(table_name) then i should be able to run a query like select * from table_name.

Right now its giving an error saying table doesnt exist.

Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Mar 2002
Location: Reading, UK
Posts: 1,133
look up dynamic sql and execute immediate in the plsql manual as this should point you in the right direction.

Reply With Quote
  #3 (permalink)  
Join Date: Sep 2002
Location: UK
Posts: 5,171
Dynamic SQL:
SQL> create or replace function select_any_table ( p_table_name in varchar2 )
  2  return sys_refcursor
  3  is
  4    rc sys_refcursor;
  5  begin
  6    open rc for 'select * from ' || p_table_name;
  7    return rc;
  8* end;
SQL> /

Function created.

SQL> var x refcursor
SQL> exec :x := select_any_table('DEPT')

PL/SQL procedure successfully completed.

SQL> print x

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
Tony Andrews
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On