Solved

oracle  - complex dynamic computations

Posted on 2011-09-26
16
266 Views
Last Modified: 2012-05-12
I have a very complex logic stream where I need to read in detail data, filter it, and then count, sum, or further filter based on values in certain columns of a "rules" record in another table.  This cannot be done in a single pass and since it is dynamic, I cannot use a static view.  My initial thought was to retrieve the detail data from a pipelined function so that further "selects" can be performed.

1) Is this the best approach?
2) How would I write the pipelined function?  The only examples I see do not "select" from a table.
0
Comment
Question by:tnowacoski
  • 7
  • 7
  • 2
16 Comments
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36600696
This appears to be the perfect instance for using Dyanmic SQL Statements within PL/SQL allowing you to create dynamic SQL based on query results, filters and additional criteria.

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm
0
 
LVL 47

Expert Comment

by:schwertner
ID: 36600967
If the rules are complex you can use a temporary table within a transaction and prepare the selected rows there in the first pass. After that in the transaction you can anlayze the selected rows and to use further filters.
if the filters are complex try to use PL/SQL procedure because PL/SQL as procedural language is very flexible and can implement very complex logics.
If the filters are not very complex you can use Dynamic SQL like Jacobfw proposed. SQL is not so flexible as PL/SQL.
0
 

Author Comment

by:tnowacoski
ID: 36709094
schwertner: by temporary tables do you mean
 create global temporary table tablename ...

Open in new window

These are statically defined and dynamically populated..right?
What if I do not exactly know the structure of the table until execution?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36709104
You can use dynamic DML statements to create the temporary table....
or just create a generic table with generic columns and use as needed
0
 

Author Comment

by:tnowacoski
ID: 36710904
In theory, can't I have a function that returns a data type of table that I then can select against?

Record data type:
create or replace type row_type AS OBJECT
    (site        varchar2(5), ... pcs number);

Open in new window


Table data type:
create or replace type table_type as 
	table of row_type;

Open in new window


Function to return table_type:
 FUNCTION get_detail_data(in_where IN VARCHAR2) RETURN table_type pipelined AS...

Open in new window


Procedure to call Function:
PROCEDURE get_rebates AS
  _data table_type;
.
.
.
_data := get_detail_data('where info');
select count(*) from table(_data);

Open in new window

0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36712241
0
 

Author Comment

by:tnowacoski
ID: 36712349
Jacobfw:  Sort of but in the
...anonymous block that calls the myFunction function, gets a table of results...
I would want to call a select:

example:
 
  _data table_type;
.
.
.
_data := get_detail_data('where info');
select count(*) from table(_data);

Open in new window

0
 
LVL 47

Expert Comment

by:schwertner
ID: 36712387
PL/SQL array is aray that is kept in the memory (PGA).
Temporary table is kept on the disk and can have many columns.
The temporary table is created in the live of an transaction or sesion and is deleted automatically.

You can create dynamically tables in the lifecycle of the PL/SQL program. And to drop them. Keeping data in tables has the advantage to be the native data structure for the database and to have many programatic tools and construction to process and to keep the data.

But the main thing is to keep the construction as simple as possible. complex construction are hard for processing and for performance tuning.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:tnowacoski
ID: 36948185
any examples for the dynamic tables?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36948240
0
 

Author Comment

by:tnowacoski
ID: 36948323
If I am not mistaken, that is creating a table, not a dynamic temporary table.  
The purpose of this code is to dynamically select a subsection of data from an existing table that will be used for detail information.  This sub-selection is dynamic in the sense that the "where" criteria will change from pass to pass.  This sub-selection needs to be stored in a temporary location (array, temp table, etc) to have further processing done to get various "summary" calculations done.  I am looking for a method that will be processor friendly, will clean up when done, and offer the greatest flexibility to be dynamic.
0
 

Author Comment

by:tnowacoski
ID: 36956472
Will it help if I bump up the points?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36956521
I do not believe you can bump up the points past 500.
I believe the Experts have provided enough guidance to your original request for an approach.  Without a fully developed work scope, we can't write the code for you and determine the best approach for your application, environment etc.

Maybe you could reword your question with more specifics.
0
 

Author Comment

by:tnowacoski
ID: 36956597
I just bumped the point from 250pts to 500pts.  So far, I think this thread has narrowed the option down to Temporary Tables and I am assuming that this would be the Global Temporary Table.  But from my understanding, this table is statically defined and dynamically populated...Correct?
If so, this brings me back to my earlier questions of: How would one dynamically create this table and populate it during the life of the session, how can it be dynamically truncated during the session and does anyone have sample code where a Global Temporary Table is dynamically created, populated, used within a "select" statement, and then destroyed at the end of the session?
0
 
LVL 7

Accepted Solution

by:
Jacobfw earned 500 total points
ID: 36956666
declare
   v number;
begin  
  execute immediate 'CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1  NUMBER,
    column2  NUMBER ) ON COMMIT DELETE ROWS';
  execute immediate 'insert into my_temp_table values (1,2)';
  execute immediate 'select count(*) from my_temp_table' INTO v;
  dbms_output.put_line(v);
end;
/

0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 500 total points
ID: 36956721
 execute immediate 'drop my_temp_table';

to distroy it at the end.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
null value 15 94
Queries 15 34
Export table into csv file in oracle 10 46
PL/SQL More than one element associated with another element 7 4
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now