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?
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?
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;
/
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;
DECLARE
mycursor SYS_REFCURSOR;
BEGIN
OPEN mycursor FOR
'select * from ' || 'tablename partition(p' || TO_CHAR(SYSDATE - 2, 'yyyymmdd') || ')';
END;
ASKER
I am using SQL Plus. Woud the examples provided work in SQL Plus?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
The latest code I posted can run in sqlplus but the 'exec' call is actually handed off to the PL/SQL engine.
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.
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?
???
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
Any news on this one ?
Any news on this one ?
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
I appreciate the info that you can not do dynamic sql in sql plus from slightwv
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.