Thanks for the question, Arjun.

Asked: September 07, 2017 - 8:58 am UTC

Last updated: September 11, 2017 - 8:14 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

I have 2 tables. One of those is interval partitioned.

CREATE TABLE testtable 
   ("MYVALUE" VARCHAR2(200) NOT NULL ENABLE, 
 "COLLECTION_TIME" DATE NOT NULL ENABLE 
 ) 
 PARTITION BY RANGE (COLLECTION_TIME) INTERVAL(NUMTODSINTERVAL(1, 'DAY')) 
            ( PARTITION p_default VALUES LESS THAN (to_timestamp('1-1-2017', 'DD-MM-YYYY')) )

CREATE TABLE destinationtable 
   ("MYVALUE" VARCHAR2(200) NOT NULL ENABLE, 
 "COLLECTION_TIME" DATE NOT NULL ENABLE 
 )

Lets say I insert 4 rows with different collection_time in the testtable.

insert into testtable values ('Value1',to_date('2-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'))

insert into testtable values ('Value1',to_date('3-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'))

insert into testtable values ('Value1',to_date('4-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'))

insert into testtable values ('Value1',to_date('5-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'))

This should create 4 new partitions in this table.

Now, I have aprocedure whose input is date, I want to insert only rows in the partition for that date into the destinationtable.
I have written a similar proc here ->

declare
   p_records_for_date varchar2(200) := '2017-09-03 00:00';
   l_parition_sql_addon  varchar2(200);
begin
   l_parition_sql_addon := ' partition for (to_date(substr('||p_records_for_date||',1,10),''yyyy-mm-dd''))';
   execute immediate 'insert into destinationtable select myvalue,collection_time from testtable'||l_parition_sql_addon ;
end;
/

My question is, will this work ? or I will hit "ORA-14763: Unable to resolve FOR VALUES clause to a partition number
14763. 00000 - "Unable to resolve FOR VALUES clause to a partition number"
*Cause: Could not determine the partition corresponding to the FOR VALUES
clause.
*Action: Remove bind variables and dependencies on session parameters
from the values specified in the FOR VALUES clause."

Please comment on when that could happen and how it can be resolved ?

and Connor said...

Well...we've got one problem to start with

SQL> CREATE TABLE testtable
  2     ("MYVALUE" VARCHAR2(200) NOT NULL ENABLE,
  3   "COLLECTION_TIME" DATE NOT NULL ENABLE
  4   )
  5   PARTITION BY RANGE (COLLECTION_TIME) INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
  6              ( PARTITION p_default VALUES LESS THAN (to_timestamp('1-1-2017', 'DD-MM-YYYY')) )
  7  /

Table created.

SQL>
SQL>
SQL>
SQL> CREATE TABLE destinationtable
  2     ("MYVALUE" VARCHAR2(200) NOT NULL ENABLE,
  3   "COLLECTION_TIME" DATE NOT NULL ENABLE
  4   )
  5   /

Table created.

SQL>
SQL>
SQL> insert into testtable values ('Value1',to_date('2-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'));

1 row created.

SQL> insert into testtable values ('Value1',to_date('3-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'));

1 row created.

SQL> insert into testtable values ('Value1',to_date('4-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'));

1 row created.

SQL> insert into testtable values ('Value1',to_date('5-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'));

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select partition_name
  2  from user_tab_partitions
  3  where table_name = 'TESTTABLE';

PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P_DEFAULT

1 row selected.

SQL>
SQL> select to_char(COLLECTION_TIME,'dd/mm/yyyy') from TESTTABLE;

