Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Error ora-01858 populating a partitioned table with insert into ... select ...

User_DGTCCSep 16 2022

I have a table with a timestamp field. I created a second partitioned table - partitioned per month and with the same filed - with the timestamp field used in the "PARTITION BY RANGE" condition. When I insert into "partitioned table" from the initial table I get error 01858.
How should I deal with the timestamp field in my select?

Comments

User_DGTCC

If necessary, I will provide specific data, but it seems like a problem to me: what to do if ...

asahide

Hi,
If possible, could you upload the table definition, the actual Insert statement, and the error output as is?
Regards,

User_DGTCC

create table test (
"TIPO_OPERAZIONE" VARCHAR2(50 CHAR),
"DATA_OPERAZIONE" TIMESTAMP (6)
);

Data as I see from sql*plus (and also from sqlDeveloper): "UPD-A","26-MAG-22 02:09:17,000000000"

create table test_PART (
"TIPO_OPERAZIONE" VARCHAR2(50 CHAR),
"DATA_OPERAZIONE" TIMESTAMP (6)
)
PARTITION BY RANGE (DATA_OPERAZIONE)
( PARTITION P_2022_06 VALUES LESS THAN (TIMESTAMP '2022-08-01 00:00:00.000000')
TABLESPACE nadtc
, PARTITION P_2022_07 VALUES LESS THAN (TIMESTAMP '2022-09-01 00:00:00.000000')
TABLESPACE nadtc
, PARTITION P_2022_08 VALUES LESS THAN (TIMESTAMP '2022-10-01 00:00:00.000000')
TABLESPACE nadtc
, PARTITION P_2023_09 VALUES LESS THAN (TIMESTAMP '2022-11-01 00:00:00.000000')
TABLESPACE nadtc
)
;
insert into TEST_PART select * from TEST;
*
ERRORE alla riga 1:
ORA-01858: è stato trovato un carattere non numerico al posto di uno numerico

User_DGTCC

Insertion on the source table is done with the "sysdate".

Saubhik

Not clear what you want to tell.

CREATE TABLE test (
    "TIPO_OPERAZIONE"  VARCHAR2(50 CHAR),
    "DATA_OPERAZIONE"  TIMESTAMP(6)
);

CREATE TABLE test_part (
    "TIPO_OPERAZIONE"  VARCHAR2(50 CHAR),
    "DATA_OPERAZIONE"  TIMESTAMP(6)
)
    PARTITION BY RANGE (
        data_operazione
    )
    ( PARTITION p_2022_06
        VALUES LESS THAN ( TIMESTAMP '2022-08-01 00:00:00.000000' ),
    PARTITION p_2022_07
        VALUES LESS THAN ( TIMESTAMP '2022-09-01 00:00:00.000000' ),
    PARTITION p_2022_08
        VALUES LESS THAN ( TIMESTAMP '2022-10-01 00:00:00.000000' ),
    PARTITION p_2023_09
        VALUES LESS THAN ( TIMESTAMP '2022-11-01 00:00:00.000000' )
    );
    
INSERT INTO test VALUES (
    'A',
    sysdate
);

INSERT INTO test VALUES (
    'B',
    sysdate + 20
);

INSERT INTO test_part
    SELECT
        *
    FROM
        test;

The above code block doesn't give any error. What s your data in "test" table? Is really the column is TIMESTAMP or VARCHAR2 ?

User_DGTCC

Hi Saubhik,
in test table column is really TIMESTAMP.
My (and your) code work properly from sqlDeveloper.
I am seeing that my sqlplus is an old release, set in Italian and the combination of these 2 factors gives problems.
We can close this discussion,
thank you for your interest.

1 - 6

Post Details

Added on Sep 16 2022
#sqlplus
6 comments
105 views