-
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
-
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;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|