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'')';
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