Home » SQL & PL/SQL » SQL & PL/SQL » Temporary tables
Temporary tables [message #258173] Fri, 10 August 2007 08:15 Go to next message
santuvssantu
Messages: 8
Registered: August 2007
Junior Member
Hi All,
I got a problem in Procedures.

Can't I create a table in Procedure and later drop that table in the same proc?

CREATE OR REPLACE PROCEDURE Temp
AS
BEGIN
CREATE TABLE tblTemp(RoomId INTEGER);
END;

When I execute this I am getting an error saying
Encountered the symbol "CREATE" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with


Thanks.
Re: Temporary tables [message #258174 is a reply to message #258173] Fri, 10 August 2007 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"execute immediate".

Of course it is a very bad to create table on the fly.
You have to use a real temporary table although it is most of the time not necessary in Oracle.

Regards
Michel

[Updated on: Fri, 10 August 2007 08:25]

Report message to a moderator

Re: Temporary tables [message #258177 is a reply to message #258173] Fri, 10 August 2007 08:37 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If you need temporary storage during a routine, check out Global Temporary Tables (GTT). They will take care of your problem and do NOT need to be created and removed during processing.

[Updated on: Fri, 10 August 2007 08:37]

Report message to a moderator

Re: Temporary tables [message #258180 is a reply to message #258177] Fri, 10 August 2007 08:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're thinking like a SQL Server developer again.
It is extremely unlikely that you need these tables.

I believe that in SQL Server you use temporary tables to hold the intermediate stages of complex queries - is this right?

In Oracle you just go ahead and write the whole query - There is an internal proces called the Cost Based Optimiser that works out the best access paths for the various tables involved.

If you can give us some details of the query that you're trying to write, then we can probably point you in the right direction.
Re: Temporary tables [message #258184 is a reply to message #258177] Fri, 10 August 2007 09:06 Go to previous messageGo to next message
santuvssantu
Messages: 8
Registered: August 2007
Junior Member
Hi,
I got it using execute immediate.....but if you can tell me any other way then I will be very thankful to u guys...


CREATE OR REPLACE PROCEDURE StoredProc1
(
intMinCost INTEGER,
intMaxCost INTEGER,
intRooms INTEGER,
intPpl INTEGER,
intRoomType INTEGER,
vcharFromDate VARCHAR2,
vcharToDate VARCHAR2)
AS

RwCount INTEGER;
BEGIN
execute immediate'
CREATE TABLE #tblRoomspecList(
RoomId INTEGER
);

INSERT INTO #tblRoomspecList (RoomId)

SELECT roomid FROM tblHotel1 WHERE COST BETWEEN intMinCost AND intMaxCost AND rooms = intRooms AND people>=intPpl AND roomtype=intRoomType;


DROP TABLE #tblRoomspecList;

END;

thanks and regards,
santu.
Re: Temporary tables [message #258190 is a reply to message #258184] Fri, 10 August 2007 09:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Right - that's not the code you're running.
That piece of code will error with something like
PLS-00428: an INTO clause is expected in this SELECT statement
at the point you try to compile it onto the database
Actually you get an
ORA-00942: table or view does not exist
- SQL errors due to missing objects take precedence of pl/sql coding errors.

Running it would also give you an ORA-00911 - Invalid character error when you try to create the table - table names are a-z, 0-9 and _, just like variable names.

It will also fail to compile as the compile requires all tables referenced in the code to exist at compile time, and you are trying to create the table on the fly - all the SQL referencing that Temp table will need to be in Execute Immediate statements too.

However, once we've got past these problems, all you are doing is creating a table, inserting some data into it, and then dropping the table - do you not intend to use this data for anything?

[edited to correct the error to be raised]

[Updated on: Fri, 10 August 2007 09:40]

Report message to a moderator

Re: Temporary tables [message #258206 is a reply to message #258184] Fri, 10 August 2007 10:11 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're trying to translate T-SQL to PL/SQL word by word.
It is a very bad idea.
It is like translating a book picking each word from a dictionary.
You see what I mean?
Both database work differently, you'll face big troubles going on like you started.

Regards
Michel
Previous Topic: Create Procedure
Next Topic: How to find out queries which ran in last 1hr
Goto Forum:
  


Current Time: Tue Dec 06 06:31:22 CST 2016

Total time taken to generate the page: 0.15301 seconds