passing dynamic values to FOR loop

Ok sorry if this seems simple but zero sleep is catching up

I'd like to accomplish the following but I want to dynamically pass
EMPNO and EMP to the FOR loop as opposed to hard coding them.

The desired result set looks like the following with the values
hard coded as below

set serveroutput on

CREATE OR REPLACE PROCEDURE merge_one
AS
BEGIN
FOR x IN (SELECT empno FROM emp) LOOP
dbms_output.put_line(x.empno);
END LOOP;
END;
/

exec merge_one;


7369
7499
7521
7566
7654
7698
7782
7788
7839
7844


However, when I try and pass EMPNO and EMP dynamically I am not getting what I want. Here is what I tried


set serveroutput on

CREATE OR REPLACE PROCEDURE merge_two (p_table_name VARCHAR2)
AS
var_col_1 VARCHAR2(100);
BEGIN
SELECT column_name INTO var_col_1 FROM user_tab_cols WHERE table_name = p_table_name AND column_name = 'EMPNO';
FOR x IN (SELECT var_col_1 FROM p_table_name) LOOP
dbms_output.put_line(x.var_col_1);
END LOOP;
END;
/


Warning: Procedure created with compilation errors.

Errors for PROCEDURE MERGE_TWO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/10 PL/SQL: SQL Statement ignored
6/32 PL/SQL: ORA-00942: table or view does not exist
7/3 PL/SQL: Statement ignored
7/24 PLS-00364: loop index variable 'X' use is invalid



I also tried this

CREATE OR REPLACE PROCEDURE merge_three (p_table_name VARCHAR2)
AS
var_col_1 VARCHAR2(100);
BEGIN
SELECT column_name INTO var_col_1 FROM user_tab_cols WHERE table_name = p_table_name AND column_name = 'EMPNO';
FOR x IN (SELECT var_col_1 FROM user_tab_cols WHERE table_name = p_table_name) LOOP
dbms_output.put_line(x.var_col_1);
END LOOP;
END;
/

exec merge_three('EMP');

resulting in

EMPNO
EMPNO
EMPNO
EMPNO
EMPNO
EMPNO
EMPNO
EMPNO


any ideas?

tks

steve

Try this..

CREATE OR REPLACE PROCEDURE merge_two (p_table_name VARCHAR2)
AS
cursor c1 is select column_name FROM user_tab_cols WHERE table_name = p_table_name AND column_name = 'EMPNO';

var_col_1 VARCHAR2(100);
BEGIN
FOR x IN c1 LOOP
dbms_output.put_line(x.column_name);
END LOOP;
END;
/

tks vinit but i also want to be able to dynamiclly pass the column name as well.

Hi

you have to use dynamic sql

Code:

---


SQL> CREATE OR REPLACE PROCEDURE DYNAMIC(P_TABLE_NAME VARCHAR2, P_COLUMN_NAME VARCHAR2)
  2  IS
  3    VAR_COL_1          VARCHAR2(100);
  4    TYPE REF_CURSOR IS REF CURSOR;
  5    C1                REF_CURSOR;
  6    VAR_ENAME          VARCHAR2(36);
  7  BEGIN
  8    SELECT COLUMN_NAME
  9      INTO VAR_COL_1
 10      FROM USER_TAB_COLUMNS
 11    WHERE TABLE_NAME=P_TABLE_NAME
 12      AND COLUMN_NAME=P_COLUMN_NAME;
 13    OPEN C1 FOR 'SELECT '||VAR_COL_1||' FROM '||P_TABLE_NAME;
 14    LOOP
 15    FETCH C1 INTO VAR_ENAME;
 16    EXIT WHEN C1%NOTFOUND;
 17    DBMS_OUTPUT.PUT_LINE(VAR_ENAME);
 18    END LOOP;
 19    CLOSE C1;
 20  END;
 21  /

Procedure created.

SQL> EXEC DYNAMIC('EMP', 'ENAME')
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

SQL> EXEC DYNAMIC('EMP', 'EMPNO')
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

However I think you dont need th SELECT into part since we are passing column name as well

CREATE OR REPLACE PROCEDURE DYNAMIC(P_TABLE_NAME VARCHAR2, P_COLUMN_NAME VARCHAR2)
IS
  TYPE REF_CURSOR IS REF CURSOR;
  C1                REF_CURSOR;
  VAR_ENAME          VARCHAR2(36);
BEGIN
  OPEN C1 FOR 'SELECT '||P_COLUMN_NAME||' FROM '||P_TABLE_NAME;
  LOOP
  FETCH C1 INTO VAR_ENAME;
  EXIT WHEN C1%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(VAR_ENAME);
  END LOOP;
  CLOSE C1;
END;
/

---

regards

As usual excellent reply and useful code!

tks again and sorry for the follow up,

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;

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

exec update_with_two_col_pk('EMP');


Procedure created.

BEGIN update_with_two_col_pk('EMP'); END;

*
ERROR at line 1:
ORA-00932: inconsistent datatypes
ORA-06512: at "WR.UPDATE_WITH_TWO_COL_PK", line 31
ORA-06512: at line 1



If I get this to work my code will be some derivation of an update based on a column with only one col in the pk like below.

DBMS_OUTPUT.PUT_LINE (' UPDATE ' || p_table_name || '@wjade2 a SET ' || var_update || ' = ' || var_update_two || ' FROM ' ||
p_table_name || ' b WHERE a.' || var_col_1 || ' = b.' || var_col_1 || ') WHERE a.' || p_column_name || ' = ' || var_name);

forgot to add that i create a concentanted pk on EMP consisting of (EMPNO and ENAME) for testing purposes

steve

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');

well with that approach you must know beforehand how many columns you have... for example it would work if you only have one or three columns?

yes, you are right

a simple call to something like this and then proceed on from there

CREATE OR REPLACE FUNCTION count_pk_cols (p_constraint_name IN VARCHAR) RETURN NUMBER
AS
var_col_count VARCHAR2(100);
BEGIN
SELECT COUNT(column_name) INTO var_col_count
FROM user_cons_columns
WHERE constraint_name = p_constraint_name;
-- FOR DEBUGGING dbms_output.put_line(var_col_count);
RETURN var_col_count;
END;
/

Hi

but are you gonna create a procedure for each number of columns....???

that was my line of thinking be it an elegant solution or not, and also the fact that this db has no more than 3 cols in concatenated keys.