TO_CHAR(CO
----------
02/09/0017
03/09/0017
04/09/0017
05/09/0017

4 rows selected.

but lets proceed with the test case you probably *intended* to write

SQL> insert into testtable values ('Value1',to_date('2-SEP-2017 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'));

1 row created.

SQL> insert into testtable values ('Value1',to_date('3-SEP-2017 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'));

1 row created.

SQL> insert into testtable values ('Value1',to_date('4-SEP-2017 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'));

1 row created.

SQL> insert into testtable values ('Value1',to_date('5-SEP-2017 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM'));

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select partition_name
  2  from user_tab_partitions
  3  where table_name = 'TESTTABLE';

PARTITION_NAME
-----------------------------------------------------------------------------------------------------------------
P_DEFAULT
SYS_P3969
SYS_P3970
SYS_P3971
SYS_P3972

So *now* we have 4 partitions. Now your anonymous block *will* get an error...but not because of partitioning.

SQL>
SQL> declare
  2     p_records_for_date varchar2(200) := '2017-09-03 00:00';
  3     l_parition_sql_addon  varchar2(200);
  4  begin
  5     l_parition_sql_addon := ' partition for (to_date(substr('||p_records_for_date||',1,10),''yyyy-mm-dd''))';
  6     execute immediate 'insert into destinationtable select myvalue,collection_time from testtable'||l_parition_sql_addon ;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at line 6

Because you are not generating the SQL correctly


SQL>
SQL> set serverout on
SQL> declare
  2     p_records_for_date varchar2(200) := '2017-09-03 00:00';
  3     l_parition_sql_addon  varchar2(200);
  4  begin
  5     l_parition_sql_addon := ' partition for (to_date(substr('||p_records_for_date||',1,10),''yyyy-mm-dd''))';
  6
  7  --   execute immediate 'insert into destinationtable select myvalue,collection_time from testtable'||l_parition_sql_addon ;
  8  dbms_output.put_line('insert into destinationtable select myvalue,collection_time from testtable'||l_parition_sql_addon );
  9  end;
 10  /
insert into destinationtable select myvalue,collection_time from testtable partition for (to_date(substr(2017-09-03
00:00,1,10),'yyyy-mm-dd'))

PL/SQL procedure successfully completed.

See the missing quotes. But lets put that aside and just try some inserts.... You'll find they work for partitions and non-existent partitions too


SQL>
SQL> insert into destinationtable
  2  select myvalue,collection_time
  3  from testtable partition for (to_date(substr('2017-09-03 00:00',1,10),'yyyy-mm-dd'));

1 row created.

SQL> insert into destinationtable
  2  select myvalue,collection_time
  3  from testtable partition for (to_date(substr('2018-09-03 00:00',1,10),'yyyy-mm-dd'));

0 rows created.

Rating

(3 ratings)

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

Comments

Bind variables

Thomas Brotherton, September 07, 2017 - 2:32 pm UTC

The value(s) they are passing into their statement should be bound, not concatenated. Mr. Kyte would be disappointed in you not mentioning that.

September 09, 2017 - 12:57 am UTC

You *did* do a test to back up that statement yes ? :-)

Because I did :-)

SQL> variable x varchar2(100)
SQL> exec :x := '2017-09-03 00:00';

PL/SQL procedure successfully completed.

SQL>
SQL> insert into destinationtable
  2  select myvalue,collection_time
  3  from testtable partition for (to_date(substr(:x,1,10),'yyyy-mm-dd'));
insert into destinationtable
            *
ERROR at line 1:
ORA-14763: Unable to resolve FOR VALUES clause to a
partition number


SQL>
SQL> insert into destinationtable
  2  select myvalue,collection_time
  3  from testtable partition for (to_date(substr('2017-09-03 00:00',1,10),'yyyy-mm-dd'));

1 row created.

This is confusing.

Arjun Talwar, September 09, 2017 - 7:41 am UTC

I did actually try running this and this works flawlessly.

