Re: How can I simplify queries with inline views?

Comments embedded.
Don wrote:
> We are writing PL/SQL code that have some huge queries with many
> inline views. These queries are used to insert data into a relational
> database from a staging area. We usually use the inline views to
> avoid making many updates to the source table - the updates occur in
> the inline view.
>

I am not sure what you're saying here. How can a SELECT (which is what you have in an inline view) update a table?

> Our problem is when you have a select statement with an in-line view,
> and this in-line view has a couple of in-lines views, and these
> in-line views .... etc. As you see it can get very hard to follow.
>
> Do you know of an easier way to do this?
>

I've seen a great many inline views that could be reduced to a simple outer join or a nested subquery ... but the developer gets used to doing inline views, so everything is solved by repeating them. Uh-huh. I've also seen a great many inline views that can't be rewritten ... and you're stuck with thos (unless you can convince someone they really don't need to see that data after all).

> We could create views and use them but I don't really want a lot of
> views around that will not be used again. Is it possible to create a
> temporary view that automatically goes away when the session dies?
> (like a temp table).
>

Well now, in PL/SQL you can dynamically build a SELECT statement as input to the 'EXECUTE IMMEDIATE' command. The next statement you dynamically build will replace the previous one. Of course, you might want to be careful with this plan ... each of those SELECT statements will be unique in the SGA, causing another hard parse (and affecting your performance). You might as well toss soft parses out the window.

> Is there a way to have a PL/SQL function return a table or view? Then
> in the FROM clause we could call the function.
>

Is there a way for a function in any programming language to return an unspecified number of arguments? You can return a %rowtype or you can fill out a PL/SQL table. But any output parameter can return only one thing (not a result set) ... even if that one thing is a pointer to an array or PL/SQL table or some such.

> Any tips would be appreciated.
>
> Don
>
Received on Fri Dec 20 2002 - 11:43:05 CST