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

"Kin Ng" <kin_ng5_at_yahoo.com> wrote in message news:d5b3f600.0307210859.67ef65c2_at_posting.google.com...
> Daniel,
>
> Sorry I didn't mention it but your 2nd and 3rd options revolved around
> dyanmic sql which frankly didn't address the main issue.

Yes it did see below

> Here is what
> I think Oracle is lacking in the temp table features of SQL Server:
> dynamic table creation with dynamic columns and types AND AND AND
> returns data with this newly created table AND AND this table will be
> deleted when the session ends. This is really an old IBM concept
> where each session has it's own temp area that you can create whatever
> you want and the SYSTEM will clean up your mess at the end of the
> session (IBM's job). If I put this dynamic thing in the SP, I expect
> the table can be created with different columns each time it is
> called.

You can do this with dynamic sql. build a statement in your SP and then include

execute immediate '<your sql>'; in the SP. <your sql> is not known till runtime so it is truly dynamic.

> Or may be I am completely wrong in Dynamic SQL (btw, I am using .NET's
> C# to call Oracle so most of the calls are already dynamic SQL) or
> DBMS_SQL but from my past (I have to say limited) experience plus
> newly reviewed DBMS_SQL doc I coudn't see how you can do that. Would
> you mind giving a pesudo code on how to do that?

Ah we seem to be talking about two different things. I *think* Daniel has in mind something like

create or replace procedure sp_daft(no_cols in number, dtype in varchar2) as
l_strSQL varchar2(32767);
begin
l_strSQL := 'create table mytemp (';
for i in 1..no_cols-1 loop
l_strSQL := l_strSQL||' col'||i||' dtype, '; end loop;
l_strSQL := l_strSQL||' col'||no_cols||' dtype'; l_strSQL := l_strSQL||' tablespace users'; execute immediate l_strSQL;
end;
/

which if one has the appropriate rights can create a table with no_cols columns of dtype datatype.

As you can probably tell by my naming convention, ISTM that creating an object of wildly different characteristics on the fly may be something that an RDBMS is capable if but it is very very silly indeed. Of course if one isn't interested in design, scalability and data integrity one can alwas do this, but then other technologies offer the ability to do this at considerably less than 40k a processor.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************