declare
      p_records_for_date varchar2(200) := '2017-09-03 00:00:00';
      l_parition_sql_addon  varchar2(200);
   begin
      l_parition_sql_addon := ' partition for (to_date(substr('''||p_records_for_date||''',1,10),''yyyy-mm-dd''))';
 
    execute immediate 'insert into destinationtable select myvalue,collection_time from testtable'||l_parition_sql_addon;
    end;
   /

In reality, The p_records_for_date is passed to my procedure as an input. and then I run the execute immediate statement exactly as mentioned above. The records get inserted and if some random date is passed for which no partition is there, even then I dont get any exception.

I didnt understand the repost that was made on this question.

September 11, 2017 - 8:14 am UTC

Yes, but that is not the same code as you initially posted.

regarding the bounded values

Rajeshwaran, Jeyabal, September 12, 2017 - 12:48 pm UTC

First, the date value doesn't match with the format specified.

demo@ORA11G> insert into testtable values ('Value1',to_date('2-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM')) ;
demo@ORA11G> insert into testtable values ('Value1',to_date('3-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM')) ;
demo@ORA11G> insert into testtable values ('Value1',to_date('4-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM')) ;
demo@ORA11G> insert into testtable values ('Value1',to_date('5-SEP-17 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM')) ;
demo@ORA11G> commit;
demo@ORA11G> select * from testtable;

MYVALUE    COLLECTION_TIME
---------- --------------------
Value1     02-SEP-0017
Value1     03-SEP-0017
Value1     04-SEP-0017
Value1     05-SEP-0017

demo@ORA11G>

fixed that, got this data in place, with the right format.

demo@ORA11G> truncate table testtable;
demo@ORA11G> insert into testtable values ('Value1',to_date('2-SEP-2017 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM')) ;
demo@ORA11G> insert into testtable values ('Value1',to_date('3-SEP-2017 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM')) ;
demo@ORA11G> insert into testtable values ('Value1',to_date('4-SEP-2017 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM')) ;
demo@ORA11G> insert into testtable values ('Value1',to_date('5-SEP-2017 06:36:28 AM', 'DD-MON-YYYY HH:MI:SS AM')) ;
demo@ORA11G> commit;
demo@ORA11G>
demo@ORA11G> select * from testtable;

MYVALUE    COLLECTION_TIME
---------- --------------------
Value1     02-SEP-2017
Value1     03-SEP-2017
Value1     04-SEP-2017
Value1     05-SEP-2017

since given the interval defined on the table for each day

PARTITION BY RANGE (COLLECTION_TIME) INTERVAL(NUMTODSINTERVAL(1, 'DAY'))

we could makes use of the range predicates like this. (that is bounded too).

demo@ORA11G> variable x varchar2(20)
demo@ORA11G> exec :x := '2017-09-03';

PL/SQL procedure successfully completed.

demo@ORA11G>
demo@ORA11G> begin
  2     insert into destinationtable(myvalue,collection_time)
  3     select myvalue,collection_time
  4     from testtable
  5     where collection_time >=  to_date(:x,'yyyy-mm-dd')
  6             and collection_time < to_date(:x,'yyyy-mm-dd')+1;
  7
  8     for x in (select * from table(dbms_xplan.display_cursor))
  9     loop
 10             dbms_output.put_line(x.plan_table_output);
 11     end loop;
 12  end;
 13  /
SQL_ID  fwcp70700hw7t, child number 0
-------------------------------------
INSERT INTO DESTINATIONTABLE(MYVALUE,COLLECTION_TIME) SELECT
MYVALUE,COLLECTION_TIME FROM TESTTABLE WHERE COLLECTION_TIME >=
TO_DATE(:B1,'yyyy-mm-dd') AND COLLECTION_TIME <
TO_DATE(:B1,'yyyy-mm-dd')+1

Plan hash value: 2531717698

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT           |           |       |       |    51 (100)|          |       |       |
|   1 |  LOAD TABLE CONVENTIONAL   |           |       |       |            |          |       |       |
|*  2 |   FILTER                   |           |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|           |     3 |   333 |    51   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | TESTTABLE |     3 |   333 |    51   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_DATE(:B1,'yyyy-mm-dd')+1>TO_DATE(:B1,'yyyy-mm-dd'))
   4 - filter(("COLLECTION_TIME">=TO_DATE(:B1,'yyyy-mm-dd') AND
              "COLLECTION_TIME"<TO_DATE(:B1,'yyyy-mm-dd')+1))

Note
-----
   - dynamic sampling used for this statement (level=2)


PL/SQL procedure successfully completed.

demo@ORA11G> select * from destinationtable;

MYVALUE    COLLECTION_TIME
---------- --------------------
Value1     03-SEP-2017

demo@ORA11G>

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Bind variables

This is confusing.

regarding the bounded values

More to Explore

VLDB

VLDB

ORA-14763 when referencing a partition with INSERT

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Bind variables

This is confusing.

regarding the bounded values

More to Explore

VLDB

VLDB