PL/SQL: Oracle DB table as input parameter?!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: PL/SQL: Oracle DB table as input parameter?!

  1. #1
    Join Date
    Oct 2005
    Posts
    2

    PL/SQL: Oracle DB table as input parameter?!

    Hi!
    I've been desperately trying figure out how to set a table name as an input parameter. Here is the beginning of my code where I declare the table input variable (v_eim_table):

    create or replace procedure BATCH_RENUM(k_records_in_batch NUMBER, k_records_to_commit NUMBER, v_batch_num NUMBER, v_eim_table VARCHAR2) is

    Here is how I am using this table variable in my cursor variable:

    DECLARE
    CURSOR batch_cur IS
    SELECT ROW_ID,
    IF_ROW_BATCH_NUM
    FROM v_eim_table
    WHERE IF_ROW_BATCH_NUM = v_batch_num
    ORDER BY IF_ROW_BATCH_NUM,
    ROW_ID;

    I get the error that v_eim_table variable must be declared! Please help!
    Thanks,
    Karen

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You'd have to use execute immediate or DBMS_SQL to change the table name like this. On the other hand, if you had a limited selection of tables that all exist then you might do something like:
    Code:
    CURSOR batch_cur IS
    SELECT ROW_ID,
    IF_ROW_BATCH_NUM
    FROM TABLE1
    WHERE IF_ROW_BATCH_NUM = v_batch_num
    AND 'TABLE1' = v_eim_table
    UNION ALL
    SELECT ROW_ID,
    IF_ROW_BATCH_NUM
    FROM TABLE2
    WHERE IF_ROW_BATCH_NUM = v_batch_num
    AND 'TABLE2' = v_eim_table
    ORDER BY 2, 1;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Oct 2005
    Posts
    2
    I took your second coding suggestion and it worked! Thanks so much for your help!!!!!
    Karen

Posting Permissions

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


Click Here to Expand Forum to Full Width