Link to home
Create AccountLog in
Avatar of jjrr007
jjrr007

asked on

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?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.
Avatar of jjrr007

ASKER

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.
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;
/
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;
Avatar of jjrr007

ASKER

I am using SQL Plus.  Woud the examples provided work in SQL Plus?
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jjrr007

ASKER

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?
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.
Avatar of jjrr007

ASKER

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.
>>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?
???
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi,
Any news on this one ?
Avatar of jjrr007

ASKER

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