Solved

Date Format for Partitions In Oracle

Posted on 2013-05-13
14
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39161885
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
ID: 39161909
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39161932
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 39161966
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
ID: 39162501
I am using SQL Plus.  Woud the examples provided work in SQL Plus?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 39162543
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
 
LVL 1

Author Comment

by:jjrr007
ID: 39162919
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39162948
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
ID: 39162977
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39163120
>>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
ID: 39165321
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
ID: 39187767
Hi,
Any news on this one ?
0
 
LVL 1

Author Closing Comment

by:jjrr007
ID: 40016039
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

627 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