Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2001
    Posts
    45

    Question Unanswered: PL/SQL: eval OR Reference to specific column/field NUMBER

    Originally I was looking for an EVAL-like function in PL/SQL: I have a variable that contains the name of a column. Now I would like to access these column values, something like

    myvalue := myrecord_rec.MYVARIABLE;
    ^^^^^^^^^^ EVAL() ???

    How is this done in PL/SQL?

    But.....

    ...maybe eval just doesnt exist in PL/SQL...

    is there a way to refer to a certain column number?? Instead of using names I could simply choose the "value of colum(numer 2)" or something similar....??

    Thanks in advance

  2. #2
    Join Date
    Nov 2001
    Posts
    45

    Unhappy

    Alright, I guess

    EXECUTE IMMEDIATE

    does the trick... However even the smallest test doesnt work.... HEEEELP!!!!!!:

    EXECUTE IMMEDIATE 'BEGIN HTP.P("test"); END;';

  3. #3
    Join Date
    Sep 2006
    Posts
    1
    Hi all,
    I have the same problem: got a record and a variable containing the column name. What I need is a procedure get_value returning the value of the column in the record. Example:

    cursor cur is SELECT * FROM mytable;
    rec mytable%ROWTYPE;
    col varchar2;
    val varchar2;

    BEGIN

    col := 'ID';
    ...

    open cur;
    loop

    fetch cur into rec;
    exit when cur%NOTFOUND;

    get_value(rec, col, val); -- that should return rec.ID into val !!!
    ...

    end loop;
    close cur;

    END

    Can anyone help?
    Thank you!


    Quote Originally Posted by waldemar
    Originally I was looking for an EVAL-like function in PL/SQL: I have a variable that contains the name of a column. Now I would like to access these column values, something like

    myvalue := myrecord_rec.MYVARIABLE;
    ^^^^^^^^^^ EVAL() ???

    How is this done in PL/SQL?

    But.....

    ...maybe eval just doesnt exist in PL/SQL...

    is there a way to refer to a certain column number?? Instead of using names I could simply choose the "value of colum(numer 2)" or something similar....??

    Thanks in advance

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by waldemar
    Alright, I guess

    EXECUTE IMMEDIATE

    does the trick... However even the smallest test doesnt work.... HEEEELP!!!!!!:

    EXECUTE IMMEDIATE 'BEGIN HTP.P("test"); END;';
    If "test" is the variable containing the name of the column, then try something like this:
    Code:
    EXECUTE IMMEDIATE 'BEGIN HTP.P("'||test||'"); END;';

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    LK, I think your statement should be:

    Code:
    EXECUTE IMMEDIATE 'BEGIN HTP.P('''||test||'''); END;';
    feliziaa,

    Are you trying to write a cursor that will return data from different columns? If so, I'd recommend something like this:

    Code:
    PROCEDURE my_proc (col_to_select VARCHAR2)
    IS
      TYPE curvar_type is REF CURSOR;
      curvar curvar_type;
      
      cursor_value VARCHAR2(10);
     
    BEGIN
      OPEN curvar FOR 'SELECT ' || col_to_select || ' FROM my_table';
    
      WHILE TRUE LOOP
        FETCH curvar INTO cursor_value;
        EXIT WHEN curvar%NOTFOUND;
    
        ... do whatever with the value you retrieve ...
    
      END LOOP;
      CLOSE curvar;
    
    END;
    Last edited by joebednarz; 09-29-06 at 14:47.
    JoeB
    save disk space, use smaller fonts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •