Skip to Main Content
  • Questions
  • using insert into partition (partition_name) in PLSQL

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mahesh.

Asked: October 22, 2015 - 1:14 pm UTC

Last updated: June 24, 2022 - 10:08 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Hi ,

I am new to PLSQL and i am trying to insert data into table using insert into partition (partition_name) . When i am trying to load the data its saying the 'specified partition is not exixisting' . Please help me in this. The query is mentioned below

declare
v_start_time timestamp;
v_end_time timestamp;
v_last_day timestamp;
v_partition_date number ;
v_newpart VARCHAR2(30);
v_constant varchar2(5);
v_IMPRESSION_DATE impression_temp_1.IMPRESSION_DATE%TYPE;
begin
v_start_time := to_timestamp('01-04-2015 00:00:00', 'dd-mm-yyyy hh24:mi:ss');
v_end_time := to_timestamp('01-04-2015 23:59:59', 'dd-mm-yyyy hh24:mi:ss');
v_last_day := to_timestamp('10-04-2015 00:00:00', 'dd-mm-yyyy hh24:mi:ss');
v_partition_date := 20150401;
v_constant := 'P_';
v_newpart := concat(v_constant , v_partition_date);
dbms_output.put_line(v_newpart);
WHILE v_start_time <= v_last_day LOOP
dbms_output.put_line(v_newpart);
insert into IMPRESSION_TEMP_1 partition (v_newpart)
(
IMPRESSION_DATE
, IMPRESSION_TIMESTAMP
, RESPONSE_TIMESTAMP
, REP_CREATED_DATE_TIME
, REP_CREATED_BY
, REP_LAST_UPDATED_DATE_TIME
, REP_LAST_UPDATED_BY
)
select
N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP ,
N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP ,
N_NBA_DATA_IMPRESSION.RESPONSE_TIMESTAMP ,
N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP as REP_CREATED_DATE_TIME ,
'ARDP0B34' as REP_CREATED_BY,
N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP ,
'ARDP0B34' as REP_LAST_UPDATED_BY
FROM DATA_IMPRESSIONDATA_IMPRESSION
WHERE "DATA_IMPRESSION"."IMPRESSION_TIMESTAMP" >= v_start_time
and "DATA_IMPRESSION"."IMPRESSION_TIMESTAMP" <= v_end_time;
v_start_time := v_start_time + 1 ;
v_end_time := v_end_time + 1 ;
v_partition_date := v_partition_date + 1;
end loop;
commit;
end;

Error report:
ORA-02149: Specified partition does not exist
ORA-06512: at line 19
02149. 00000 - "Specified partition does not exist"
*Cause: Partition not found for the object.
*Action: Retry with correct partition name.
P_20150401
P_20150401


The DBMS_OUTPUT of the query shows P_20150401 is the values of the variable v_newpart)


But The partition with the name P_20150401 actually exist in the temp table .

SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'IMPRESSION_TEMP_1'
and partition_name = 'P_20150401' ;

PARTITION_NAME
------------------------------
P_20150401 .


So could you please advise me why insert into IMPRESSION_TEMP_1 partition (v_newpart) is not taking the value P_20150401 ?





and Chris said...

You can't use a variable to specify a partition name. You must pass an explicit reference.

However...

You don't need to supply the partition name at all! Oracle will automatically determine which partition to place the row in based on the value of the partition key column(s). Remove the partition clause from your insert completely:

...
insert into IMPRESSION_TEMP_1 (
  IMPRESSION_DATE 
, IMPRESSION_TIMESTAMP 
, RESPONSE_TIMESTAMP 
, REP_CREATED_DATE_TIME 
, REP_CREATED_BY 
, REP_LAST_UPDATED_DATE_TIME 
, REP_LAST_UPDATED_BY 
) ...

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

use System Partitoning

Paul, October 28, 2015 - 3:30 pm UTC

If for some reason you really want to specify the partition in which to insert by a variable (I recently had a case), you can use system partitioning combined with the dataobj_to_partition function. Here's an example how I did this (with a bulk insert):

        forall j in 0..lidx( i ) - 1
        insert into tabmov2
        partition ( dataobj_to_partition( tabmov2, pid ))
        values lrtmov2( i )( j ) ;


Here the pid variable is the object_id (from all_objects view) for the partition to be used.

You can specify partition and subpartitions as parameters

Ray, June 23, 2022 - 8:44 pm UTC

You actually CAN specify the partition/subpartition. Simply use EXECUTE IMMEDIATE

EXECUTE IMMEDATE 'insert into IMPRESSION_TEMP_1 partition ('||v_newpart||')
(
IMPRESSION_DATE
, IMPRESSION_TIMESTAMP
, RESPONSE_TIMESTAMP
, REP_CREATED_DATE_TIME
, REP_CREATED_BY
, REP_LAST_UPDATED_DATE_TIME
, REP_LAST_UPDATED_BY
)
select
N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP ,
N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP ,
N_NBA_DATA_IMPRESSION.RESPONSE_TIMESTAMP ,
N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP as REP_CREATED_DATE_TIME ,
''ARDP0B34'' as REP_CREATED_BY,
N_NBA_DATA_IMPRESSION.IMPRESSION_TIMESTAMP ,
''ARDP0B34'' as REP_LAST_UPDATED_BY
FROM DATA_IMPRESSIONDATA_IMPRESSION
WHERE "DATA_IMPRESSION"."IMPRESSION_TIMESTAMP" >= to_timestamp('''||to_char(v_start_time,'DD-MON-YYYY HH24:MI:SS.FF AM')||'',''DD-MON-YYYY HH24:MI:SS.FF AM''')'
and "DATA_IMPRESSION"."IMPRESSION_TIMESTAMP" <= to_timestamp('''||to_char(v_end_time,'DD-MON-YYYY HH24:MI:SS.FF AM')||'',''DD-MON-YYYY HH24:MI:SS.FF AM'')';
Chris Saxon
June 24, 2022 - 10:08 am UTC

True - though the partition name is still a literal in the SQL statement; it's only a variable in PL/SQL

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here