Home » SQL & PL/SQL » SQL & PL/SQL » how to create a table within a procedure
icon8.gif  how to create a table within a procedure [message #295468] Tue, 22 January 2008 07:06 Go to next message
fixerus
Messages: 5
Registered: January 2008
Junior Member
Sorry for the silly question !

I want to create tables as result of a select statements!

I tried to do it within a plsql procedure but I got an error message during the compiling, PLS-00103. Could anybody help me how to do that .

the problem is basically how can I generate tables within a procedure , package or any batch etc. with giving just one parameter

thank you in advance
J


Re: how to create a table within a procedure [message #295472 is a reply to message #295468] Tue, 22 January 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sorry for the silly question !

So why post it, above all in expert forum!

Quote:
I want to create tables as result of a select statements!

This is indeed silly!

Quote:
I tried to do it within a plsql procedure but I got an error message during the compiling, PLS-00103

Why don't read PL/SQL User's Guide and Reference?

Regards
Michel
Re: how to create a table within a procedure [message #295474 is a reply to message #295468] Tue, 22 January 2008 07:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Main question is: why do you think you need to create a table based on the results of a select?

[Edit: hint: SQLServer/Sybase-like intermediate, temporary tables are NOT the way to go in Oracle!]

[Updated on: Tue, 22 January 2008 07:16]

Report message to a moderator

Re: how to create a table within a procedure [message #295482 is a reply to message #295474] Tue, 22 January 2008 07:47 Go to previous messageGo to next message
fixerus
Messages: 5
Registered: January 2008
Junior Member
I need to collect data from different tables summed for cetain period . that's why I need to create table as result of a query

J
Re: how to create a table within a procedure [message #295483 is a reply to message #295482] Tue, 22 January 2008 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the relation between:
- I need to collect data from different tables summed for cetain period
- I need to create table as result of a query

You don't create a table each time you select (collect) data.

Regards
Michel
Re: how to create a table within a procedure [message #295486 is a reply to message #295483] Tue, 22 January 2008 08:03 Go to previous messageGo to next message
fixerus
Messages: 5
Registered: January 2008
Junior Member
I am sorry that I couldn't describe my problem and really preciate your efforts to help me!

So this is one of the create table statements
This collets data from another table and on place of <2307> should by a parameter in the procedure

create table store_stock_2307_start as
select bolt,lfdartnr,sum( decode(LELT0923_ertek,null,0,lelt0923_ertek)) as LELT0923_ertek,
sum(decode(LELT0923_DB,null,0,LELT0923_DB)) as LELT0923_DB,
sum(decode(KI_DB,null,0,KI_DB)) as ki_db,
sum(decode(KI_ertek,null,0,KI_ertek)) as ki_ertek,
sum(decode(elad_DB,null,0,elad_DB)) as elad_db,
sum(decode(elad_ertek,null,0,elad_ertek)) as elad_ertek,

sum(decode(LELT0923_DB,null,0,LELT0923_DB))+ sum(decode(KI_DB,null,0,KI_DB))- sum(decode(elad_DB,null,0,elad_DB)) as stock_now
from PICKED_SOLD_20070923_
where datum <=(select gueltig_ab from bvm where lfdnr=2307)-1
group by bolt,lfdartnr

regards

J
Re: how to create a table within a procedure [message #295491 is a reply to message #295486] Tue, 22 January 2008 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you create a table?

Regards
Michel
Re: how to create a table within a procedure [message #295493 is a reply to message #295491] Tue, 22 January 2008 08:21 Go to previous messageGo to next message
fixerus
Messages: 5
Registered: January 2008
Junior Member
the date nedd to be froozen and beside that
this table is used for further queries

Re: how to create a table within a procedure [message #295499 is a reply to message #295493] Tue, 22 January 2008 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the date nedd to be froozen and beside that

Do you mean you want to permanently store the result data, for as long as your source data?

Quote:
this table is used for further queries

You could use a temporary table.

Regards
Michel

Re: how to create a table within a procedure [message #295501 is a reply to message #295468] Tue, 22 January 2008 08:38 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Still the question remains Why ?

Why not create a view or perhaps a materialized view depending on your requirements,
outside of any procedure ?

Creating tables within a procedure is very seldom the correct solution.
Re: how to create a table within a procedure [message #295504 is a reply to message #295499] Tue, 22 January 2008 08:39 Go to previous messageGo to next message
fixerus
Messages: 5
Registered: January 2008
Junior Member
1. no at all . I need this data ca. 3 month
2. To tell the true I don't know the temp tables

but I think it could be the solution . the only question is how??
Re: how to create a table within a procedure [message #295505 is a reply to message #295504] Tue, 22 January 2008 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the only question is how??

"execute immediate"

Regards
Michel

[Updated on: Tue, 22 January 2008 08:41]

Report message to a moderator

Re: how to create a table within a procedure [message #295578 is a reply to message #295493] Tue, 22 January 2008 15:41 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
fixerus wrote on Tue, 22 January 2008 15:21
this table is used for further queries

I guess you have / will have set of reports / queries which will be executed against these tables you'll create once you figure it out.

If I understood you correctly, these tables will be named as 'store_stock_2307_start', 'store_stock_6542_start', 'store_stock_99734_start' etc.

How do you plan to incorporate change of orange parts into bunch of queries / reports you write? Will you perform "search and replace" each time you create a new table?

Are you sure you don't want to research temporary tables?
Re: how to create a table within a procedure [message #295580 is a reply to message #295578] Tue, 22 January 2008 15:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot, I think the answer is in:
Quote:
I need this data ca. 3 month

(I think "ca." means something like "during")

Regards
Michel
Re: how to create a table within a procedure [message #295582 is a reply to message #295580] Tue, 22 January 2008 15:49 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh.

I promise, I will never watch CSI and browse forum at the same time again.
I promise, I will never watch CSI and browse forum at the same time again.
I promise, I will never watch CSI and browse forum at the same time again.
...
Re: how to create a table within a procedure [message #295622 is a reply to message #295468] Tue, 22 January 2008 22:34 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear fixerus,

please mail your tables and required output table(create manually with some test cases).
Re: how to create a table within a procedure [message #295723 is a reply to message #295622] Wed, 23 January 2008 03:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you need the data about 3 months, I would create one table store_stock_start and add the "orange part" as an additional column that you can put in the where clause of the report.

Oh, and "ca. (circa)" is German for "approx. (approximately)", for those who wondered. Wink

Re: how to create a table within a procedure [message #295738 is a reply to message #295468] Wed, 23 January 2008 04:19 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
If you need the data about 3 months


test cases have only few rows,not 3 months data.
Re: how to create a table within a procedure [message #295744 is a reply to message #295738] Wed, 23 January 2008 04:31 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What does how many rows there are to do with how long you will need the data to stay frozen? ./fa/1600/0/

Previous Topic: want to print dynamic sql query
Next Topic: Identity columns
Goto Forum:
  


Current Time: Sun Dec 04 10:35:17 CST 2016

Total time taken to generate the page: 0.07656 seconds