Date Format for Partitions In Oracle

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?
Who is Participating?
Docteur_ZConnect With a Mentor Commented:
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

slightwv (䄆 Netminder) Commented:
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.
jjrr007Author Commented:
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.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

In pl/sql it goes something like:

    mycursor sys_refcursor;
    open mycursor for 'select * from ' || 'BOB' || '1';
building off of slightwv's example using your date format...

    mycursor   SYS_REFCURSOR;
    OPEN mycursor FOR
        'select * from ' || 'tablename partition(p' || TO_CHAR(SYSDATE - 2, 'yyyymmdd') || ')';
jjrr007Author Commented:
I am using SQL Plus.  Woud the examples provided work in SQL Plus?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
jjrr007Author Commented:
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?
slightwv (䄆 Netminder) Commented:
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.
jjrr007Author Commented:
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.
slightwv (䄆 Netminder) Commented:
>>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?
Any news on this one ?
jjrr007Author Commented:
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
All Courses

From novice to tech pro — start learning today.