Results 1 to 3 of 3
  1. #1
    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.

    Thanks,
    madhu

  2. #2
    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.

    Alan

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Dynamic SQL:
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •