never mind, funny what you can accomplish when you stop banging your head against the wall.

thought I would share with the class again

steve


where emp has a empno and deptno as a concatenated key


CREATE OR REPLACE PROCEDURE update_with_two_col_pk(p_table_name VARCHAR2)
IS
v_pk_col_1 VARCHAR2(1000); /* The first column of the pk */
v_pk_col_2 VARCHAR2(1000); /* The second column of the pk */
type ref_cursor IS REF CURSOR;
c1 REF_CURSOR;
v_pk VARCHAR2(100); /* The name of the pk constraint */
v_name_1 VARCHAR2(500);
v_name_2 VARCHAR2(500);
v_update VARCHAR2(2000); /* The first part of the update string */
v_update_two VARCHAR2(2000); /* The second part of the update string */
v_pk_count NUMBER; /* The number of columns in the pk */
BEGIN
v_pk := find_pk(p_table_name); /* Finds then name of the PK for the provided table */
v_update := build_update(p_table_name); /* Builds the first part of the update string */
v_update_two := build_update_two(p_table_name); /* Builds the second part of the update string */
v_pk_count := count_pk_cols(v_pk); /* Checks to see how many columns the PK is composed of */

/* Get the first column of the concatenated pk */
SELECT column_name INTO v_pk_col_1 FROM user_cons_columns
WHERE table_name = p_table_name
AND constraint_name = v_pk
AND position = 1;
/* Get the second column of the concatenated pk */
SELECT column_name INTO v_pk_col_2 FROM user_cons_columns
WHERE table_name = p_table_name
AND constraint_name = v_pk
AND position = 2;

OPEN c1 FOR 'SELECT ' || v_pk_col_1 || ',' || v_pk_col_2 ||' FROM ' || p_table_name;
LOOP
FETCH c1 INTO v_name_1, v_name_2;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (v_name_1 || ' ' || v_name_2);
END LOOP;
CLOSE C1;
END;
/

exec update_with_two_col_pk('EMP');