Solved

Date Format for Partitions In Oracle

Posted on 2013-05-13
14
320 Views
Last Modified: 2014-04-22
I want to change this statment:
 From tablename  partition(p20130511) tb

To look two days back, so I don't have to keep on inputting the dates.

I want to change it to something like this:
From tablename  partition(p (sysdate  - interval '2' day)) tb

I am returning an invalid partition error when I try this.  Could someone please let me know how I can do this?
0
Comment
Question by:jjrr007
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
You cannot do dynamic SQL like this on a table (or partition).

It is sort of the equivalent of this:
say you have a table named BOB1:  You cannot do this:
select * from 'BOB' || '1';

You will need to use dynamic SQL.
0
 
LVL 1

Author Comment

by:jjrr007
Comment Utility
Could you please provide an example of how to do this (using dynamic SQL or another method)?  I have increase the point value of this question to the max.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
What tool are you using to run this and what is the end result?

In pl/sql it goes something like:

declare
    mycursor sys_refcursor;
begin
    open mycursor for 'select * from ' || 'BOB' || '1';
end;
/
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
building off of slightwv's example using your date format...


DECLARE
    mycursor   SYS_REFCURSOR;
BEGIN
    OPEN mycursor FOR
        'select * from ' || 'tablename partition(p' || TO_CHAR(SYSDATE - 2, 'yyyymmdd') || ')';
END;
0
 
LVL 1

Author Comment

by:jjrr007
Comment Utility
I am using SQL Plus.  Woud the examples provided work in SQL Plus?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
Comment Utility
You cannot do dynamic sql directly in sqlplus.

try this:
SQL>  var mycur refcursor
SQL> open :mycur for  'select * from ' || 'tablename partition(p' || TO_CHAR(SYSDATE - 2, 'yyyymmdd') || ')';
SQL> print mycur
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:jjrr007
Comment Utility
I'm not following why your earlier posts will not work in SQL Plus.  I am just running a query in SQL Plus.  Why do I need to change the query, because of using SQL Plus?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
sqlplus does not allow dynamic SQL like you are wanting to do.

The latest code I posted can run in sqlplus but the 'exec' call is actually handed off to the PL/SQL engine.
0
 
LVL 1

Author Comment

by:jjrr007
Comment Utility
Thanks for the responses!  I appreciate your time.

 So I just paste the code you wrote (with the table names) in a query and have SQL plus run it.  Is that right or do I enter it into the command window?

How do I use the results- could I use mycurr as a table like this or using a temp table?

select *
From mycur

If I can use it in a temp table, please provide an example.  I should be all done once I know where to enter this (in the query or command using SQL plus) and how do I use the cursor results.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>select * From mycur

You cannot select from a cursor.

>>If I can use it in a temp table, please provide an example.  

This goes back to my post asking how the information will be used.

We need to know more about what you are doing before we can suggest the 'best' way to do it ('best' is in quotes because what is 'best' changes with differing opinions).

What we know:
You have data in a partition.
You want to select just from that partition.

We need to know the rest:
Where and how will it be used?
Is this part of a larger script?
Will you be using the select data for something else?
???
0
 
LVL 7

Accepted Solution

by:
Docteur_Z earned 400 total points
Comment Utility
I don't understand why you don't simply use a "where" clause with the date, and let Oracle do the partition prunning itself.....
something like :
select * from tablename  where 
partition_key_column between 
sysdate  - interval '3' day and sysdate  - interval '2' day 

Open in new window

0
 
LVL 7

Expert Comment

by:Docteur_Z
Comment Utility
Hi,
Any news on this one ?
0
 
LVL 1

Author Closing Comment

by:jjrr007
Comment Utility
Placing it in the where statment worked. Sometimes we forget the simple stuff.  

I appreciate the info that you can not do dynamic sql in sql plus from slightwv
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now