Re: Need SQL Server Temp Table equivalent (challenge!)

"Kin Ng" <kin_ng5_at_yahoo.com> wrote in message news:d5b3f600.0307140820.3e61c801_at_posting.google.com...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:<3f126380$0$15030$ed9e5944_at_reading.news.pipex.net>...
> > "Kin Ng" <kin_ng5_at_yahoo.com> wrote in message
> > news:d5b3f600.0307121142.3465010_at_posting.google.com...
> > > I have a need to create a temp table for a session only. The temp
> > > table's structure is not fixed. Thus the current Oracle temp session
> > > table won't work.
> >
> > Well you can create tables using dynamic SQL but if you don't intend to
> > store this data in permanent tables, then I would have thought that an
array
> > (PL/SQL table ) would do you fine. If you do intend to permanently store
the
> > data (and if you don't why are you using an RDBMS) then make sure you
get an
> > appropriate ER diagram done and the appropriate structure should fall
out.
>
> "Well you can create tables using dynamic SQL " This is not allowed as
> we did follow the recommendation of our good data modeler. i.e. We
> have a good ER diagram and it didn't solve problems like that.
>
> Can you give an example of how an Array can be returned to the users
> via a view?

I wouldn't be using a view, I'd be using PL/SQL and as Daniel says you return resultsets from PL/SQL by way of a ref cursor.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK