Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2007
    Posts
    12

    Unanswered: ORA-00947 and equal number of inserting values

    Hello everybody,

    I have a procedure which looks like this:

    procedure derive is
    cnt integer default 0;
    begin
    insert into DM.DMD_DERIVED (
    ID1_CIF,
    ID100_MESIAC,
    B5058_EMP_FL_Ma6,
    B5059_PB_FLA_Ma6,
    N5110_3A_PRODCELK,
    ....
    ....
    ) select /*+ LEADING(DMD_BASE)
    INDEX(D DMD_HIST_5000_5299_PK) USE_NL(D)
    INDEX(E DMD_HIST_5300_5399_PK) USE_NL(E)
    INDEX(F DMD_HIST_5400_5599_PK) USE_NL(F)
    INDEX(G DMD_HIST_5600_6599_PK) USE_NL(G)
    INDEX(H DMD_HIST_6600_7099_PK) USE_NL(H)
    INDEX(I DMD_HIST_7100_7399_PK) USE_NL(I)
    INDEX(J DMD_HIST_7400_7999_PK) USE_NL(J)
    */
    xx.ID1_CIF,
    xx.ID100_MESIAC,
    greatest(B5058_EMP_FLAG,l1_B5058_EMP_FLAG,l2_B5058 _EMP_FLAG,l3_B5058_EMP_FLAG,l4_B5058_EMP_FLAG,l5_B 5058_EMP_FLAG) B5058_EMP_FL_Ma6,
    greatest(B5059_PB_FLAG,l1_B5059_PB_FLAG,l2_B5059_P B_FLAG,l3_B5059_PB_FLAG,l4_B5059_PB_FLAG,l5_B5059_ PB_FLAG) B5059_PB_FLA_Ma6,
    (N5110_PRODCELK+L1_N5110_PRODCELK+L2_N5110_PRODCEL K)/3 N5110_3A_PRODCELK,
    ...
    ...
    from
    DM.DMD_HIST_5000_5299 D,
    DM.DMD_HIST_5300_5399 E,
    DM.DMD_HIST_5400_5599 F,
    DM.DMD_HIST_5600_6599 G,
    DM.DMD_HIST_6600_7099 H,
    DM.DMD_HIST_7100_7399 I,
    DM.DMD_HIST_7400_7999 J,
    DM.DMD_BASE xx
    where
    xx.id1_cif = D.id1_cif
    and xx.id1_cif = E.id1_cif
    and xx.id1_cif = F.id1_cif
    and xx.id1_cif = G.id1_cif
    and xx.id1_cif = H.id1_cif
    and xx.id1_cif = I.id1_cif
    and xx.id1_cif = J.id1_cif
    ;


    In spite of being absolutely sure, that I'm inserting equal number of values as I'm selecting (735), I got this error:

    PL/SQL: ORA-00947: not enough values

    Is there any possible (GRANTs on tables, which I'm selecting from or anything like that) reason for this error? Can somebody help me ??

    Thank you very much and sorry for my poor english :-(

    Lubo

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by lubogabal
    In spite of being absolutely sure, that I'm inserting equal number of values as I'm selecting (735), I got this error:

    PL/SQL: ORA-00947: not enough values
    I still rather believe Oracle than you.

    735 (seven hundred thirty-five) columns! Gosh! Lucky you, you've got the whole weekend to find that missing value!

  3. #3
    Join Date
    Sep 2007
    Posts
    12
    I, as well as you, believe Oracle rather then myself :-)), but it is automatically generated code, which I was able to compile in one schema (the same code), but not in another schema - because of previously posted error.

    Anyway ... I checked the number of rows "manually" several times (check of metadata on which is generated code based) and number of rows generated as well ... I couldn't find any reason for this error ...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It looks as if table 'dmd_derived' differs between those schemas.

    If table is simply ALTERED in order to add another column, Oracle will place it at the end of column list. Could you identify this (those) column(s) that is missing by this simple examination?

  5. #5
    Join Date
    Sep 2007
    Posts
    12
    Quote Originally Posted by Littlefoot
    It looks as if table 'dmd_derived' differs between those schemas.

    If table is simply ALTERED in order to add another column, Oracle will place it at the end of column list. Could you identify this (those) column(s) that is missing by this simple examination?
    1) select count(*) from all_tab_cols
    2) where owner = 'DM'
    3) and table_name = 'DMD_DERIVED';

    unfortunatelly returns 735 as well :-(. I did not altered this table for sure, I'm really hopeless...

    I will have to check those columns manually I'm affraid :-(.

    But thank you very much for your interest anyway...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In that case, you didn't provide enough values in the SELECT column / values list. If it was a simple case, it would look like this:
    Code:
    SQL> create table dmd_derived (id number, name varchar2(20));
    
    Table created.
    
    SQL> insert into dmd_derived
      2    (id, name)
      3    (select 1
      4     from dual
      5    );
    insert into dmd_derived
                *
    ERROR at line 1:
    ORA-00947: not enough values
    
    
    SQL>
    Unfortunately, your selectable column list isn't that simple. You use functions (as GREATEST; DECODE might look even worse) and I really wouldn't know how to simplify error searching process.

  7. #7
    Join Date
    Sep 2007
    Posts
    12

    final solution

    Quote Originally Posted by Littlefoot
    In that case, you didn't provide enough values in the SELECT column / values list. If it was a simple case, it would look like this:
    Code:
    SQL> create table dmd_derived (id number, name varchar2(20));
    
    Table created.
    
    SQL> insert into dmd_derived
      2    (id, name)
      3    (select 1
      4     from dual
      5    );
    insert into dmd_derived
                *
    ERROR at line 1:
    ORA-00947: not enough values
    
    
    SQL>
    Unfortunately, your selectable column list isn't that simple. You use functions (as GREATEST; DECODE might look even worse) and I really wouldn't know how to simplify error searching process.
    Thank you very much for helping me. Searching for error really wasn't simple, in spite of the fact, that error was really simple. I don't know whether you are interested in solution, but ...

    I had expressions like next one there:

    (5*N6200_1m_TrTB+3*l1_N6200_1m_TrTB+l2_N6200_1m_Tr TB-l3_N6200_1m_TrTB-3*l4_N6200_1m_TrTB-5*l5_N6200_1m_TrTB)/35 N6200_1m_TrT_T6 ...

    The solution was to put spaces between attribute names and operator - to replace '+' with ' + ' and so on ... It was that simple ... :-( And that .... stupid ... :-(

    Thank you again for help ...

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Thank you for the feedback!

    How did you find that
    Code:
    5*N6200_1m_TrTB+3*l1_N6200_1m_TrTB
    was supposed to be
    Code:
    5 * N6200_1m_TrTB + 3 * l1_N6200_1m_TrTB
    As far as I know, Oracle doesn't care about that.
    Code:
    2+2 is equal to 2 + 2
    No difference at all.

  9. #9
    Join Date
    Sep 2007
    Posts
    12
    Quote Originally Posted by Littlefoot
    No difference at all.
    Actually, I don't think it was problem with those operators themselves - I guess it was something with word-wrapping - sqlplus did not encoded it properly. I had a really long expressions there and that could be the case ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •