Forum Stats

  • 3,874,594 Users
  • 2,266,750 Discussions
  • 7,911,908 Comments

Discussions

Categories

Problem with variable in procedure

646894
646894 Member Posts: 63
edited Jan 21, 2010 4:18AM in SQL & PL/SQL
Hello,

I have a string in a variable.

ignore_column_name := 'LAST_NAME';

I have a cursor in a procedure

CURSOR C1 is
SELECT column_name
FROM user_tab_columns
WHERE table_name = 'PATIENT'
AND column_name NOT IN (ignore_column_name);

I am using the above output to generate a column list and hence i should not be seeing 'LAST_NAME' in the output.

But this is not the case. For some strange reason the variable value does not get substituted into the cursor.

Could someone please help me get the desired result.

Thanks
Tagged:
· Share on TwitterShare on Facebook

Answers

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    show it 
    SQL> declare
  2     ignore_column_name varchar2 (100) := 'ENAME';
  3     CURSOR C1 is
  4     SELECT column_name
  5     FROM user_tab_columns
  6     WHERE table_name = 'EMP'
  7  --   AND column_name NOT IN (ignore_column_name)
  8  ;
  9  begin
 10     for r in c1
 11     loop
 12        dbms_output.put_line ('result: '||r.column_name);
 13     end loop;
 14  end;
 15  /
result: EMPNO
result: ENAME
result: JOB
result: MGR
result: HIREDATE
result: SAL
result: COMM
result: DEPTNO

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> declare
  2     ignore_column_name varchar2 (100) := 'ENAME';
  3     CURSOR C1 is
  4     SELECT column_name
  5     FROM user_tab_columns
  6     WHERE table_name = 'EMP'
  7     AND column_name NOT IN (ignore_column_name)
  8  ;
  9  begin
 10     for r in c1
 11     loop
 12        dbms_output.put_line ('result: '||r.column_name);
 13     end loop;
 14  end;
 15  /
result: EMPNO
result: JOB
result: MGR
result: HIREDATE
result: SAL
result: COMM
result: DEPTNO

PL/SQL procedure successfully completed.

SQL>
    · Share on TwitterShare on Facebook
  • 646894
    646894 Member Posts: 63
    Hello,

    the only difference between your program and mine is that..

    ignore_column_name variable is declared but not initialized before the cursor. I mean..

    declare
    ignore_columns_name varchar2(100);
    cursor c1 is
    select column_name
    from user_tab_columns
    where column_name not in (ignore_column_name);
    begin
    ignore_column_name := 'LNAME';
    for r in c1
    loop
    column := column||','||r.column_name;
    end loop;
    ##REST OF THE CODE##
    end;
    /

    the thing is .. ignore_column_name is not hardcoded, instead it is generated from a different part of the code. so I cannot initialise it like u have shown it. Can u help me? is it possible to achieve this?

    Thanks
    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,565 Red Diamond
    Azeroth wrote:
    Hello,

    the only difference between your program and mine is that..

    ignore_column_name variable is declared but not initialized before the cursor. I mean..

    the thing is .. ignore_column_name is not hardcoded, instead it is generated from a different part of the code. so I cannot initialise it like u have shown it. Can u help me? is it possible to achieve this?

    Thanks
    It doesn't make a difference if it's initialized before the cursor declaration or not, just as long as it's initialized before the cursor is opened.
    SQL> declare
  2    ignore_column_name varchar2 (100);
  3    CURSOR C1 is
  4    SELECT column_name
  5    FROM user_tab_columns
  6    WHERE table_name = 'EMP'
  7    AND column_name NOT IN (ignore_column_name);
  8  begin
  9    ignore_column_name := 'ENAME';
 10    for r in c1
 11    loop
 12       dbms_output.put_line ('result: '||r.column_name);
 13    end loop;
 14  end;
 15  /
result: EMPNO
result: JOB
result: MGR
result: HIREDATE
result: SAL
result: COMM
result: DEPTNO

PL/SQL procedure successfully completed.

SQL>
    BluShadow
    · Share on TwitterShare on Facebook
  • 646894
    646894 Member Posts: 63
    Hello,

    This was a really helpful reply.. it cleared my only suspicion but not my problem. Here I am pasting the entire code.. I will highlight the part thats not working.. Please let me know what you think the problem is.

    CREATE OR REPLACE PROCEDURE
    test_etl1
    (
    table1 IN user_tab_columns.table_name%TYPE,
    table2 IN user_tab_columns.table_name%TYPE
    ) IS
    col_list VARCHAR2(3000);
    new_col_list VARCHAR2(3000);
    column_name VARCHAR2(100);
    num number;
    table3 VARCHAR2(100);
    column_list VARCHAR2(3000);
    stmt VARCHAR2(3000);
    CURSOR C1 IS
    SELECT column_list
    FROM ignore_columns
    WHERE table_name = table1;
    CURSOR C2 IS
    SELECT column_name
    FROM user_tab_columns
    WHERE table_name = table3
    AND column_name NOT IN (new_col_list);
    BEGIN
    col_list := null;
    FOR R1 in C1
    LOOP
    col_list := R1.column_list;
    END LOOP;
    num := length(col_list);

    WHILE num <> 1
    LOOP
    column_name := SUBSTR(col_list, 0,INSTR(col_list,' '));

    IF (NVL(SUBSTR(col_list, INSTR(col_list, ' '), LENGTH(col_list)),'1') != '1')
    THEN
    col_list := SUBSTR(col_list, INSTR(col_list, ' '), LENGTH(col_list));
    col_list := LTRIM(col_list);
    new_col_list := new_col_list||''''||RTRIM(column_name)||''''||',';
    ELSE
    num := 1;
    END IF;
    END LOOP;

    new_col_list := RTRIM(new_col_list,',');

    table3 := upper(table1);
    FOR R2 IN C2
    LOOP
    * column_list := column_list||''''||R2.column_name||''''||',';*
    END LOOP;
    column_list := RTRIM(column_list,',');
    stmt := 'CREATE TABLE error_table AS SELECT '||column_list||' FROM '||table1||' MINUS SELECT '||column_list||'FROM '||table2;

    END;

    Table ignore_columns contains a single row.
    table_name = patient_v
    column_list = 'LAST_NAME'; with the quotes

    Thanks
    · Share on TwitterShare on Facebook
This discussion has been closed.