CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlvarray
thekucays
thekucays

Reputation: 618

Oracle Stored Procedure varray beyond count

i've created 3 stored procedures. Each of their functions are :
1. proc_insertleveluser -> insert into "leveluser" table and return the last id inserted
2. proc_insertpermissiondtl -> insert into "permission_dtl" table and return the last id inserted
3. proc_insert_relation_lpd -> insert into "lvl_permission_dtl" table with data provided from procedure 1 and 2

proc_insertleveluser

create or replace procedure proc_insertleveluser(level_desc varchar, level_ int, department_id varchar, r_id_level out int)
as
begin
  insert into leveluser (level_desc, level_, department_id) values (level_desc, level_, department_id)
  returning id_level into r_id_level;
  commit;
end;

proc_insertpermissiondtl

create or replace procedure proc_insertpermissiondtl(status_ int, entry_ int, view_ int, modify_ int, delete_ int, approval_ int, r_id_p_dtl out int)
as
begin
  insert into permission_dtl (status_, entry_, view_, modify_, delete_, approval_)
  values(status_, entry_, view_, modify_, delete_, approval_)
  returning id_p_dtl into r_id_p_dtl;
  commit;
end;

and proc_insert_relation_lpd

create or replace procedure proc_insert_relation_lpd(lu_level_desc varchar, lu_level_ int, lu_department_id varchar)
as
  r_id_level int;  /* r_ is for return */
  r_id_p_dtl int;  
  type arr_id_p_dtl is varray(13) of int;
  arraynya arr_id_p_dtl := arr_id_p_dtl();
begin
  proc_insertleveluser(lu_level_desc, lu_level_, lu_department_id, r_id_level); 
  for i in 1..arraynya.count loop  
    proc_insertpermissiondtl(0, 0, 0, 0, 0, 0, r_id_p_dtl);
    arraynya(i) := r_id_p_dtl;
  end loop;

  /* more code here to insert to "lvl_permission_dtl" table */

  commit;
end;

When i execute the code, 1 record added into "leveluser" table, but not in "permission_dtl" table. It seems like the code inside the for loop was not executed.

What causes this? is the "arraynya" variable empty so the for loop won't execute? Or is there another problem? :)

Upvotes: 0

Views: 178

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

You've created an empty array so the count will be 0 when you get to the loop. As Bob Jarvis points out, if you want to iterate through the loop 13 times, you'd want to use the limit, not the count of the array.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    type arr_id_p_dtl is varray(13) of int;
  3    arraynya arr_id_p_dtl := arr_id_p_dtl();
  4  begin
  5    dbms_output.put_line( 'Count is ' || arraynya.count );
  6    dbms_output.put_line( 'Limit is ' || arraynya.limit );
  7    --
  8    -- This doesn't do anything since the count is 0
  9    --
 10    for i in 1..arraynya.count
 11    loop
 12      dbms_output.put_line( 'Count loop i=' || i );
 13    end loop;
 14    --
 15    -- This will iterate 13 times
 16    --
 17    for i in 1..arraynya.limit
 18    loop
 19      dbms_output.put_line( 'Limit loop i=' || i );
 20    end loop;
 21* end;
 22  /
Count is 0
Limit is 13
Limit loop i=1
Limit loop i=2
Limit loop i=3
Limit loop i=4
Limit loop i=5
Limit loop i=6
Limit loop i=7
Limit loop i=8
Limit loop i=9
Limit loop i=10
Limit loop i=11
Limit loop i=12
Limit loop i=13

PL/SQL procedure successfully completed.

Upvotes: 2

Related Questions