Re: Select-Into

Thanks for your reply.

I have some more problems with the 2 solutions below.... Before I discuss the problems, some background on why I looking for 'temp. tables'. I am making a front-end application. The application takes any table in the database and copies the rows in existing tables with new primary Key.
Consider a table below:

    CaseId UserId UserName

    1            100            Inder
    1            101            Lisa
    1            101            Mike

Primary Key is CaseId and UserId. I need to make copy of ALL 3 rows and add 3 new rows with CaseId=2. So the new table looks like:

    CaseId UserId UserName

    1            100            Inder
    1            101            Lisa
    1            101            Mike
    2            100            Inder
    2            101            Lisa
    2            101            Mike

Since it has to a "Generic" scheme, I would not know Column names other than the name-"CaseId".
In Sybase it would have been possible by writing the following code:

1. Select * into #tmpTable from <tblName> where CaseId=1
2. Update #tmpTable set CaseId=2
3. Insert into <tblName> select * from #tmpTable
4. drop table #tmpTable

Now the problems:
Solution 1:

        (create table....)

1. This requires all the users to have create table rights.
2. If a user is logged in at more than 1 workstation, we have problems, since the table is "NOT really" temporary. Its just that no body else has permissions to it. So at 2 workstations logged under same id might clash for the "table name".

Solution 2:

    (Stored procedure)
    This seems to be creating a "REAL temporary table". But with the limited knowledge I have on ORACLE, I guess that DECLARE TABLE requires table definition similar to CREATE TABLE. Hence it is not a "generic" solution.

Is there anything I can do to help myself??

Thanks again in taking time to solve this problem. I really appreciate it.

Inder

>
>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 Tue Aug 17 1999 - 11:17:19 CDT