Home » SQL & PL/SQL » SQL & PL/SQL » Distinct within a procedure
Distinct within a procedure [message #191483] Wed, 06 September 2006 09:21 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,

I have a table with a date field containing data more than 5 years old. Based on the year value, I want to dynamically create table such as TABLE03 for year 2003, TABLE04 and so on.

I have 2 approaches, one is check the value for each row and then based on that use execute immediate to either create a table or proceed to the next row.

2nd is use distinct(substr(date_column,8,9)) and based on that check if the table exists, precreate the tables and directly process the further logic. Is cursor good for this as values of this may be 03 04 05. or array. Please suggest.




Regards,
Re: Distinct within a procedure [message #191486 is a reply to message #191483] Wed, 06 September 2006 09:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
create your tables in advance. Don't create tables on runtime; oracle really really doesn't like that.
Besides, it would require you to write all your insert-statements as dynamic sql as well..
Re: Distinct within a procedure [message #191506 is a reply to message #191486] Wed, 06 September 2006 15:24 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks..Any idea how to change the table name dynamically based on the condition of where clause. (eg 2004 data goes to table04 ) and so on
Re: Distinct within a procedure [message #191514 is a reply to message #191483] Wed, 06 September 2006 17:34 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Any idea how to change the table name dynamically based on the condition of where clause
It can be done using EXECUTE IMMEDIATE, but this 'solution' does NOT scale & should be avoided.
Alternatively use CASE, SYNONYM, or VIEWs
Re: Distinct within a procedure [message #191555 is a reply to message #191514] Thu, 07 September 2006 02:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How many different years do you have? Is it really that necessary to do it dynamically, or could you just write static sql for each year?
Re: Distinct within a procedure [message #191651 is a reply to message #191555] Thu, 07 September 2006 08:14 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
I agree that the no of times I have to create tables based on year is once for each year. But the client wants everything to be automated and no DDL must be done outside of package/procedure as DBA passwords are available with 3/4 admins.

Regards,
Re: Distinct within a procedure [message #192553 is a reply to message #191651] Tue, 12 September 2006 18:49 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Remember that creating a table is DDL and DDL implicitly commits any outstanding work. I would consider partitioned tables personnaly rather than manually separating your years. you can add or remove or swap partitions very easily and the optimizer knows which partition to look in (assuming you have the date in the where clause).
Re: Distinct within a procedure [message #193807 is a reply to message #192553] Tue, 19 September 2006 09:01 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Thanks for the help. I got the logic.
Previous Topic: How to pass the error ?
Next Topic: function changes not recognised
Goto Forum:
  


Current Time: Tue Dec 06 04:45:56 CST 2016

Total time taken to generate the page: 0.07338 seconds