Re: Can ADO to Oracle do this?

LOK Kok Wah wrote:

> -newbie to oracle, looking to do something similar in oracle
>
> in mssql I can write a stored procedure to do the following
>
> create procedure
> create table #temptbl (a varchar(20))
>
> insert #temptbl ........ values (whole list)
> select * from #temptbl
>
> from ado, I can define a recordset oobject and run the SP and the results
> of the #temptbl will be in the recordset
>
> 1. can I have such temp tables in oracle? which version would support this?
> 2. in oracle stored procedure, i do not seem to be able to a select without
> putting the results into a table i.e. I cannot do select * from table -
> it expects the results to go into a table
>
> I'm trying to avoid creating a permanent table and if oracle can do the same
> would be excellent
> cheers!
>
> mailto:lokkokwah_at_yahoo.com

You can do anything in Oracle you are used to doing. That said best practice is to not do so.

To try to kludge Oracle into being a Microsoft product, or kludge SQL Server into being Oracle is a bad way to approach any problem. In your case, while Oracle has temp tables and while you can create table's on the fly ... temp tables are hardly ever used because the difference in architecture makes them essentially unnecessary. And creating tables on-the-fly is about as efficient a means of killing performance and scalability as I can imagine. Time to unlearn bad habits, and/or product specific habits, and do Oracle the way Oracle is supposed to be done.

With Oracle you want to use SELECT ... INTO ... FROM ... WHERE ... and in your case most likely the syntax

OPEN ref_cursor FOR
SELECT ... Daniel Morgan Received on Mon Feb 24 2003 - 17:50:21 CET