Re: Can ADO to Oracle do this?

"LOK Kok Wah" <lokkokwah_at_yahoo.com> wrote in message news:b3da7c$6o0$1_at_reader01.singnet.com.sg...
> -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?

yes, but you almost certainly don't want to, and you'd define them globally not within an sp (i.e. just like a permanent table). Oracle 8i is I believe the release number that you were looking for and the feature is GLOBAL TEMPORARY TABLES.
> 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

go to asktom.oracle.com and search for ref cursor which is the way to return recordsets from stored procedures. 90+% of the time the equivalent oracle procedure to the dummy one your present would be a simple ref cursor that selects the required data from the base table and returns it as an output parameter to the sp.

> I'm trying to avoid creating a permanent table and if oracle can do the
same
> would be excellent
> cheers!

If you want to go one stage better and use Oracle appropriately get hold of a copy of tom kytes 'expert one on one oracle' from amazon.

--
Niall Litchfield
Oracle DBA
Audit Commission UK