Hi,
I get an error ORA-01438: value larger than specified precision allowed for this column when I run an SQL script below.
The error is on this statement :
stmt := 'INSERT INTO '||V_nom_table||' VALUES (:1, :2, :3, :4, :5, :6, :7)';
Execute immediate stmt USING n, V_DATE1, V_VALEUR, 0, '01/01/01', 0, 0;
It is for the variable "v_valeur" that Oracle complains. I have checked out the value of valeur at this moment (with the statement DBMS_OUTPUT.PUT_LINE( 'Valeur : ' || V_valeur );in the script), and it is 0. So I try to enter a 0 in the column "RAW_VALUE". The column "RAW_VALUE" is type NUMBER. I don't understand why Oracle complains because a value of 0 is not larger than the "specified precision allowed for this column". How to solve this ?
Script :
alter session set NLS_DATE_FORMAT = 'DD/MM/YY-HH:MI:SS' ;
set serveroutput on
DECLARE
cursor C1 is select date1, valeur, clef_var, id from EVV_STQR4@BDSIVOA.NTSIVOA, sensor
where clef_var = (select clef_var from c_variable@bdsivoa.NTSIVOA where ref_var= 'W093LONBT018' ) and date1 > (select lastacq from sensor where ID = (Select ID from sensor where CMT= 'W093LONBT018' )) and CMT = 'W093LONBT018' ;
V_DATE1 DATE NULL ;
V_Nom_table varchar2(1000) ;
Stmt VARCHAR2(2000);
V_Nom_ID Varchar2(2000) ;
V_nom_seq Varchar2(2000) ;
V_VALEUR NUMBER (24,6);
V_CLEF_VAR NUMBER ;
n number ;
boucles number default 0 ;
MAX_RAW_DATE DATE ;
BEGIN
For ligne in C1
Loop
exit when SQL%NOTFOUND ;
boucles := boucles + 1 ;
V_Nom_ID := TO_CHAR(ligne.ID) ;
V_DATE1 := ligne.DATE1 ;
V_VALEUR := ligne.VALEUR ;
V_nom_table := 'SAFEGE.MSR_'||V_Nom_ID;
V_CLEF_VAR := ligne.CLEF_VAR;
V_nom_seq := 'SQ_MSRID_'||V_Nom_ID||'.NEXTVAL';
DBMS_OUTPUT.PUT_LINE( 'Données');
DBMS_OUTPUT.PUT_LINE( 'Date : ' || V_date1 );
DBMS_OUTPUT.PUT_LINE( 'Valeur : ' || V_valeur );
stmt := 'begin SELECT '||V_nom_seq||' into :n FROM DUAL; end;';
EXECUTE IMMEDIATE Stmt using out n;
stmt := 'INSERT INTO '||V_nom_table||' VALUES (:1, :2, :3, :4, :5, :6, :7)';
Execute immediate stmt USING n, V_DATE1, V_VALEUR, 0, '01/01/01', 0, 0;
end loop ;
END ;
/
EXIT
Error message :
ORA-01438: valeur incohérente avec la précision indiquée pour cette colonne
ORA-06512: à ligne 33
01438. 00000 - "value larger than specified precision allowed for this column"
*Cause: When inserting or updating records, a numeric value was entered
that exceeded the precision defined for the column.
*Action: Enter a value that complies with the numeric column's precision,
or use the MODIFY option with the ALTER TABLE command to expand
the precision.
I am using DB Oracle 12.1.
Regards.