Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    16

    Unanswered: Comma Seperated List Handling

    Hi,
    I'm Kinda stuck with using comma seperated list. I get 2 input parameter to my proc which is comma seperated list. I need to convert them into list and update a table.

    Example Input :

    childid - '1,2,3,4'
    val - '5,6,7,8'

    i pass such input and i need to update table x

    i.e

    update X set val = 5
    where childid = 1

    In Similar fashion i need to update val 6,7,8 for childid's - 2,3,4.

    Can someone please throw some light, so that i can achieve what am trying for.

    Here is what i tried.

    PROCEDURE Update_val(pChildID IN VARCHAR2,
    pVal IN VARCHAR2,
    ReturnStatus OUT VARCHAR2)

    IS

    v_code NUMBER;
    v_errm VARCHAR2(64);
    pReturnstatus VARCHAR2(40);
    v_id X.id%TYPE;
    v_avg_bal X.Avg_Ledger_Bal%TYPE;

    cursor c1_list is
    select t1.column_value as "ID" , t2.column_value as "Val"
    from table(antistragg(pChildScenarioID)) t2,
    table(antistragg(pPropAvgDailyBal)) t1;


    -- Tried to use 2 loops
    /* cursor c1_c_list is select t1.column_value as "ID"
    from table(antistragg(pChildID)) t1;

    cursor c1_c_avg_list is select t1.column_value as "AVG_BAL"
    from table(antistragg(pVal)) t1;*/

    BEGIN

    -- I tried like this :

    FOR c1_rec in c1_list LOOP
    update x
    set val = c1_rec.val
    where childid = c1_rec.ID;

    end loop;

    -- using 2 loops
    /* for c1_rec in c1_c_list loop
    for c2_rec in c1_c_avg_list loop
    update x
    set val = c2_rec.AVG_BAL
    where childid = c1_rec.ID;
    end loop;
    end loop;*/

    ReturnStatus := 'SUCCESS';

    EXCEPTION
    WHEN OTHERS THEN
    ReturnStatus := 'FAILED';
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.PUT_LINE('Error Code ' || v_code || ':' || v_errm);

    END Update_val;


    The function Antistragg :

    CREATE OR REPLACE FUNCTION ANTISTRAGG( p_string IN VARCHAR2, p_delim IN VARCHAR2 := ',' )
    RETURN AntistraggType
    AS
    l_string LONG DEFAULT p_string || p_delim;
    l_data AntistraggType := AntistraggType();
    n NUMBER;

    BEGIN
    LOOP
    EXIT WHEN l_string IS NULL;
    n := INSTR( l_string, p_delim );
    l_data.EXTEND;
    l_data(l_data.COUNT) :=
    LTRIM( RTRIM( SUBSTR( l_string, 1, n-1 ) ) );
    l_string := SUBSTR( l_string, n+1 );
    END LOOP;

    RETURN l_data;
    END;


    Please Help.

    Thanks,
    Vijay

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what should occur when the two strings contain different number of commas?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2008
    Posts
    16
    Hi, Thanks for the reply. The two strings will always contain equal number of comma's.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by vijayarajvp View Post
    Hi, Thanks for the reply. The two strings will always contain equal number of comma's.
    A professional implementation of PROCEDURE Update_val would properly handle the case where the numbers of commas is different.
    It is lazy & sloppy to not code for this possibility, since result will be indeterminate or wrong.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2008
    Posts
    16
    Hi,
    I myself found this workaround. Its working too..

    PROCEDURE Update_val(pChildID IN VARCHAR2,
    pVal IN VARCHAR2,
    ReturnStatus OUT VARCHAR2)

    IS

    v_code NUMBER;
    v_errm VARCHAR2(64);
    pReturnstatus VARCHAR2(40);

    BEGIN

    merge into X s
    using (with a as (select C_id, valu
    from (select column_value C_id, rownum rn
    from table(antistragg(pChildID))) a,
    (select column_value valu, rownum rn
    from table(antistragg(pVal))) b
    where a.rn = b.rn)
    select *
    from a) b on (s.id = b.C_id) when matched then
    set val = valu
    where childid = C_id;

    ReturnStatus := 'SUCCESS';

    EXCEPTION
    WHEN OTHERS THEN
    ReturnStatus := 'FAILED';
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.PUT_LINE('Error Code ' || v_code || ':' || v_errm);

    END Update_val;

    Thanks,
    Vijay

Posting Permissions

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