• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3129
  • Last Modified:

How can I dynamically create a %rowtype variable

Oracle 10

How can I dynamically create a %rowtype variable for a table_name that has been passed into a procedure?
 
In my example code I want to change DVR_REC RT_TEST.MEMBER%ROWTYPE into something like DVR_REC RT_TEST. || table_name || %ROWTYPE
 
Long winded explanation of how Ive painted myself into this corner:
 
My assignment is basically archiving data from a production database to a smaller archive database. I will be given a list of what rows in a particular table to archive and I must move those rows and all matching rows from all related tables.
 
The relationships in the production database are documented in a table where each row represents 1 primary table to foreign table relationship (Im using the terms primary and foreign rather loosely here, quite possibly backward). The table contains 4 columns: primary table name, primary key field name, foreign table name, foreign key field name.
 
To this end I am writing a procedure that copies the original table row to the archive database and then copy rows from the related primary tables where there key field equals the value of the matching filed in the original row.
 
In this procedure I use dynamic SQL to assemble the insert command. For this command I need to get the value for the primary tables key field from the original row. To do this I select the original row into a %rowtype variable and use the primary key name (from the relations table) as an index to the %rowtype variable.
 
The original table name is passed into the procedure and I need to dynamically create the %rowtype variable.
 
So how can I dynamically create a %rowtype variable for a table_name that has been passed into a procedure?
 
Code sample:
 
CREATE OR REPLACE PROCEDURE TEST (TABLE_NAME VARCHAR2, FIELD_NAME VARCHAR2, FIELD_VALUE VARCHAR2)
 AS
 
  -- CURSORS
 TYPE CV_TYPE IS REF CURSOR;
 CUR_DRIVER CV_TYPE;
 CUR_REF CV_TYPE;
 
--************************************************
--************************************************
 
 DVR_REC RT_TEST.MEMBER%ROWTYPE;
 
--************************************************
--************************************************
 
 
 
 REF_REC RT_TEST.REF_TEST%ROWTYPE;
 
 COMMAND VARCHAR2 (500);
 COMMAND1 VARCHAR2 (500);
 COMMAND2 VARCHAR2 (500);
 COMMAND3 VARCHAR2 (500);
 COMMAND4 VARCHAR2 (500);
 
 VALUE      VARCHAR2 (30);
 
BEGIN
 
