0

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? :)

mahi_0707
  • 1,030
  • 9
  • 17
thekucays
  • 568
  • 3
  • 9
  • 32
  • 1
    Looking at `proc_insert_relation_lpd` there's nothing I can see which would put anything into `arraynya` prior to `arraynya` being using the the `for` statement. Were you expecting something else? Perhaps instead of using `arraynya.count` you meant to use `arraynya.limit`? – Bob Jarvis - Слава Україні May 30 '15 at 19:43
  • well, before the "begin" statement, i want to create empty varray "arraynya" from arr_id_p_dtl, which is a 13 length varray, and then fill every index inside "arraynya" with the value returned from proc_insertpermissiondtl on the for loop... is that the correct way? – thekucays May 30 '15 at 20:43
  • As i said, I think you want to use `arraynya.limit` instead of `arraynya.count` in your `while` loop. Best of luck. – Bob Jarvis - Слава Україні May 30 '15 at 21:58
  • Oh, how stupid I am.. Thanks a lot, I got it now.. I thought ".count" is similar to ".length" or something like that.. :D – thekucays May 31 '15 at 02:54
  • See [PL/SQL Reference: Using Collection Methods](http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#LNPLS00508) for documentation of the available collection methods. – Bob Jarvis - Слава Україні May 31 '15 at 11:54

1 Answers1

2

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.
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Hi @JustinCave unfortunately i still got a little problem here.. Why do i have to fill the varray first before i use it? In this case i have to type `arraynya arr_id_p_dtl := arr_id_p_dtl(0,0,0,0,0,0,0,0,0,0,0,0,0);` instead of `arraynya arr_id_p_dtl := arr_id_p_dtl();` Otherwise, it will throw "beyond count" error :(
    That will be awful if i have a 100 length of varray right?     – thekucays May 31 '15 at 08:43
  • @thekucays - Not sure I understand your question. I'm hard-pressed to imagine a situation that I'd use a `varray` in the first place rather than a nested table or an associative array. If you were going to declare a 100 element `varray` for some reason and wanted to initialize every element to 0, I'd simply write the loop that I did in my answer and initialize the elements that way. – Justin Cave May 31 '15 at 20:00