Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011

    Unanswered: PL/SQL and reusing local variables

    Hi guys!

    Im pretty new to pl/sql, so I dont know if this is working as intended, or just needs a rewrite.

    I'm calling procedure 'acct_insert' from procedure 2 multiple times, but acct_insert only inserts one of the rows.

    I tried to make an example to illustrate my issue:

    v_id number := null;
    case_id number := 110099;
    acct_nr_1 varchar2(35) := '9999999999';
    acct_nr_2 varchar2(35) := '9999999998';
    acct_nr_3 varchar2(35) := '9999999997';
    acct_nr_4 varchar2(35) := '9999999996';
    acct_nr_5 varchar2(35) := '9999999995';

    pkg.acct_insert(v_id, null, case_id, acct_nr_1, 'SYSTEM', 'SYSTEM', null);
    pkg.acct_insert(v_id, null, case_id, acct_nr_2, 'SYSTEM', 'SYSTEM', null);
    pkg.acct_insert(v_id, null, case_id, acct_nr_3, 'SYSTEM', 'SYSTEM', null);
    pkg.acct_insert(v_id, null, case_id, acct_nr_4, 'SYSTEM', 'SYSTEM', null);
    pkg.acct_insert(v_id, null, case_id, acct_nr_5, 'SYSTEM', 'SYSTEM', null);
    The example above will only insert acct_nr_5 into the other tables.
    why is that, and can i do anything about it?

    What i ideally would like is to use only 1 variable, and then assign the value to it before the procedure is called the 2nd time, 3rd etc. I tried this as well, but it will still only insert the last row, then next time i run procedure 2, it will insert the 2nd to last row etc.

    the procedure calling the acct_insert procedure is picking values from various fields, ie "if col_1 has value, call acct_insert with the value from col_1", and then further down it checks the same way if col_2 has a value etc.

    Im running Oracle 9 if that matters.

    Any input on how this can be done?

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    It would appear that there is some logic in pkg.acct_insert that causes only 1 row to be inserted - or perhaps rolls back previous changes before inserting the next row? Either that or the logic in the calling program is not as shown in your example. Can you post the code of pkg.acct_insert - or if it's big, a cut-down version that demonstrates the problem?

  3. #3
    Join Date
    Aug 2011
    - or perhaps rolls back previous changes before inserting the next row?
    This was it - kindof.
    The first variable, v_id, will genereate a new id if it is null, but in the procedure pkg.acct_insert this id was not reset before the procedure was looped through with the next set of data, so it would kick in on the duplicate check for the other 4 rows. Gotta love rookiemistakes

    Thanks for your help, it seems a change of perspective was all I needed

Posting Permissions

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