This forum is currently locked. You can't register or post questions at this time. (read more)

Plsql Collection - Global Variable

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Plsql Collection - Global Variable

Postby Guru3i » Thu Nov 29, 2012 2:14 pm

Hi Tim,

What we do is ..

1) We populate tab_emp_sal_comm with Bulk collect and iterate through it in a loop.
2) Check whether the Passed deptno is available in tab_emp_sal_comm collection.
3) If its available populate one more collection called tab_emp_sal_comm_1 and push only matching records into it.
4) From the matched collection, we want to populate that to the package global collection table which is again of the same type.
5) By default for every new call the old values are overwritten, but i want to append the global variable upon every call and finally make a bulk update to corresponding table(s).
6) l_deptno will be a parameter and the values will change on every call to this procedure in real time code.

For the sake of easiness given a simulated example from EMP table. Aim is to append the Global table in the package. Because on every call the previously loaded values are overwritten. I want them to be available and the further calls should only append tthe values as next set of rows instead of over writing.

Code: Select all


CREATE OR REPLACE PACKAGE employees_pkg
IS
  type rec_sal_comm is record(esal emp.sal%type, ecomm emp.comm%type,edeptno emp.deptno%type);
  type at_emp_sal_comm is table of rec_sal_comm index by pls_integer;
  pkg_tab_emp  at_emp_sal_comm;
  pkg_tab_emp_1 at_emp_sal_comm;
END;
/

declare
  -- Local variables here
  type emp_sal_comm is record(
    esal    emp.sal%type,
    ecomm   emp.comm%type,
    edeptno emp.deptno%type);
  type at_emp_sal_comm is table of emp_sal_comm index by pls_integer;
  tab_emp_sal_comm  at_emp_sal_comm;
  tab_emp_sal_comm1 at_emp_sal_comm;
  l_deptno          dept.deptno%type := 30;
  l_comm            number(7, 2) := 0;
  M_CNTR            NUMBER(7, 2) := 0;
begin

  select sal, comm, deptno bulk collect into tab_emp_sal_comm from emp;
  for indx in 1 .. tab_emp_sal_comm.count loop
    if tab_emp_sal_comm(indx).edeptno = l_deptno then
      tab_emp_sal_comm1(indx).ecomm := tab_emp_sal_comm(indx).ecomm * 0.5;
      tab_emp_sal_comm1(indx).esal  := tab_emp_sal_comm(indx).esal * 0.75;
    end if;
  end loop;
  dbms_output.put_line(tab_emp_sal_comm1.count);
  dbms_output.put_line('**');

  m_cntr := tab_emp_sal_comm1.FIRST;
  loop
    exit when M_CNTR is null;
--    dbms_output.put_line(M_CNTR || ' ** ' ||nvl(tab_emp_sal_comm1(M_CNTR).ecomm, 0));
    employees_pkg.pkg_tab_emp(m_cntr).ecomm := tab_emp_sal_comm1(M_CNTR)
                                                    .ecomm;
    employees_pkg.pkg_tab_emp(m_cntr).edeptno := tab_emp_sal_comm1(M_CNTR)
                                                      .edeptno;
    employees_pkg.pkg_tab_emp(m_cntr).esal := tab_emp_sal_comm1(M_CNTR).esal;
    m_cntr := tab_emp_sal_comm1.next(m_cntr);
  end loop;

  employees_pkg.pkg_tab_emp_1 := employees_pkg.pkg_tab_emp;
 -- dbms_output.put_line('**');
--  dbms_output.put_line(employees_pkg.pkg_tab_emp_1.count);
end;


Thanks in advance

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Plsql Collection - Global Variable

Postby Tim... » Thu Nov 29, 2012 5:57 pm

Hi.

You can append the values from one collection to an existing collection using the MULTISET UNION command. You can see an example here.

Code: Select all

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION l_tab2;
 
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/


See: http://www.oracle-base.com/articles/8i/ ... operations

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: Plsql Collection - Global Variable

Postby Guru3i » Fri Nov 30, 2012 2:46 pm

Thanks Tim for your time. What i wanted is some thing like whats shown below. For every execution of the block below with different values for l_deptno variable, the package_global collection i.e. table of records gets appended. You can check the count of the package global collection after running the blocks. But with Multiset operator is this doable and can we achieve the same goal?

Please help with a example if you can. Thanks in advance

