PL/SQL Bulk Collection Insert syntax

Question

I am tasked with inserting a new transaction_code into a table which contains a min_cost and max_cost from the same table. What I am trying to do is take the min_cost from counties with a transaction code of 2 and the max_cost from counties with a transaction_code of 4. Here is what I am so far.

I wrote the errors that I am getting as comments next to the line in which I am getting them. I also tried using the arrays as tables, I didnt think it would work but I left them in there so you would understand what I attemped (they are commented out).

declare 
    type cost_min_array IS TABLE OF court_cost.cost_range_min%type INDEX BY BINARY_INTEGER;
    type cost_max_array IS TABLE OF court_cost.cost_range_max%type INDEX BY BINARY_INTEGER;
    v_min_cost cost_min_array;
    v_min_cost cost_max_array;
BEGIN
  begin
    select cost_range_min -- SQL STATEMENT IGNORED
    bulk collect into v_min_cost
    from court_cost
    where state = 'IL'
    and transaction_code = 4
    and end_date is null
    order by county
  EXCEPTION WHEN OTHERS THEN --SQL COMMAND NOT PROPERLY ENDED
    v_min_cost.delete;
  END;

  BEGIN
    select cost_range_max -- SQL STATEMENT IGNORED
    bulk collect into v_max_cost
    from court_cost
    where state = 'IL'
    and transaction_code = 2
    and end_date is null
    order by county
  EXCEPTION WHEN OTHERS THEN -- SQL COMMAND NOT PROPERLY ENDED
    v_max_cost.delete;
  END;

  for indx in 1..v_min_cost.count
  loop
    insert into court_cost -- SQL STATEMENT IGNORED
      (TRANSACTION_CODE,
      STATE,
      COUNTY,
      COST_RANGE_MIN,
      BEGIN_DATE,
      END_DATE,
      DATE_INSERTED,
      COURT,
      COST_RANGE_MAX)
    select lcc.TRANSACTION_CODE,
      lcc.STATE,
      lcc.COUNTY,
      v_min_cost(indx),
      lcc.BEGIN_DATE,
      lcc.END_DATE,
      lcc.DATE_INSERTED,
      lcc.COURT,
      v_max_cost(indx)
    from court_cost lcc
--      cost_min_array cmn,
--      cost_max_array cmx
  end loop; -- SQL COMMAND NOT PROPERLY ENDED
end; -- ENCOUNTERED THE SYMBOL ; WHEN EXPECTING LOOP

Any push in the right direction will be greatly appreciated, thanks.

score 1 · Answer 1 · edited Jul 25 '13 at 21:41

You have missed semicolons thrice in the code which is resulting in the errors . In code down I have added them please check is code runs now. The statement ignored is coming because semi colon is not added at the end of it and even the exception section is considered in the same statement that's why giving error.

declare 

    type cost_min_array IS TABLE OF court_cost.cost_range_min%type INDEX BY BINARY_INTEGER;
    type cost_max_array IS TABLE OF court_cost.cost_range_max%type INDEX BY BINARY_INTEGER;
    v_min_cost cost_min_array;
    v_min_cost cost_max_array;

BEGIN

  begin

    select cost_range_min -- SQL STATEMENT IGNORED
    bulk collect into v_min_cost
    from court_cost
    where state = 'IL'
    and transaction_code = 4
    and end_date is null
    order by county--semi colon added

  EXCEPTION WHEN OTHERS THEN --SQL COMMAND NOT PROPERLY ENDED
    v_min_cost.delete;

