|
Tip of the Week
(Archived)
The 500
Column Question
Another OTN forum question this week...The thread is
Insert statement - Please help. The problem is:
-
A 500 column (wow!) table with rows that need to be
duplicated.
-
The table contains a sequence driven primary key.
-
The only change between the original row and the
duplicate row is that the primary key is set to the next value in the
sequence.
-
No trigger exists on the table to assign the next
sequence value to the primary key, and there seems to be reluctance to
add a trigger.
-
Can this be done without specifying all 500 columns in
the insert statement?
To the best of my knowledge, doing this in a single insert
statement would require specifying all 500 columns. But there is an
alternative, by using PL/SQL and, with Oracle9i r2, record based DML.
In the following example, I am going to create a sequence
t_seq, a table t with a sequence driven primary key and 500 more
columns (ok, only 2, but it really does not make any difference), and
a function f_dup_row that will duplicate the row for an input primary
key, returning the primary key of the new row as output.
CREATE
SEQUENCE t_seq;
Sequence created.
CREATE TABLE t
(t_id INTEGER PRIMARY KEY,
cola VARCHAR2(1),
colb VARCHAR2(1));
Table created.
CREATE OR REPLACE FUNCTION f_dup_row (a_t_id INTEGER)
RETURN INTEGER
AS
r_t t%ROWTYPE;
-- Define a record type based on the table
BEGIN
--
SELECT *
-- Retrieve the row you want to duplicate
INTO r_t
FROM t
WHERE t_id = a_t_id;
--
SELECT t_seq.NEXTVAL
-- Update the primary key from the sequence
INTO r_t.t_id
FROM dual;
--
INSERT INTO t VALUES r_t; -- Insert new row
(uses record DML)
--
RETURN r_t.t_id;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END f_dup_row;
/
Function created.
Notice in the above function no column other than the
primary key is mentioned by name at all. The use of a PL/SQL record in the
insert statement is the record-based DML that is new with Oracle9i r2. The
example below shows the function in action.
INSERT
INTO t VALUES (t_seq.NEXTVAL, 'a', 'a');
1 row created.
COMMIT;
Commit complete.
VARIABLE new_pk NUMBER
EXEC :new_pk := f_dup_row (1);
PL/SQL procedure successfully completed.
PRINT :new_pk
NEW_PK
----------
2
SELECT * FROM T;
T_ID C C
---------- - -
1 a a
2 a a
2 rows selected.
The row is duplicated with a new primary key without ever
having to reference any column other than the primary key.
Note: This tip was tested on Oracle9i.
Suggestions or comments? Click
here for the tip feedback page. Thank you. |
