If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I have worked on your code and it does nicely. I am also trying to get more than one column returned. The whole reason behind this is that I am trying to generate dynamic sql that will generate pk info and compare rows on the fly for update based on pk. I have 100 tables so I don't want to write static sql as this is dev to production and invariably things will change and the value of static updates will be fleeting.

I am trying to update your code to work on 2, 3 and 4 columns as the largest concatenated key in the db is 3 columns

Here is how I adjusted your code and the error below. Any suggestions?

tks

steve

CREATE OR REPLACE PROCEDURE update_with_two_col_pk(p_table_name VARCHAR2)
IS
v_pk_col_1 VARCHAR2(100); /* The first column of the pk */
v_pk_col_2 VARCHAR2(100); /* 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 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;

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;