ORA-14108: illegal partition-extended table name syntax

Question

I have a requirement where I need to run a update script over multiple partitions of a table . I have written a script for it as below:

but it gives

ORA-14108: illegal partition-extended table name syntax

Cause: Partition to be accessed may only be specified using its name. User attempted to use a partition number or a bind variable.

Action: Modify statement to refer to a partition using its name

Any idea how can I circumvent this error?

DECLARE 

TYPE partition_names IS varray(1) OF varchar2(20);

curr_partition partition_names;

LENGTH integer;

BEGIN

curr_partition :=partition_names('SM_20090731');

LENGTH := curr_partition.count;


FOR i IN 1 .. LENGTH LOOP 

dbms_output.put_line('Current Partition name is: '||curr_partition(i));

UPDATE TABLE_Y PARTITION (curr_partition(i))
SET PARTITION_KEY=TO_DATE('2017-08-21','YYYY-MM-DD')
WHERE ORDER_ID IN
    (SELECT ORDER_ID
     FROM TABLE_X);

END LOOP;

END;

/

score 4 · Accepted Answer · answered Sep 02 '17 at 12:38

You will have to concatenate the partition name in and use dynamic SQL, i.e.

EXECUTE IMMEDIATE
  'UPDATE TABLE_Y PARTITION (' || curr_partition(i) || ')
   SET PARTITION_KEY=TO_DATE(''2017-08-21'',''YYYY-MM-DD'')
   WHERE ORDER_ID IN
       (SELECT ORDER_ID
        FROM TABLE_X)';

Whenever you run a SQL SELECT query or an INSERT, UPDATE or DELETE statement from PL/SQL, bind variables are used to pass into the SQL engine the values of any PL/SQL expressions. In particular, a bind parameter will be used for curr_partition(i). However, it seems the PARTITION clause of such queries and statements doesn't support bind parameters. I guess that this is because Oracle tries to create an execution plan for the query or statement before it has the bind parameter values, but if the query or statement specifies a partition, that information is a critical part of the plan and hence cannot be provided in a bind parameter.

This also worked to solve the same error when executing an `EXECUTE IMMEDIATE 'ALTER TABLE table DROP PARTITION :partition_name' USING 'some_partition_name'`. How could we know we could not use bind vars on THIS specific query... Thanks! — Alfabravo, Nov 06 '19 at 23:04

ORA-14108: illegal partition-extended table name syntax

1 Answers1