1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dynamic Client_sys.add_to_attr

Discussion in 'SQL PL/SQL' started by eight, Feb 12, 2015.

  1. eight

    eight Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    PROCEDURE New_ (
    err_msg_ OUT VARCHAR2,
    ora_error_ OUT VARCHAR2,
    rec_ IN kdadb_mdm_part_all_tab%ROWTYPE) -- incoming record with 240 fields
    IS
    attr_ VARCHAR2(2000);
    info_ VARCHAR2(2000);
    objid_ VARCHAR2(2000);
    objversion_ VARCHAR2(2000);
    USER_DEFINED EXCEPTION;
    counter_ NUMBER := 0;
    conn_field_ VARCHAR2(5);

    --select basic data
    CURSOR check_exists (conn_field_ IN VARCHAR2) IS
    SELECT *
    FROM kdadb_mdm_convert t
    WHERE t.conn_field = conn_field_;
    convert_rec_ check_exists%ROWTYPE;

    BEGIN
    client_sys.Clear_Attr(attr_);
    New__(info_, objid_, objversion_, attr_, 'PREPARE');
    --Add 3 default fields,
    client_sys.Add_To_Attr('MESSAGE_ID', rec_.message_id, attr_);
    client_sys.Add_To_Attr('MESSAGE_LINE', rec_.message_line, attr_);
    client_sys.Add_To_Attr('C00', rec_.c00, attr_);
    --all other values should be added to attr_ only if basic data exists
    --Start with C values C01 to C99
    counter_ := 1;
    WHILE counter_ < 100
    LOOP
    convert_rec_ := NULL;
    conn_field_ :='C'||LPAD(counter_,2,0);
    OPEN check_exists(conn_field_);
    FETCH check_exists INTO convert_rec_;
    IF check_exists%FOUND
    THEN
    CLOSE check_exists;
    --This the normal add to attr string.
    --client_sys.Add_To_Attr('C01', rec_.c01, attr_);
    --This is how I want to do it, but then I do not get the value of the rec_.
    client_sys.Add_To_Attr(conn_field_, rec_.||counter_, attr_);
    --I do not want to add 480 row like below.
    --Can it be solved dynamicly?
    --client_sys.Add_To_Attr(conn_field_, rec_.C03, attr_);
    --IF conn_field_ = 'C01' THEN
    -- client_sys.Add_To_Attr(conn_field_, rec_.C01, attr_);
    -- ELSIF conn_field_ = 'C02' THEN
    -- client_sys.Add_To_Attr(conn_field_, rec_.C02, attr_);
    -- ELSIF conn_field_ = 'C03' THEN
    -- client_sys.Add_To_Attr(conn_field_, rec_.C03, attr_);
    -- END IF;
    ELSE
    CLOSE check_exists;
    END IF;
    counter_ := counter_ + 1;
    END LOOP;

    New__(info_, objid_, objversion_, attr_, 'DO');

    EXCEPTION
    WHEN USER_DEFINED THEN
    ROLLBACK;
    WHEN OTHERS THEN
    ora_error_ := SUBSTR( SQLERRM, 1, 200 );
    ROLLBACK;
    END New_;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    client_sys.Add_To_Attr(conn_field_, 'rec_.'||counter_, attr_);


    Presuming rec_. is a constant string not referencing a column name.
     
  3. eight

    eight Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi David
    Thanks for your reply.

    The solution you suggest makes the code possible to compile.
    but it gives the wrong result.

    the result will be rec_.C03 and I and want the value of the record of rec_.C03 in my case a 'Y'.
    Peter
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then it's an easy edit to fix this, I was hopeful you could figure it out:


    client_sys.Add_To_Attr(conn_field_, rec_||'.'||counter_, attr_);