Using PL/SQL code instead of writing views
416312Nov 3 2005 — edited Nov 3 2005Hello,
My organisation is currently faced with the problem of maintaining a large number of views.
Whenever there is a request from the user to get data from the database we oversee, we often have to create a new view for the purpose. This resulted in a large list of views and often than not, these views are sometimes repetitive and a large number of time is spent hard-coding the SQL.
I recently come across QNXO and realise writing packages as API to tables helped. The approach is somehow similar to OO, where components can be reused.
But the problem is most of the solutions I came across are using Index_by tables approach. I cannot adopt this method in my organisation; my users use SQL and Index_by tables cannot be used in SQL.
Using nested tables is an alternative, but that would mean creating a new collection type(of table). This isn't much better than creating a new view.
Hence, Index_by tables seems to be the way to go, but how can I use it in SQL? Or is there a way I can output the data in a table format for the users?
I believe there are developers who faced the same problem as mine. I would be interested to know how you tackle it.