Create Stored Procedure with temp table

[SundancerKid]

I would like to create a stored procedure that
contains the following logic:

Create myproc with out parameters TempCPT4F

Create tempA1 table
Create tempA2 table
Create tempB1 table
Create tempB2 table
Create tempC1 table
Create tempC2 table
Create TempCPT4F table

---------------------------------------
-- Initial Selection with One to Many
Select Data and insert it into TempA1
--
-- Summarize TempA1 using Group BY and Order BY
Select Data from TempA1 and insert it into TempA2

---------------------------------------
-- Initial Selection with One to Many
Select Data and insert it into TempB1
--
-- Summarize TempB1 using Group BY and Order BY
Select Data from TempB1 and insert it into TempB2

---------------------------------------
-- Initial Selection with One to Many
Select Data and insert it into TempC1
--
-- Summarize TempC1 using Group BY and Order BY
Select Data from TempC1 and insert it into TempC2

---------------------------------------
Select and join TempA2, TempB2, TempC2 into TempCPT4F

Delete all temp files

--------------------------------------------------

I have done this before in MS SQL but I haven't done this in Oracle yet.

 

[SantaMufasa]

Sundancer,

As an important component of your becoming syntactically familiar with Oracle, may I suggest that you "rough out" an Oracle SQL script that does just the bare necessities:

[ul][li]Create tempA1 table[/li][li]Create tempA2 table[/li][li]Initial Selection with One to Many[/li][li]Select Data and insert it into TempA1[/li][li]Summarize TempA1 using Group BY and Order BY[/li][li]Select Data from TempA1 and insert it into TempA2[/li][/ul]I recommend this exercise with an Oracle SQL reference manual at your side. You then refine the code to your best ability, then if you have additional syntax issues/questions, then post your code, along with any error messages, then we can help you resolve your issues. (I also propose that if you post problematic code, you also post the CREATE TABLE... and INSERT... statements that will allow us to quickly replicate and test your situation/code.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SundancerKid]

Thanks for the Quick Reply Mufasa,

What I am looking for is an example of a working Stored Procedure where they join more than 2 files together and use temp tables.

Thanks,

Rick

 

[Thargy]

Sun,

I'm with Santa on this one, but for slightly different reasons.

First of all, when moving across from sql server to Oracle, it's important to realise that temporary tables (which I believe are custom and practice in sql server) are nearly always superfluous in Oracle.

Second, the narrative you provide seems to indicate that you need to perform a couple of stages of grouping and/or sorting on the primary table, and that the temporary tables do nothing more than facilitate this. I would like you to post create table statements and insert statements to enable us to replicate your problem. Then please state the desired output, and I'll bet a pound to a penny that between us, Santa and I can do it in a few lines of sql, without resorting to stored procedures.

Third, unless you are referring to files defined as external tables, or OS files accessed by means of UTL_FILE, Oracle stored procedures do not join files, so I wonder if you could clarify what you're attempting here.

Regards

T