Stored Procedure Pl/SQL

[Civic06]

how to write a stored procedure in which a temporary table is created and inserts are made into that table.

[Vimalsoft Pty Ltd]

CREATE PROC prc_insert_example
AS

SELECT * INTO #TEMP
FROM MYTABLE

OR

CREATE PROC prc_insert_example
AS

CREATE TABLE #My_Temp
(
Age int,
FName varchar(15)
)

insert into #My_Temp
select Age,FName from MYTABLE

Do you understand ?

Vuyiswa Maseko, Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers." C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.somee.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Civic06]

THis syntax is not correct for a oracle stored procedure unless i am doing something wrong

[Vimalsoft Pty Ltd]

I thought you wanted a SQl example , but am worried because you posted here without using google http://www.google.co.za/search?hl=en&q=create+a+temp+table+in+oracle&meta=&aq=1&oq=Create+a+temp+table+in+[^] Please don't do this again, its against the Forums policy. i forgive you for that. :)

Vuyiswa Maseko, Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers." C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.somee.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Civic06]

I tried it but it doesn't seem to work. Also, I just read that in oracle stored procedures you shouldn't create the temp table in the procedure it should exist and then you may use it in the procedure. but how can it exist in advance.

[David Skelly]

Oracle (PL-SQL) doesn't have temporary tables in the same way as SQL Server (T-SQL). Read up about Oracle Global Temporary Tables. These work in a different way from SQL Server #temp tables, but give you the same end result.