COMMAND := 'SELECT * FROM mvppept.' || TABLE_NAME || ' WHERE ' || FIELD_NAME || ' = ''' || FIELD_VALUE || '''';
 --dbms_output.put_line (COMMAND);
 
 
 OPEN CUR_DRIVER FOR COMMAND;
 LOOP
      FETCH CUR_DRIVER INTO DVR_REC;
      EXIT WHEN CUR_DRIVER%NOTFOUND;
 
-- COPY ROW FROM DRIVER TABLE IN THE MASTER DATABASE TO SAME NAME -- TABLE IN THE MINI DATABASE AND TEMP TABLE
DELETE FROM RT_TEST.MEMBER1;              -- CREATE A TENP TABLE WITH A SINGLE ROW
INSERT INTO RT_TEST.MEMBER1 VALUES DVR_REC;     -- FROM THE DRIVER TABLE
      --INSERT INTO RT_TEST.MEMBER VALUES DVR_REC;
     
      OPEN CUR_REF FOR 'SELECT * FROM RT_TEST.REF_TEST
WHERE FT = ''' || TABLE_NAME || ''' and rownum < 9  ORDER BY PT, FT';
     
        LOOP
-- GET RECORD THAT ASSOCIATES A TABLE AND KEY FIELD WITH THE DRIVER TABLE AND DRIVER KEY FIELD
            FETCH CUR_REF INTO REF_REC;
            EXIT WHEN CUR_REF%NOTFOUND;
 
-- GET THE VALUE OF THE FK COLUMN OF THE DRIVER RECORD FROM THE TEMP TABLE
COMMAND := 'SELECT ' || REF_REC.FK || ' FROM
RT_TEST.MEMBER1';
            EXECUTE IMMEDIATE COMMAND INTO VALUE;
 
-- COPY ROW FROM ASSOCIATED TABLE IN FULL DATABASE TO THE --SAME TABLE IN THE MINI DATABASE
-- BASSED ON THE VALUE OF THE DRIVER AND ASSOCIATED TABLE KEY FIELDS.
COMMAND1 := 'INSERT INTO RT_TEST.' || REF_REC.PT || ' SELECT * FROM MVPPEPT.'|| REF_REC.PT
||' WHERE ' || REF_REC.PK  || ' = ''' ||  RTRIM (VALUE) || '''';
            dbms_output.put_line (COMMAND1);
      END LOOP;
 END LOOP;
 
END;
 
/
0
rmtye
Asked:
rmtye
1 Solution
 
dqmqCommented:
You cannot do a dynamic %rowtype, AFAIK.  That's the bad news.  The good news is that I doubt you need one.

My suggestion is to eliminate the outside cursor and do your inserts like this (pseudo code):

begin transaction;    --please

--insert row in driver table table
COMMAND := 'Insert into Test.' || TABLE_NAME || ' select * from MVPPEPT. ' || TABLE_NAME || ' where ' || FIELD_NAME || ' = ''' || FIELD_VALUE || '''';

EXECUTE IMMEDIATE COMMAND

--now cursor through child tables
OPEN CUR_REF FOR 'SELECT * FROM RT_TEST.REF_TEST
WHERE FT = ''' || TABLE_NAME || ''' and rownum < 9  ORDER BY PT, FT';
        LOOP
-- GET RECORD THAT ASSOCIATES A TABLE AND KEY FIELD WITH THE DRIVER TABLE AND DRIVER KEY FIELD
            FETCH CUR_REF INTO REF_REC;
            EXIT WHEN CUR_REF%NOTFOUND;

--Don't need to lookup FK value, use join instead:


-- COPY ROW FROM ASSOCIATED TABLE IN FULL DATABASE TO THE --SAME TABLE IN THE MINI DATABASE
-- BASSED ON THE VALUE OF THE DRIVER AND ASSOCIATED TABLE KEY FIELDS.
COMMAND1 := 'INSERT INTO RT_TEST.' || REF_REC.PT || ' SELECT * FROM MVPPEPT.'|| REF_REC.PT
 
|| ' C INNER JOIN MVPPEPT. ' || TABLE_NAME || ' P ON P.' || REF_REC.PK || ' = C.' ||  REF_REC.FK
 ' WHERE C.' || FIELD_NAME || ' = ''' || FIELD_VALUE || '''';

 --EXECUTE IMMEDIATE COMMAND1;
 
 dbms_output.put_line (COMMAND1);
      END LOOP;

Commit; --when happy





0
 
MikeOM_DBACommented:

1) It would be easier if your archive database mirror the production schema.
-- or --
2) Design the archive database as "Data Warehouse or Data Mart" type and use standard extract-transfer-and-load (ETL) procedures to move the data.
0
 
rmtyeAuthor Commented:
Well I lied, the application realy isn't archiving; but, the results are the same and describing it as archiveing clearly described the concept.

Ron
0
 
Mark GeerlingsDatabase AdministratorCommented:
Please note that while Oracle PL\SQL supports some dynamic SQL syntax if you use "execute immediate" or the more complex procedures in DBMS_SQL, PL\SQL is certainly *NOT* optimized for dynamic SQL statements!

Yes, in some cases dynamic SQL statements can make for fewer lines of PL\SQL code, and/or fewer procedures, but be aware that performance of dynamic SQL statements in PL\SQL will be slower than similar procedures that use static SQL statements.  So, if performance is an issue (do you know of an Oracle database where performance is not an issue?) you may need to consider a procedure for each table with static SQL statements.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now