Code: Select all

declare
  l_deptno          dept.deptno%type := 30;
  tab_emp_sal_comm  employees_pkg.at_emp_sal_comm;
begin
   select sal, comm, deptno bulk collect into tab_emp_sal_comm from emp where deptno = l_deptno;
   for i in 1 .. tab_emp_sal_comm.count
   loop
      employees_pkg.pkg_tab_emp(employees_pkg.pkg_tab_emp.COUNT+1):=tab_emp_sal_comm(i);
   end loop;
end;
/
declare
  l_deptno          dept.deptno%type := 20;
  tab_emp_sal_comm  employees_pkg.at_emp_sal_comm;
begin
   select sal, comm, deptno bulk collect into tab_emp_sal_comm from emp where deptno = l_deptno;
   for i in 1 .. tab_emp_sal_comm.count
   loop
      employees_pkg.pkg_tab_emp(employees_pkg.pkg_tab_emp.COUNT+1):=tab_emp_sal_comm(i);
   end loop;
end;
/
exec dbms_output.put_line(employees_pkg.pkg_tab_emp.COUNT);


Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Plsql Collection - Global Variable

Postby Tim... » Fri Nov 30, 2012 3:42 pm

Hi.

You have this line:

Code: Select all

employees_pkg.pkg_tab_emp_1 := employees_pkg.pkg_tab_emp;


If that line were changed to the following, the pkg_tab_emp_1 collection would contain all the records.

Code: Select all

employees_pkg.pkg_tab_emp_1 := employees_pkg.pkg_tab_emp_1 MULTISET UNION employees_pkg.pkg_tab_emp;


Each time you call the code you are overwriting the contents of employees_pkg.pkg_tab_emp. Once complete, you overwrite the contents of employees_pkg.pkg_tab_emp_1 with the new collection, rather than appending to it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: Plsql Collection - Global Variable

Postby Guru3i » Sat Dec 01, 2012 9:49 am

Hi Tim,

I tested with what you said, its returning this error , I am not sure what i am missing here

Code: Select all

 declare
   l_deptno            dept.deptno%type := 10;
   tab_emp_sal_comm    employees_pkg.at_emp_sal_comm;
   tab_emp_sal_comm_1  employees_pkg.at_emp_sal_comm;
 begin
    select sal, comm, deptno
    bulk collect into tab_emp_sal_comm
    from emp where deptno = l_deptno; 
    for i in 1 .. tab_emp_sal_comm.count
    loop
      employees_pkg.pkg_tab_emp_2(employees_pkg.pkg_tab_emp_2.COUNT+1):=tab_emp_sal_comm(i);
    end loop; 
    employees_pkg.pkg_tab_emp_2 := employees_pkg.pkg_tab_emp_2 MULTISET UNION employees_pkg.pkg
    dbms_output.put_line(tab_emp_sal_comm_1.count);
 end;

ERROR at line 13:
ORA-06550: line 13, column 36:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_ALL'
ORA-06550: line 13, column 36:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_ALL'
ORA-06550: line 13, column 5:
PL/SQL: Statement ignored


Thanks in advance

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Plsql Collection - Global Variable

Postby Tim... » Sat Dec 01, 2012 12:09 pm

Hi.

Oh. OK. You are using an index-by-table (associative array), so this is not appropriate for MULTISET UNION. In that case, you will have to do the same thing manually, by looping through the first collection, adding the elements to the second collection.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: Plsql Collection - Global Variable

Postby Guru3i » Sun Dec 02, 2012 6:30 am

Hi Tim,
Thanks for clarifications. So the example given by you was for a nested table am i right. Also is my approach right for index by table.
Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Plsql Collection - Global Variable

Postby Tim... » Sun Dec 02, 2012 8:34 am

Hi.

Yes. MULTISET UNION is for nested tables or varrays.

Your approach is fine, provided you manually extend the second collection.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: Plsql Collection - Global Variable

Postby Guru3i » Sun Dec 02, 2012 10:06 am

Hi Tim,

yes I do manually extend the collection index by way of this line

Code: Select all

employees_pkg.pkg_tab_emp_2(employees_pkg.pkg_tab_emp_2.COUNT+1):=tab_emp_sal_comm(i)


Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Plsql Collection - Global Variable

Postby Tim... » Sun Dec 02, 2012 12:05 pm

OK

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 7 guests

cron