Re: Using pipelined functions

The scenario is this ... we do a lot of searching on our website that is data related. Pulling in single records and based on the results performing further searches. Currently this is done by querying another database (mySQL),and the results are compiled on the Web Servers. The new approach i'm trying to implement based on discussions with my directors, and web developers is to remove the majority of the logic for the searches from the web site functions. So that we could just get mokeys to do further development on the site should we need to without them having to deal with the masses of underlying tables, also the code that interacts with the databases will then reside on a 'single' machine. So changes are done in one place. At the moment we've to FTP to as many as 10 servers when changes are made and In my opinion this would no longer be necessary if we implemented the searches as stored functions in the database. I am aware of the fact that i'm a newbie really when dealing with oracle, but i've experience using other databases and have become aware of the differences between these and the oracle database.

Simply put I and the web developers are in agreement, that calling a single stored function within a package and getting the results we need is better than going between the servers 4 or 5 times to retrieve data. It is of my opinion that using packages and returning a table type as per requirements would be the best way to achieve this. Simply because should we be at a stage where it is 'real world ready' any query performance tuning could be done at database level leaving web developers to do the web development and the 'performance junkies' to tweak queries in the stored procedures.

I've redesigned the code and have managed to bring it down to around 500 lines in total. Which means we now have replaced in excess of 2000 lines on our website with a 500 line package, which returns thesame data. Thanks to everyone for their input. Specially, DA Morgan who forced me to rethink my strategy. Received on Tue Jan 11 2005 - 02:52:19 CST