Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Select-Into

Re: Select-Into

From: Joao Prates <jmmp_at_esoterica.pt>
Date: Thu, 19 Aug 1999 01:14:35 +0100
Message-ID: <935025398.857997@orodruin.esoterica.pt>


Hi,

I've developed in Transact-SQL (sybase version of PL/SQL) for about 2 and half years.

Before that I had developed in Oracle's PL/SQL for about 3 years, and now after my work's end with Sybase I'm back with Oracle for some more years. I'm always responsible for the server side of the projects I'm in, so I guess I've some experience in it.

This goes to tell you that for my experience with both DBMS I've no doubt that Oracle is much more powerful, from the optimiser to the logical structures and even extended SQL (PL/SQL vs Transact SQL).

However, even Aquiles had his week point. I'm sorry to say (hurrah to Sybase) that in Oracle you have no such thing as a temp table. In that respect, Sybase is much better. It is the most important thing I can remember about Sybase that I miss in Oracle.

So I guess you must start searching for other ideas, such as temporary (but real) tables with some primary key that allows you to distinguish from one session to another.

Sorry to bring you this "bad news",

Joao Prates

Inder Singh <isingh_at_utilinc.com> wrote in message news:#OWGuuM6#GA.356_at_cpmsnbbsa02...
> 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:
> a) Select * into #tmpTable from <tblName> where CaseId=1
> b) Update #tmpTable set CaseId=2
> c) Insert into <tblName> select * from #tmpTable
> d) drop table #tmpTable
>
> Now the problems:
> Solution 1:
> (create table....)
> a) This requires all the users to have create table rights.
> b) 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 Wed Aug 18 1999 - 19:14:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US