passing dynamic values to FOR loop
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: passing dynamic values to FOR loop

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    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

  2. #2
    Join Date
    Oct 2000
    Posts
    467
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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 05:06 PM.

  5. #5
    Join Date
    Oct 2000
    Posts
    139
    As usual excellent reply and useful code!

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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?

  10. #10
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
  •  



Click Here to Expand Forum to Full Width