Home » SQL & PL/SQL » SQL & PL/SQL » table type indexed by binary integer
table type indexed by binary integer [message #629938] Wed, 17 December 2014 23:39 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,
Can you please explain this? in 1,2 code
Declare
    -- which is a table of type Number indexed by binary integers.
   x_pck_detail_tab     wsh_util_core.id_tab_type;
Begin
 for c_rec in c1 loop
   .......
  for c_detail_rec in c2 loop
   i := i + 1;
   x_pck_detail_tab(i) := c_detail_rec.delivery_detail_id; ----->(1)
  END LOOP;
   package.procedure(x_pck_detail_tab);
   x_pck_detail_tab.delete;  --->(2)
    commit;
END LOOP; --Main Loop


Thanks
Re: table type indexed by binary integer [message #629939 is a reply to message #629938] Wed, 17 December 2014 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
package has been delivered so it can be deleted.
Re: table type indexed by binary integer [message #629942 is a reply to message #629939] Thu, 18 December 2014 01:20 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

create or replace 
PACKAGE test_pkg 
AS 
   
TYPE empno_tab IS TABLE OF emp.empno%TYPE INDEX BY binary_integer;

PROCEDURE test_tab;

END;

create or replace 
PACKAGE BODY test_pkg 
AS 
PROCEDURE Test_tab 
IS 
  -- which is a table of type Number indexed by binary integers. 
  --   x_pck_detail_tab     wsh_util_core.id_tab_type; 
  i           NUMBER := 0; 
  t_empno_tab EMPNO_TAB; 
  CURSOR c1 IS 
    SELECT * 
    FROM   dept; 
  CURSOR c2 IS 
    SELECT * 
    FROM   emp; 
BEGIN 
--    FOR c_rec IN c1 LOOP 
        FOR c_detail_rec IN c2 LOOP 
            i := i + 1; 

            T_empno_tab(i) := c_detail_rec.empno; ----->(1) --stored the results to a associative array

            dbms_output.Put_line('xx'||T_empno_tab(i)); 
        END LOOP; 

        --   package.procedure(x_pck_detail_tab); 
        t_empno_tab.DELETE; --->(2) --entire associative array deleted
        FOR j IN 1 .. t_empno_tab.count LOOP 
         dbms_output.Put_line('inside table'); 
            dbms_output.Put_line('yy'||T_empno_tab(j)); 
        END LOOP; 

        COMMIT; 
--    END LOOP; --Main Loop 
END; 
Re: table type indexed by binary integer [message #629947 is a reply to message #629942] Thu, 18 December 2014 01:42 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks,

Suppose if i get empno's are 1234,222,333,4444 so on , next the below code deletes all the empno's right? or delete one by one? please explain
t_empno_tab.DELETE; --->(2) --entire associative array deleted

Re: table type indexed by binary integer [message #629949 is a reply to message #629947] Thu, 18 December 2014 01:45 Go to previous message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Deletes entire table, if you want to delete specifically then follow this page collection methods.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collection_method.htm

[Updated on: Thu, 18 December 2014 01:46]

Report message to a moderator

Previous Topic: ORA-00907 : Formatting Error
Next Topic: Issue with DBMS_LOB.SubStr
Goto Forum:
  


Current Time: Fri Mar 29 08:24:29 CDT 2024