5 Replies Latest reply: Mar 28, 2013 7:55 AM by David_Pasternak RSS

    the classical auto_increment Problem

      Hi @ everybody,

      analog to another thread of me i'm trying to define a sequence and a trigger for a table, where i want to import data from MySQL and that will be used by a programm internally of us to insert more data.

      At MySQL side there is one field, the RecNR field, defined as auto_increment. So i want to define a sequence and a trigger at Oracle side to realize that. Here are my Scripts for that:

      the Table:
      CREATE TABLE AccountingPrimary
        JobID varchar(16) NULL,
        Status char(2) NULL,
        Year varchar(4) NULL,
        Month char(2) NULL,
        Day char(2) NULL,
        Sender varchar(16) NULL,
        ObjectClass varchar(16) NULL,
        Receiver varchar(16) NULL,
        JobName varchar(16) NULL,
        ProcessingTimeComplete varchar(16) NULL,
        ExecutionTimeOfAll varchar(16) NULL,
        NoOfAllExecutedFunctionCalls varchar(16) NULL,
        NoOfAllIncorrectFunctionCalls varchar(16) NULL,
        ObjectID varchar(16) NULL,
        NoOfComponents varchar(16) NULL,
        AccumulatedCompSize varchar(16) NULL,
        SizeOfEachComp varchar(128) NULL,
        YearMonth varchar(6) NULL,
      The Sequence:
      CREATE SEQUENCE AccountingPrimary_counter
      increment BY 1 start WITH 1 nomaxvalue;
      And the trigger:
      CREATE TRIGGER AccPriAI_Trigger BEFORE INSERT ON AccountingPrimary
      SELECT AccountingPrimary_counter.NEXTVAL INTO :NEW.RecNR FROM DUAL;
      Now, when im inserting something into the table for testing that trigger, Oracle gaves me errors. By the way, i'm using SQL Developer:
      INSERT INTO AccountingPrimary
      Fehler beim Start in Zeile 42 in Befehl:
      INSERT INTO AccountingPrimary
      Fehler bei Befehlszeile:43 Spalte:129
      SQL-Fehler: ORA-00984: Spalte hier nicht zulässig
      00984. 00000 -  "column not allowed here"
      or that way, without the "1" at the and for the RecNR field:
      -- zum Test Daten einfügen
      INSERT INTO AccountingPrimary
      Fehler beim Start in Zeile 42 in Befehl:
      INSERT INTO AccountingPrimary
      Fehler bei Befehlszeile:42 Spalte:13
      SQL-Fehler: ORA-00947: Anzahl der Werte reicht nicht aus
      00947. 00000 -  "not enough values"
      It should be very simple, what i'm doing wrong, but i can't see my fail. Please help!

      Thanks a lot and regards,
        • 1. Re: the classical auto_increment Problem
          Paul M.
          INSERT INTO AccountingPrimary
          Try removing quotes for last value :
          Edited by: Paul M. on Mar 28, 2013 12:50 PM

          Of course it was

          • 2. Re: the classical auto_increment Problem
            First off, a string in Oracle is delimited by single quotes not double quotes. And if you are inserting a number, you'd want to insert a number not a string.
            INSERT INTO table_name( <<list of columns>> )
              VALUES( '1', 'toll', '2013', ... );
            Since you aren't listing the column names in your INSERT statement (not a good idea), you would need to specify the values in the order the columns are listed in the table and you would have to have a value for every column. That probably isn't what you want if your intention is to omit the RecNR column in your INSERT statement. So you almost certainly need to list the columns
            INSERT INTO AccountingPrimary( jobID, status, year, ... )
              VALUES( '1', 'toll', '2013', ... );
            It's not obvious to me why you are storing year, month, and day in separate columns and as strings rather than storing a date or at least storing the components as numbers. But that is a separate data model discussion.

            • 3. Re: the classical auto_increment Problem
              Paul M.
              a string in Oracle is delimited by single quotes not double quotes.
              Right Justin :-)
              • 4. Re: the classical auto_increment Problem
                Do not use double quotes.

                SQL> create table footab( id integer, name varchar2(10) );
                Table created.
                SQL> insert into footab values( "1", "test" );
                insert into footab values( "1", "test" )
                ERROR at line 1:
                ORA-00984: column not allowed here
                SQL> insert into footab values( 1, 'test' );
                1 row created.
                • 5. Re: the classical auto_increment Problem
                  Why a numer is inserting as a string i can't tell. I didn't designed the table at MySQL. It would have his sence, but whitch i can't tell. I just redesinged the table at Oracle side like it is at MySQL side to have no importing errors.

                  EDIT: I've just redesigned the Insert command. Sorry for wasting time with that, that was a really stupid error of mine ;)

                  Thanks a lot for help!

                  Edited by: David_Pasternak on 28.03.2013 05:54