ORA-14108: illegal partition-extended table name syntax

Mayank Mukherjee

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;

/
Luke Woodward

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Illegal Syntax for Set Operation

From Dev

C# Syntax Error Near Table Name

From Dev

cx_Oracle CREATE TABLE AS returns ORA-01036: illegal variable name/number

From Dev

Oracle ORA-01036 illegal variable name/number for no obvious reason

From Dev

Display the name of syntax table for the current buffer in Emacs

From Dev

Is this syntax illegal?

From Dev

Is this template syntax illegal?

From Dev

How to get partition column name from partition table in oracle server

From Dev

ORA-01036: illegal variable name/number - oci_bind_by_name

From Dev

Resizing extended partition with gparted

From Dev

Resizing extended partition with gparted

From Dev

query partition table name for a specific value

From Dev

Convert logical partition inside extended partition to primary outside of extended

From Dev

Query syntax exception, table name is not mapped

From Dev

SSD Extended Partition Misalignment

From Dev

Syntax error near table_name

From Dev

Dapper and Oracle parametrized query - ORA-01036: illegal variable name/number

From Dev

Is this syntax illegal?

From Dev

Creating swaps in extended partition

From Dev

Are there extended partitions in GPT partition table?

From Dev

SQL syntax: outer table_name

From Dev

Creating a extended partition

From Dev

Linux extended partition types

From Dev

IllegalArgumentException and Illegal partition for 'val' in sqoop

From Dev

Ora-00903 invalid table name

From Dev

ORA-00997: illegal use of LONG datatype: while creating a table with select statement

From Dev

ORA-01036: illegal variable name/number when running query through nodejs

From Dev

ORA-00903: invalid table name when creating a new table

From Dev

SQL syntax error on table name

Related Related

HotTag

Archive