END;


 BEGIN

    select cost_range_max -- SQL STATEMENT IGNORED
    bulk collect into v_max_cost
    from court_cost
    where state = 'IL'
    and transaction_code = 2
    and end_date is null
    order by county--semi colon added
  EXCEPTION WHEN OTHERS THEN -- SQL COMMAND NOT PROPERLY ENDED
    v_max_cost.delete;
  END;

  for indx in 1..v_min_cost.count

  loop

    insert into court_cost -- SQL STATEMENT IGNORED
      (TRANSACTION_CODE,
      STATE,
      COUNTY,
      COST_RANGE_MIN,
      BEGIN_DATE,
      END_DATE,
      DATE_INSERTED,
      COURT,
      COST_RANGE_MAX)
    select lcc.TRANSACTION_CODE,
      lcc.STATE,
      lcc.COUNTY,
      v_min_cost(indx),
      lcc.BEGIN_DATE,
      lcc.END_DATE,
      lcc.DATE_INSERTED,
      lcc.COURT,
      v_max_cost(indx)
    from court_cost lcc;--semi colon added
--      cost_min_array cmn,
--      cost_max_array cmx
  end loop; -- SQL COMMAND NOT PROPERLY ENDED
end; -- ENCOUNTERED THE SYMBOL ; WHEN EXPECTING LOOP

score 0 · Accepted Answer · answered Jul 25 '13 at 20:39

You're missing semi-colons after your SQL statements. Try

declare 
    type cost_min_array IS TABLE OF court_cost.cost_range_min%type INDEX BY BINARY_INTEGER;
    type cost_max_array IS TABLE OF court_cost.cost_range_max%type INDEX BY BINARY_INTEGER;
    v_min_cost cost_min_array;
    v_min_cost cost_max_array;
BEGIN
  begin
    select cost_range_min -- SQL STATEMENT IGNORED
    bulk collect into v_min_cost
    from court_cost
    where state = 'IL'
    and transaction_code = 4
    and end_date is null
    order by county;  -- semi-colon added
  EXCEPTION WHEN OTHERS THEN --SQL COMMAND NOT PROPERLY ENDED
    v_min_cost.delete;
  END;

  BEGIN
    select cost_range_max -- SQL STATEMENT IGNORED
    bulk collect into v_max_cost
    from court_cost
    where state = 'IL'
    and transaction_code = 2
    and end_date is null
    order by county;  -- semi-colon added
  EXCEPTION WHEN OTHERS THEN -- SQL COMMAND NOT PROPERLY ENDED
    v_max_cost.delete;
  END;

  for indx in 1..v_min_cost.count
  loop
    insert into court_cost -- SQL STATEMENT IGNORED
      (TRANSACTION_CODE,
      STATE,
      COUNTY,
      COST_RANGE_MIN,
      BEGIN_DATE,
      END_DATE,
      DATE_INSERTED,
      COURT,
      COST_RANGE_MAX)
    select lcc.TRANSACTION_CODE,
      lcc.STATE,
      lcc.COUNTY,
      v_min_cost(indx),
      lcc.BEGIN_DATE,
      lcc.END_DATE,
      lcc.DATE_INSERTED,
      lcc.COURT,
      v_max_cost(indx)
    from court_cost lcc;  -- semi-colon added
--      cost_min_array cmn,
--      cost_max_array cmx
  end loop; -- SQL COMMAND NOT PROPERLY ENDED
end; -- ENCOUNTERED THE SYMBOL ; WHEN EXPECTING LOOP

and see if that improves things.

Share and enjoy.

Sorry it took awhile to respond, with these changes it just seems to run forever. I havent had any time today to look into it, but I will report back. Thank you for your help! — user2405778, Jul 26 '13 at 13:57
Looking at the code a bit it appears that you're going to get a LOT of rows inserted into COURT_COST. The `INSERT INTO COURT_COST` has a `SELECT` which is unqualified - so it's going to return every row in COURT_COST. To my eye it looks like you're going to get a complete copy of the COURT_COST table re-inserted into itself for each value in the v_min_cost array. So let's suppose COURT_COST starts with 10000 rows, and v_min_cost has 500 values in it. In that case it looks to me like you'll add 500*10000 = 5,000,000 rows in the table when the INSERT is done. — Bob Jarvis - Слава Україні, Jul 27 '13 at 00:23

PL/SQL Bulk Collection Insert syntax

2 Answers2