1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Insert values into tables using pl/sql

Discussion in 'SQL PL/SQL' started by kamilia, Oct 10, 2016.

  1. kamilia

    kamilia Active Member

    Messages:
    21
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    algiers
    hi, everyone,

    I'm trying to insert values using this code pl/sql
    declare
    date_sousc date;
    montant number;
    code number;
    code_ass number;
    code_comp number;
    mat number;
    begin
    for code in 1..800000 loop
    select floor(dbms_random.value(1,200000))into code_ass from dual;
    select floor(dbms_random.value(1,20.2))into code_comp from dual;
    select floor(dbms_random.value(1,40000.2))into mat from dual;
    select trunc(dbms_random.value(5000,100000),2)into montant from dual;
    select to_date(trunc(dbms_random.value(to_char(date '2014-01-01','J'),to_char(date '2016-12-31','J')))
    ,'J')into date_sousc from dual;
    insert into contrat values(code,date_sousc,montant,code_ass,code_comp,mat);
    end loop;
    commit;
    end;
    /

    but i have this message error

    declare
    *
    ERREUR Ó la ligne 1 :
    ORA-01438: valeur incohÚrente avec la prÚcision indiquÚe pour cette colonne
    ORA-06512: Ó ligne 18

    so, i don't understand what's the problem.
    Thanks for any help.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Since you didn't provide a 'create table' statement and my generic declarations did NOT throw this error I can only presume you have length and precision declared for at least one of the NUMBER columns and you are exceeding that limitation with your decimal values.

    Provide the create table statement you used to create the table CONTRAT so my tests will reflect what you are using.
     
  3. kamilia

    kamilia Active Member

    Messages:
    21
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    algiers
    Code (Text):
    thanks, i've resolved the problem, it was about a declaration, i had to declare montant as real not as  a number(
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Interesting since I declared it as a number and did not generate that error. How do you have the column that value maps to declared in the CONTRAT table?
     
  5. kamilia

    kamilia Active Member

    Messages:
    21
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    algiers
    <code>First,I've used the type number(6,2)in the table contrat, but this type is not supported by pl/sql;
    so this is why I changed this type by real </code>
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, such declarations are supported by PL/SQL:

    Code (SQL):
    SQL> CREATE TABLE contrat(
      2  code       NUMBER,
      3  date_sousc DATE,
      4  montant NUMBER(7,1),
      5  code_ass NUMBER,
      6  code_comp NUMBER,
      7  mat NUMBER);

    TABLE created.

    SQL>
    SQL> DECLARE
      2  date_sousc DATE;
      3  montant NUMBER(7,1);
      4  code NUMBER;
      5  code_ass NUMBER;
      6  code_comp NUMBER;
      7  mat NUMBER;
      8  BEGIN
      9  FOR code IN 1..800000 loop
    10  SELECT FLOOR(dbms_random.VALUE(1,200000))INTO code_ass FROM dual;
    11  SELECT FLOOR(dbms_random.VALUE(1,20.2))INTO code_comp FROM dual;
    12  SELECT FLOOR(dbms_random.VALUE(1,40000.2))INTO mat FROM dual;
    13  SELECT trunc(dbms_random.VALUE(5000,100000),2)INTO montant FROM dual;
    14  SELECT to_date(trunc(dbms_random.VALUE(to_char(DATE '2014-01-01','J'),to_char(DATE '2016-12-31','J')))
    15  ,'J')INTO date_sousc FROM dual;
    16  -- dbms_output.put_line(code||'  '||date_sousc||'  '||montant||'  '||code_ass||'  '||code_comp||'  '||mat);
    17  INSERT INTO contrat VALUES(code,date_sousc,montant,code_ass,code_comp,mat);
    18  END loop;
    19  commit;
    20  END;
    21  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    The REAL definition sets the type to the equivalent of NUMBER(63,18) which fixed the precision error you were seeing. As you can see from the code above a declaration of NUMBER(7,1) is 'legal', as are any other valid variations of that type.