loginboy
asked on
Best way to populate smaller collections from one huge collection?
Best way to populate smaller collections (based on underlying tables) from one huge collection (based on View)?
1) Real time facts: Data volume is decent, 10K rows avg (may be 100K in 1 year), 1 view - 4 underlying tables
2) Is Associatve arrays best or nested table???
CODE BELOW:
type my_emp_tbl IS TABLE OF emp%ROWTYPE index by pls_integer;
type my_dept_tbl IS TABLE OF dept%ROWTYPE index by pls_integer;
type my_loc_tbl IS TABLE OF loc%ROWTYPE index by pls_integer;
CREATE OR REPLACE emp_dept_loc_pkg IS
-- BASED ON VIEW
type my_emp_dept_loc_tbl IS TABLE OF my_emp_dept_loc_view%ROWTY PE INDEX BY pls_integer;
emp_dept_loc_procedure (in_emp_dept_loc_tbl IN my_emp_dept_loc_tbl);
END emp_dept_loc_pkg;
CREATE OR REPLACE emp_dept_loc_pkg BODY IS
PROCEDURE emp_dept_loc_procedure (in_emp_dept_loc_tbl IN my_emp_dept_loc_tbl) IS
BEGIN
1) Process each record from the input (in_emp_dept_loc_tbl) collection and populate each of the
other (decalared in separate pacakges based on underlying tables of this view) collections EMP, DEPT, LOC????
END emp_dept_loc_procedure;
END emp_dept_loc_pkg;
1) Real time facts: Data volume is decent, 10K rows avg (may be 100K in 1 year), 1 view - 4 underlying tables
2) Is Associatve arrays best or nested table???
CODE BELOW:
type my_emp_tbl IS TABLE OF emp%ROWTYPE index by pls_integer;
type my_dept_tbl IS TABLE OF dept%ROWTYPE index by pls_integer;
type my_loc_tbl IS TABLE OF loc%ROWTYPE index by pls_integer;
CREATE OR REPLACE emp_dept_loc_pkg IS
-- BASED ON VIEW
type my_emp_dept_loc_tbl IS TABLE OF my_emp_dept_loc_view%ROWTY
emp_dept_loc_procedure (in_emp_dept_loc_tbl IN my_emp_dept_loc_tbl);
END emp_dept_loc_pkg;
CREATE OR REPLACE emp_dept_loc_pkg BODY IS
PROCEDURE emp_dept_loc_procedure (in_emp_dept_loc_tbl IN my_emp_dept_loc_tbl) IS
BEGIN
1) Process each record from the input (in_emp_dept_loc_tbl) collection and populate each of the
other (decalared in separate pacakges based on underlying tables of this view) collections EMP, DEPT, LOC????
END emp_dept_loc_procedure;
END emp_dept_loc_pkg;
ASKER
Hi slightwv,
Thanks. Technically it's not fromt he view from the input collection has structure of view.
the frontend technology will pass that huge colelction as input. So can't go with direct from view to table. Need to use collections.
Please advice.
Thanks. Technically it's not fromt he view from the input collection has structure of view.
the frontend technology will pass that huge colelction as input. So can't go with direct from view to table. Need to use collections.
Please advice.
So where does the view come into play?
Will you be loading the 'collection' into the view? parsing it and loading it into the base tables? what?
If you can drive what data type the application passes in, have you considered passing in a CLOB as XML and loading tables from the XML?
I would probably go the XML route but the final decision will be based on your own specific situation and likely require individual testing. No two systems are the same so no single solution works best across systems.
As I mentioned in the question you just closed: Any final decision will have to be based on your individual testing.
Will you be loading the 'collection' into the view? parsing it and loading it into the base tables? what?
If you can drive what data type the application passes in, have you considered passing in a CLOB as XML and loading tables from the XML?
I would probably go the XML route but the final decision will be based on your own specific situation and likely require individual testing. No two systems are the same so no single solution works best across systems.
As I mentioned in the question you just closed: Any final decision will have to be based on your individual testing.
ASKER
In case DB nested table object (though it won't be used as a column in any physical table) are created, is it possible to read from this DB nested table object in PLSQL code? If so How? what would the syntax be like?
I would need to know more about your system and objects. Even then, it still comes down to you needing to test variations on your specific system.
As for specific code examples that could not be found with an Internet search, we would need a test case that included objects, sample data and expected results.
That way, we could set it up on our test systems to make sure everything works the way you want it to work.
As for specific code examples that could not be found with an Internet search, we would need a test case that included objects, sample data and expected results.
That way, we could set it up on our test systems to make sure everything works the way you want it to work.
ASKER
Here are the test objects, sample data, expected results....
Questions below in comments, Please help
create or replace type emp_obj_type as object
( ID number,
NAME varchar2(100),
ROLE varchar2(100),
static function initialize return emp_obj_type
);
/
create or replace type body emp_obj_type as
static function initialize return emp_obj_type is
begin
return(emp_obj_type(null,
null,
null));
end initialize;
end;
/
create or replace type emp_obj_type_tbl IS TABLE OF emp_obj_type;
SET SERVEROUTPUT ON
declare
v_emp_obj_rec emp_obj_type := emp_obj_type.initialize();
v_emp_obj_tbl emp_obj_type_tbl := emp_obj_type_tbl();
begin
for i in 1..3
loop
v_emp_obj_rec.ID := i;
v_emp_obj_rec.NAME := 'ABC - ' || i;
v_emp_obj_rec.ROLE := 'ROLE of ABC - ' || i;
v_emp_obj_tbl.extend();
v_emp_obj_tbl(v_emp_obj_tb l.last) := v_emp_obj_rec;
end loop;
dbms_output.put_line('Coun t of nested table - '||v_emp_obj_tbl.count());
/*
1) How to I read (right here) the data from this nested table object?
2) Don''t want to use Loops
3) Can I do direct SELECT?
4) CAST, COLLECT, MULTISET? Keywords will be helpful?
*/
end;
/
Questions below in comments, Please help
create or replace type emp_obj_type as object
( ID number,
NAME varchar2(100),
ROLE varchar2(100),
static function initialize return emp_obj_type
);
/
create or replace type body emp_obj_type as
static function initialize return emp_obj_type is
begin
return(emp_obj_type(null,
null,
null));
end initialize;
end;
/
create or replace type emp_obj_type_tbl IS TABLE OF emp_obj_type;
SET SERVEROUTPUT ON
declare
v_emp_obj_rec emp_obj_type := emp_obj_type.initialize();
v_emp_obj_tbl emp_obj_type_tbl := emp_obj_type_tbl();
begin
for i in 1..3
loop
v_emp_obj_rec.ID := i;
v_emp_obj_rec.NAME := 'ABC - ' || i;
v_emp_obj_rec.ROLE := 'ROLE of ABC - ' || i;
v_emp_obj_tbl.extend();
v_emp_obj_tbl(v_emp_obj_tb
end loop;
dbms_output.put_line('Coun
/*
1) How to I read (right here) the data from this nested table object?
2) Don''t want to use Loops
3) Can I do direct SELECT?
4) CAST, COLLECT, MULTISET? Keywords will be helpful?
*/
end;
/
>>Here are the test objects, sample data, expected results....
Sorry but not even close.
I see no actual data and no desired results.
What I'm looking for is:
given:
create table tab1(col1 char(1));
--with data, the 'sample data'
insert into tab1 values('a');
insert into tab1 values('1');
commit;
I need:
-- the expected results: Anything that is a number.
col1
------
1
I need to be able to create my own objects from what you provide so I can produce the results you post as expected.
What is better "A" or "B" isn't a good question because of what I posted above: Every database/system is different. What is 'best' for one may not be 'best' or others.
Only by trial and error can you make that decision. We can 'suggest' but only once we can test...
Sorry but not even close.
I see no actual data and no desired results.
What I'm looking for is:
given:
create table tab1(col1 char(1));
--with data, the 'sample data'
insert into tab1 values('a');
insert into tab1 values('1');
commit;
I need:
-- the expected results: Anything that is a number.
col1
------
1
I need to be able to create my own objects from what you provide so I can produce the results you post as expected.
What is better "A" or "B" isn't a good question because of what I posted above: Every database/system is different. What is 'best' for one may not be 'best' or others.
Only by trial and error can you make that decision. We can 'suggest' but only once we can test...
Here is what I understand:
You have some application written in some language that will pass some data in some format to your database.
You want to know what the 'best' way to take that data and do something with it.
Now do you see where we need more information?
You have some application written in some language that will pass some data in some format to your database.
You want to know what the 'best' way to take that data and do something with it.
Now do you see where we need more information?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found solution outside of EE
Have you tested them versus just selecting from the view and processing without storing the results in memory as a collection?
As far as your question goes, I would personally set up both methods as as tests and, well, test them both against my database/server setup.