Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Define variable a ROWTYPE based on a table name passed as a parameter

586609Nov 30 2009 — edited Dec 2 2009
I am trying to write a procedure that processes rows for tables of differing structures. Table_name is passed as an IN parameter to the procedure. I define a cursor within the function that is defined by a SELECT * FROM table_name. I also need to define two variables, l_this_row and l_prev_row. I need to define them as table_name%ROWTYPE. Of course, table_name is a VARCHAR2 that contains the name of a table, whereas Oracle is expecting an actual table name in the declaration. How can I define these variables to make something like the code shown below compile and work properly? I am using Oracle 10gR2.

DEFINE PROCEDURE foo(table_name IN VARCHAR2) IS
l_this_row table_name%ROWTYPE;
l_prev_row table_name%ROWTYPE;
l_cursor SYSREFCURSOR;
BEGIN
OPEN l_cursor FOR
'SELECT * FROM '
|| table_name
|| ' ORDER BY '
|| get_pk_col_name_for_table(table_name)
|| ', version_num'; --version_num is guaranteed to be a valid column name in every table, although the other column names vary in name, quantity, and type per table.

LOOP
FETCH l_cursor INTO l_this_record;
EXIT WHEN l_cursor%NOTFOUND;

-- dynamic comparison of l_this_record and l_prev_record and conditional processing based on the comparison occurs here

l_prev_record := l_this_record;
END LOOP;
END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 30 2009
Added on Nov 30 2009
10 comments
1,973 views