-
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
I'm stmontgo and I approve of this message
-
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;
/
Vinit
-
tks vinit but i also want to be able to dynamiclly pass the column name as well.
I'm stmontgo and I approve of this message
-
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
Last edited by pando; 12-15-2002 at 06:06 PM.
-
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);
I'm stmontgo and I approve of this message
-
forgot to add that i create a concentanted pk on EMP consisting of (EMPNO and ENAME) for testing purposes
steve
I'm stmontgo and I approve of this message
-
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');
I'm stmontgo and I approve of this message
-
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;
/
I'm stmontgo and I approve of this message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|