Home » SQL & PL/SQL » SQL & PL/SQL » Partition Exchange in Procedure
Partition Exchange in Procedure [message #280502] Tue, 13 November 2007 22:08 Go to next message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
Below Procedure giving Error.
ORA-00904: "PART_30_NOV_2007": invalid identifier

In the table pname column has PART_30_NOV_2007 value and type is varchar(25).Any ideas please


create or replace procedure Partion_Exchng
IS
V_PARTION_NAME varchar2(25);
BEGIN
select pname INTO V_PARTION_NAME from part
where substr(pname,9,8)=(select TO_CHAR(sysdate,'MON')||'_'||TO_CHAR(sysdate,'YYYY') from dual);
EXECUTE IMMEDIATE 'UPDATE sart set eno=99 where pname='||TO_CHAR(V_PARTION_NAME);
end;


Thanks
Sheker


Re: Dynamic SQL in Procedure [message #280505 is a reply to message #280502] Tue, 13 November 2007 22:19 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Can you post the entire structure of the part table..
Re: Dynamic SQL in Procedure [message #280506 is a reply to message #280502] Tue, 13 November 2007 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines above.
Please CUT & PASTE from SQL*Plus session using <code tags> to
show ALL details of complete session.

I don't understand what you are hoping to achieve or why you need dynamic SQL & won't use bind variable(s).
Re: Dynamic SQL in Procedure [message #280508 is a reply to message #280502] Tue, 13 November 2007 22:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
It is a good idea to use DBMS_OUTPUT to output your statement, so that you can see what it comes out like. You are missing single quotes on both sides, so it should be:

EXECUTE IMMEDIATE 'UPDATE sart set eno=99 where pname='''||TO_CHAR(V_PARTION_NAME)||'''';

or better, using a bind variable:

EXECUTE IMMEDIATE 'UPDATE sart set eno=99 where pname=:b_partition_name'
USING v_partition_name;

or better than that, without dynamic sql, just:

UPDATE sart set eno=99 where pname=v_partition_name;

Simpler yet, you can skip the select from dual and just use the value.

Even simpler, you can just use one update statement in the procedure, without a separate select.

And, simplest of all, you could just do the whole thing in one SQL update statement without any pl/sql.

So, I am not sure why you are doing everything the hard way.

[Updated on: Tue, 13 November 2007 22:34]

Report message to a moderator

Re: Dynamic SQL in Procedure [message #280816 is a reply to message #280502] Wed, 14 November 2007 20:52 Go to previous messageGo to next message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
Thanks for your replies.I am new to this forum

Here is my requirement:
I have temp Table say W_CHNG will have monthly volume 20million.This data need to move to Target(W_CHNG_DIM) table(into current month partition,table partitioned by Range for each month )using Partition Exchange method.After moving into Target table data need to be truncated in Temp table.Both tables have same structure.

I am getting below errors while executing :
Identifier is too long
at AXY.Partion_Exchng ,line 9
,line 2

Code :

create or replace procedure AXY.Partion_Exchng
is
V_NAME varchar2(20);
BEGIN
select partition_name into V_NAME from user_tab_partitions
where table_name = 'W_CHNG_DIM'
and substr(partition_name,13,8)=(select
TO_CHAR(sysdate,'MON')||'_'||TO_CHAR(sysdate,'YYYY') from dual);
EXECUTE IMMEDIATE 'ALTER TABLE W_CHNG_DIM EXCHANGE PARTITION'||V_NAME||'WITH TABLE W_CHNG without validation UPDATE GLOBAL INDEXES' ;
end;


Thanks in Advance
Sheker
Re: Dynamic SQL in Procedure [message #280822 is a reply to message #280502] Wed, 14 November 2007 21:34 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
space characters matter!

IMO, you should form the SQL statement into a single VARCHAR2 variable & print it out before the EXECUTE IMMEDIATE

You can then CUT & PASTE into SQL*Plus after it fail to see exactly what is wrong.
Partition Exchange in Procedure [message #281135 is a reply to message #280502] Thu, 15 November 2007 18:08 Go to previous messageGo to next message
sheker2007
Messages: 19
Registered: November 2007
Junior Member
Here is my requirement:
I have temp Table say W_CHNG will have monthly volume 20million.This data need to move to Target(W_CHNG_DIM) table(into current month partition,table partitioned by Range for each month )using Partition Exchange method.After moving into Target table data need to be truncated in Temp table.Both tables have same structure.

I am getting below errors while executing :
Identifier is too long
at AXY.Partion_Exchng ,line 9
,line 2

Code :

create or replace procedure AXY.Partion_Exchng
is
V_NAME varchar2(20);
BEGIN
select partition_name into V_NAME from user_tab_partitions
where table_name = 'W_CHNG_DIM'
and substr(partition_name,13,8)=(select
TO_CHAR(sysdate,'MON')||'_'||TO_CHAR(sysdate,'YYYY') from dual);
EXECUTE IMMEDIATE 'ALTER TABLE W_CHNG_DIM EXCHANGE PARTITION'||V_NAME||'WITH TABLE W_CHNG without validation UPDATE GLOBAL INDEXES' ;
end;


Thanks in Advance
Sheker
Re: Partition Exchange in Procedure [message #281167 is a reply to message #281135] Thu, 15 November 2007 23:29 Go to previous message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You need to make sure you have spaces in all the appropriate places. For example,

PARTITION'||V_NAME||'WITH

should be:

PARTITION '||V_NAME||' WITH

Please see the example below. Also notice that I have eliminated the unnecessary select from dual.

SCOTT@orcl_11g> CREATE TABLE w_chng_dim
  2    (col1  NUMBER,
  3  	col2  DATE)
  4    PARTITION BY RANGE (col2)
  5  	 (PARTITION W_CHNG_DIM__OCT_2007 VALUES LESS THAN (TO_DATE ('01-nov-2007', 'dd-mon-yyyy')),
  6  	  PARTITION W_CHNG_DIM__NOV_2007 VALUES LESS THAN (TO_DATE ('01-dec-2007', 'dd-mon-yyyy')))
  7  /

Table created.

SCOTT@orcl_11g> INSERT INTO w_chng_dim VALUES (1, TO_DATE ('5-oct-2007', 'DD-mon-yyyy'))
  2  /

1 row created.

SCOTT@orcl_11g> CREATE TABLE w_chng
  2    (col1  NUMBER,
  3  	col2  DATE)
  4  /

Table created.

SCOTT@orcl_11g> INSERT INTO w_chng VALUES (2, TO_DATE ('10-nov-2007', 'DD-mon-yyyy'))
  2  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM w_chng_dim
  2  /

      COL1 COL2
---------- ---------
         1 05-OCT-07

SCOTT@orcl_11g> SELECT * FROM w_chng
  2  /

      COL1 COL2
---------- ---------
         2 10-NOV-07

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE partition_exchng
  2  AS
  3    v_name  VARCHAR2 (30);
  4  BEGIN
  5    SELECT partition_name
  6    INTO   v_name
  7    FROM   user_tab_partitions
  8    WHERE  table_name = 'W_CHNG_DIM'
  9    AND    SUBSTR (partition_name, 13, 8) =
 10  	      TO_CHAR (SYSDATE, 'MON') || '_' || TO_CHAR (SYSDATE, 'YYYY');
 11    EXECUTE IMMEDIATE
 12  	 'ALTER TABLE W_CHNG_DIM EXCHANGE PARTITION ' || v_name
 13  	 || ' WITH TABLE W_CHNG WITHOUT VALIDATION UPDATE GLOBAL INDEXES';
 14  END partition_exchng;
 15  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXECUTE partition_exchng

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM w_chng_dim
  2  /

      COL1 COL2
---------- ---------
         1 05-OCT-07
         2 10-NOV-07

SCOTT@orcl_11g> SELECT * FROM w_chng
  2  /

no rows selected

SCOTT@orcl_11g> 

Previous Topic: Trigger - can I access :NEW AND :OLD dynamically???
Next Topic: Consistency
Goto Forum:
  


Current Time: Sat Dec 03 06:04:05 CST 2016

Total time taken to generate the page: 0.10783 seconds