Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: dynamic column request

    Setup: 9i

    I have an ODBC client who sends an insert statement into TABLE1 defined as:
    EQ NUMBER(10)
    CD NUMBER(10)
    E1 NUMBER
    E2 NUMBER
    E3 NUMBER
    E4 NUMBER
    E5 NUMBER
    E6 NUMBER
    E7 NUMBER
    E8 NUMBER
    E9 NUMBER
    E10 NUMBER

    Each EQ, CD, En triplet needs to be stored as a record in a different table. I have an AFTER INSERT trigger on TABLE1 and in it I would like to be able to write a loop to store these records. Using dynamic sql I can create a insert statement like

    INSERT INTO table2 (eq, cd, data_point_id, value
    VALUES (11, 13, En)

    but instead of En I need the value contained in En.

    Any one know how to do this? I have tried so many different approaches I am thoroughly confused.
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    In case anyone is wondering I figured out what I needed to do.

    I took my E1 - En columns and stuffed them into a VARRAY. I then built my dynamic sql string using {varray}(loopcounter).
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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