Home » SQL & PL/SQL » SQL & PL/SQL » Partition Query
Partition Query [message #262721] Tue, 28 August 2007 02:25 Go to next message
skkazmi
Messages: 44
Registered: April 2006
Member
Hi,

I m facing problem when extract the data from the partition.
Following expression

DECLARE
T_ID NUMBER;
P_NAME VARCHAR2(20):='PRO_TMST_2007';
BEGIN
SELECT '200708'||LPad(Nvl(Max(To_Number(SubStr(TRANS_ID,7,Length(TRANS_ID)))),0)+1,4,0) NO
INTO T_ID
FROM PRO_TRANS_MST PARTITION (P_NAME)
WHERE To_Char(TRANS_DT,'YYYYMM')='200708';
Dbms_Output.PUT_LINE(T_ID);
END;

Note: P_Name is user define variable.

I found n error i.e
ORA-02149: Specified partition does not exist

My Question is that, can i use variable in partitioning selection.

Khurram.
Re: Partition Query [message #262723 is a reply to message #262721] Tue, 28 August 2007 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can i use variable in partitioning selection

No, it must be a constant.
You have to use dynamic SQL if you want to do this.

Regards
Michel
Re: Partition Query [message #262725 is a reply to message #262723] Tue, 28 August 2007 02:34 Go to previous messageGo to next message
skkazmi
Messages: 44
Registered: April 2006
Member
thks 4 reply

how can i create the dymanic SQL. Which gives dymanically partitioning selection. Can u give some idea about.

Khurram
Re: Partition Query [message #262727 is a reply to message #262725] Tue, 28 August 2007 02:40 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
execute immediate

Regards
Michel
Previous Topic: DEBUG PIVOT CODE
Next Topic: case in where ... what i m doing wrong??
Goto Forum:
  


Current Time: Tue Dec 06 06:48:53 CST 2016

Total time taken to generate the page: 0.08314 seconds