Tag: Oracle Collections

this write up is very simple if you already followed my previous two topics. In this we shall create a ref cursor which will return group of complex Objects. so first lets have a look at our database part as below.

create or replace
package pkg_state as
procedure add_state(in_state in state_typ);
procedure get_state(in_state_id in number,out_state out state_typ);
<b>TYPE CUR_DATA IS REF CURSOR;
procedure get_all_states(out_state OUT CUR_DATA);</b>
end pkg_state;
==================================================================================
create or replace
package body pkg_state as
procedure add_state(in_state in state_typ) is
begin
insert into states(id, state) values(in_state.id,in_state);
commit;
end add_state;
procedure get_state(in_state_id in number,out_state out state_typ) is
begin
select state into out_state from states where id = in_state_id;
end get_state;
<b>procedure get_all_states(out_state out CUR_DATA) is
begin
OPEN out_state FOR
SELECT id,state
FROM states
ORDER BY id asc;
end get_all_states;</b>
end pkg_state;

As a continuation to my first part here we can discuss how can we handle complex object types from java to oracle. As an example lets take a “State Object which internally have Capital object” now this complete object should get persisted in single column of table with out joins and multiple tables. lets create database first as below.

Now its time to write the procedure implementation for the above created objects.

create or replace
package pkg_state as
procedure add_state(in_state in state_typ);
procedure get_state(in_state_id in number,out_state out state_typ);
end pkg_state;
==============================================================================
create or replace
package body pkg_state as
procedure add_state(in_state in state_typ) is
begin
insert into states(id, state) values(in_state.id,in_state);
commit;
end add_state;
procedure get_state(in_state_id in number,out_state out state_typ) is
begin
select state into out_state from states where id = in_state_id;
end get_state;
end pkg_state;

Once the database is ready now let us start coding or domain objects as we did in the part-1State.java

Note : uncomment actorDaoImpl.addActor(actorType); to insert the record and buttom three lines to delete set of records as a batch delete operation passing array of ids.
The project structure should resemble the screen as below once everything is done.

Hi All,these days i got little busy with my new assignment of work in my project finally i took some time today and thought of writing a very nice concept on Oracle Collections and Handling them in Spring framework.Oracle has given a very awesome feature called collections such as “TabType, ObjectType, VArray” etc.

Note : Observer only the type names in above code are case sensitive because oracle stores the Metadata information in capital case.
Now we have to implement the RowMapper class which will map the columns from database to our Java Domain object as below.

Note : uncomment actorDaoImpl.addActor(actorType); to insert the record and buttom three lines to delete set of records as a batch delete operation passing array of ids.
The project structure should resemble the screen as below once everything is done.

Lets see the handling of complex Object types in my next Post[Part-2]
Happy Oracle Collections and Spring 🙂