Oracle delete value from array PL\SQL

Question

I'm new to Oracle and having an issue deleting a value from an array. What I'm trying to: If I've got an array with 1, 2, 3, 4 I want to remove 2 once the database has dealt with this array value and then move onto 3.

The code I'm using is:

type test_rec is record (temp_id number,
      pos1 number,
      pos2 number,
      pos3 number);

type test_array is table of test_rec index by binary_integer;

PROCEDURE pr_test (
parv_test IN test_array)
AS
BEGIN
FOR i in parv_test.first .. parv_test.last LOOP
    IF parv_test(i).action_type = 'I' THEN
        INSERT STATEMENT

        parv_test.delete(i);
    END IF;
END LOOP;
END pr_test ;

However, I get the error:

PLS-00363: expression 'parv_test' cannot be used as an assignment target

Can someone tell me where I'm going wrong?

Thanks a lot.

Cheers

Alex

Try changing the parameter definition: parv_test IN OUT test_array — Erich Kitzmueller
– Erich Kitzmueller, Commented Feb 22, 2017 at 11:40
copy parv_test to local parameter, you can not modify input parameter — Kamil Ibadov
– Kamil Ibadov, Commented Feb 22, 2017 at 11:44

Kamil Ibadov · Accepted Answer · 2017-02-22 11:49:43Z

3

You can not modify input parameter,

Try like this,

create or replace package body test_pkg is

   type test_rec is record(temp_id number,
                                 pos1 number,
                                 pos2 number,
                                 pos3 number,
                                 action_type varchar2(1));

   type test_array is table of test_rec index by binary_integer;

   PROCEDURE pr_test(parv_test IN test_array) AS parv_test_local test_array;
BEGIN
  parv_test_local := parv_test;
  FOR i in parv_test_local.first .. parv_test_local.last LOOP
    IF parv_test_local(i).action_type = 'I' THEN
      --INSERT STATEMENT

      parv_test_local.delete(i);
    END IF;
  END LOOP;
END pr_test;

begin
null;
end test_pkg;

answered Feb 22, 2017 at 11:49

Kamil Ibadov

7103 gold badges21 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle delete value from array PL\SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related