Re: Select-Into

You can create a table as a copy of another in several ways. Following are a couple of easy methods within PL/SQL

1. create table tmpTable as select * from srcTable; (can add tablespace and storage specs before 'as select...'
2. use the 'copy' table command

As you wanted, no one else can see your 'temporary' table until you give them permission - as is the case with any object that you create.

You will have to issue a 'drop table' command to remove the table.

Alternatively, to fetch all of the data in a table, manipulate it and discard the 'temporary table', you could use a PL/SQL table type in a stored procedure. The PL/SQL table is a temporary storage method - valid while the procedure is being executed - that will hold data and allow it to be manipulated. This would be especially useful if you are doing this action regularly.

Those are few of the ways you can achieve the functionality that you are looking for in Oracle.

• Lisa

 <BluesSax_at_Unforgettable.com> wrote:

>Oracle has temp tables, but not in the way that you describe. What you may
>actually be looking for is a view.
>
>Can you explain what it is that you are trying to do?
>
>"netnews.msn.com" wrote:
>
>> Hi...
>>
>> I am trying to do the following:
>>
>> Select * into #temp from tblSomeTable;
>>
>> Here #temp is a temporary table that I want Oracle to create once it execute
>> this command. Since I am new to oracle and not sure if Oracle has temporary
>> tables or not, Let me briefly explain what I expect.
>>
>> I would want a table #temp to be created. The table can only be seen by me
>> during the session it was created. Meaning, if I am logged into another
>> machine, I should not be able to see the table on the 2nd machine. Also
>> table should automatically be destroyed as soon as my session expires..
>>
>> The above is a valid statement in SYBASE, and I am sure Oracle is a much
>> powerful and advanced DBMS.
>>
>> Would appreciate any help.
>>
>> Thanks
>> Inder
Received on Mon Aug 16 1999 - 07:58:56 CDT