This discussion is archived
5 Replies Latest reply: Mar 28, 2013 5:55 AM by David_Pasternak RSS

the classical auto_increment Problem

David_Pasternak Newbie
Currently Being Moderated
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,
  RecNR int NOT NULL PRIMARY KEY
)
TABLESPACE DUPONT_STAT;
The Sequence:
CREATE SEQUENCE AccountingPrimary_counter
increment BY 1 start WITH 1 nomaxvalue;
And the trigger:
CREATE TRIGGER AccPriAI_Trigger BEFORE INSERT ON AccountingPrimary
FOR EACH ROW
Begin
SELECT AccountingPrimary_counter.NEXTVAL INTO :NEW.RecNR FROM DUAL;
End;
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
VALUES("1","toll","2013","03","13","test","test","test","test","bla","blub","dings","bums","51745","345","dingsda","5","201303","1");

Fehler beim Start in Zeile 42 in Befehl:
INSERT INTO AccountingPrimary
VALUES("1","toll","2013","03","13","test","test","test","test","bla","blub","dings","bums","51745","345","dingsda","5","201303","1")
Fehler bei Befehlszeile:43 Spalte:129
Fehlerbericht:
SQL-Fehler: ORA-00984: Spalte hier nicht zulässig
00984. 00000 -  "column not allowed here"
*Cause:    
*Action:
or that way, without the "1" at the and for the RecNR field:
-- zum Test Daten einfügen
INSERT INTO AccountingPrimary
VALUES("1","toll","2013","03","13","test","test","test","test","bla","blub","dings","bums","51745","345","dingsda","5","201303");

Fehler beim Start in Zeile 42 in Befehl:
INSERT INTO AccountingPrimary
VALUES("1","toll","2013","03","13","test","test","test","test","bla","blub","dings","bums","51745","345","dingsda","5","201303")
Fehler bei Befehlszeile:42 Spalte:13
Fehlerbericht:
SQL-Fehler: ORA-00947: Anzahl der Werte reicht nicht aus
00947. 00000 -  "not enough values"
*Cause:    
*Action:
It should be very simple, what i'm doing wrong, but i can't see my fail. Please help!

Thanks a lot and regards,
David
  • 1. Re: the classical auto_increment Problem
    Paul M. Oracle ACE
    Currently Being Moderated
    INSERT INTO AccountingPrimary
    VALUES("1","toll","2013","03","13","test","test","test","test","bla","blub","dings","bums","51745","345","dingsda","5","201303","1");
    Try removing quotes for last value :
    VALUES("1","toll","2013","03","13","test","test","test","test","bla","blub","dings","bums","51745","345","dingsda","5","201303",1);
    Edited by: Paul M. on Mar 28, 2013 12:50 PM

    Of course it was

    VALUES('1','toll','2013','03','13','test','test','test','test','bla','blub','dings','bums','51745','345','dingsda','5','201303',1);
  • 2. Re: the classical auto_increment Problem
    Justin Cave Oracle ACE
    Currently Being Moderated
    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.

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

    SQL> create table footab( id integer, name varchar2(10) );
    
    Table created.
    
    SQL> 
    SQL> insert into footab values( "1", "test" );
    insert into footab values( "1", "test" )
                                    *
    ERROR at line 1:
    ORA-00984: column not allowed here
    
    
    SQL> 
    SQL> insert into footab values( 1, 'test' );
    
    1 row created.
    
    SQL> 
  • 5. Re: the classical auto_increment Problem
    David_Pasternak Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points