0

Thanks to JSapkota who helped me pull the subpartition name as per below query

select subobject_name from user_objects where data_object_id IN 
(select dbms_rowid.rowid_object(rowid) from TABLE 
WHERE DOCID = 'S00102981655537O') 

Which gives me a result

SUBOBJECT_NAME               
--------------
MAR16_IN_UK

Now if I update that ID using below statement it works

UPDATE TABLE SUBPARTITION (MAR16_IN_UK) 
SET FLAG = 'Y'
WHERE DOCID = 'S00102981655537O';

1 rows updated

But the same query by dynamically pulling the subpartition name would throw an error

UPDATE TABLE 
SUBPARTITION (select subobject_name from user_objects where 
data_object_id IN 
(select dbms_rowid.rowid_object(rowid) from TABLE 
WHERE DOCID = 'S00102981655537O')) 
SET FLAG = 'Y'
WHERE DOCID = 'S00102981655537O';

Error

Error report -
SQL Error: ORA-00971: missing SET keyword
00971. 00000 -  "missing SET keyword"
*Cause:    
*Action:

Version: Oracle 11g

2
  • Why would you like to do that? Just simple update statement would do that. Or if you want to update partition independently then can use partition key.
    – atokpas
    Mar 31, 2016 at 9:29
  • The reason I am doing that is because I am checking the Explain Plan by both providing the partition name and not providing it. I see that the Bytes are high if I dont provide the partition name. Mar 31, 2016 at 10:08

1 Answer 1

2

you can not do in dynamically like this. partition name has to be known and the time when statement is compiled.

But you can use PARTITION FOR clause:

UPDATE TABLE T PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy')
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.