Results 1 to 15 of 15

Thread: Ora-01400

  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: Ora-01400

    Ok,

    Let say I have a table called db. I want to insert values into the table but I am getting an ora-01400 error. I understand that my primary key is not null but the existing_product_id should auto generate a value kind of like a session id.

    CREATE TABLE DB
    (
    EXISTING_PRODUCT_ID NUMBER(12) NOT NULL,
    CUSTOMER_ID NUMBER(9) NULL,
    DIRECTORY_CODE VARCHAR2(6) NULL,
    ISSUE_NUMBER NUMBER(3) NULL,
    ITEM_ID VARCHAR2(9) NULL,
    MARKET_CODE VARCHAR2(2) NULL,
    HEADING_CODE VARCHAR2(6) NULL,
    UDAC VARCHAR2(10) NULL,
    BASE_UDAC VARCHAR2(6) NULL,
    IMV_LISTING_ID VARCHAR2(9) NULL,
    LIST_GRAPHIC VARCHAR2(510) NULL,
    SPECIAL_INSTRUCTIONS VARCHAR2(510) NULL,
    LISTED_NAME VARCHAR2(250) NULL,
    LISTED_PHONE_NUMBER VARCHAR2(10) NULL,
    LISTED_ADDRESS_STREET VARCHAR2(250) NULL,
    LISTED_ADDRESS_CITY VARCHAR2(245) NULL,
    LISTED_ADDRESS_STATE VARCHAR2(2) NULL,
    LISTED_ADDRESS_ZIP_CODE VARCHAR2(9) NULL,
    LISTED_ADDRESS_PO_BOX VARCHAR2(20) NULL,
    CONTACT_NAME VARCHAR2(30) NULL,
    CONTACT_PHONE_NUMBER VARCHAR2(10) NULL,
    CONTACT_FAX_NUMBER VARCHAR2(10) NULL,
    CONTACT_EMAIL_ADDRESS VARCHAR2(100) NULL,
    SALES_REP_CODE VARCHAR2(6) NULL,
    SALES_REP_NAME VARCHAR2(60) NULL,
    SALES_REP_PHONE VARCHAR2(10) NULL,
    SALES_REP_EMAIL VARCHAR2(100) NULL,
    SALES_REP_CHANNEL VARCHAR2(20) NULL,
    SALES_DATE DATE NULL,
    REQUESTED_START_DATE DATE NULL,
    CREATION_DATE DATE NULL,
    LAST_UPDATE_DATE DATE NULL,
    DELETED CHAR(1) DEFAULT 'N' NULL
    )
    TABLESPACE DB
    NOLOGGING
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    STORAGE(BUFFER_POOL DEFAULT)
    NOPARALLEL
    NOCACHE
    /
    ALTER TABLE DB
    ADD CONSTRAINT PK_DB PRIMARY KEY (EXISTING_PRODUCT_ID)
    USING INDEX TABLESPACE DB_IDX
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE(BUFFER_POOL DEFAULT)
    LOGGING
    ENABLE
    VALIDATE


    Now here is the insert statement:

    insert into db (customer_id, directory_code, issue_number, item_id, market_code, heading_code, udac,
    base_udac, imv_listing_id, list_graphic, special_instructions, listed_name, listed_phone_number, listed_address_street,
    listed_address_city, listed_address_state, listed_address_zip_code, listed_address_po_box, contact_name, contact_phone_number,
    contact_fax_number, contact_email_address, sales_rep_code, sales_rep_name, sales_rep_phone, sales_rep_email, sales_rep_channel,
    sales_date, requested_start_date, creation_date, last_update_date, deleted)
    values(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27,'15-Nov-04', '15-Nov-04', '15-Nov-04', '15-Nov-04', 'N');

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    the existing_product_id should auto generate a value
    You would need a row-level BEFORE INSERT trigger. I don't see one...

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use a sequence to generate the ID values:
    Code:
    create sequence db_seq;
    Then you can either include a call to the sequence generator in your insert statement, or create a BEFORE INSERT trigger to do it:

    1)
    Code:
    insert into db (existing_product_id, ...) values (db_seq.NEXTVAL, ...);
    2)
    Code:
    create or replace trigger db_bir
    before insert on db
    for each row
    when (new.existing_product_id is null)
    begin
      select db_seq.nextval into :new.existing_product_id from dual;
    end;
    /
    (In this case you can omit the column from the insert statement.)

  4. #4
    Join Date
    Apr 2004
    Posts
    113
    thanks guys.

    I already figured this one out all by myself. Your answers were correct.
    I assumed the developer already had these in place.

    I am learning thanks.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps just a note ... I'd say it would be better if you use a sequence as Tony told you in his first example instead of writing a database trigger as the trigger causes overhead (and you'd better avoid that).

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Littlefoot,

    I think you need to provide some proof of actual overhead against your proof of the real worlds problems of fighting application complexity (forgotten complex insert/update propagation), forgotten sequence retrieval etc before making a statement like that.

    I think Oracle Corp would be interested in your real world figures too.

    Otherwise it's just FUD.

    Hth
    Bill
    Last edited by billm; 12-08-04 at 00:24.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hello to you too, Bill!

    It always amazes me seeing you, english native speaking people, to use abbreviations I never heard of before. Tried to figure that one out, and found this: FUD was first defined by Gene Amdahl after he left IBM to found his own company, Amdahl: "FUD is the fear, uncertainty, and doubt that IBM sales people instill in the minds of potential customers who might be considering Amdahl products."

    Now that I've worked that out, with a great relief in my soul, I can't offer you much about my note. Don't have a trace file, statistics ... nothing. It just rang a bell in my mind about similar problem I had some time ago working on a project that requested massive insert and setting some kind of a sequence into one field. It was done by one of my colleagues and me.

    My first solution was writing a database trigger which fired on every inserted row. It worked just fine during test phase (as I did it with not more than a few dozens of records). But first real situation caused not overhead, but headache at a colleague of mine. His part of the project worked fine without my trigger, but - the whole thing needed both procedures.

    Therefore I disabled the trigger and made a procedure that updated records after they've been inserted. Headache was gone, time needed for the job to be done was shortened, and we were satisfied with that solution.

    That's how my little left foot found out that using triggers (having a LOT of data involved) instead of something else slows things down.

    I'm afraid Oracle Corp. won't pay a dime for that

    Kindest regards!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by billm
    Hi Littlefoot,

    I think you need to provide some proof of actual overhead against your proof of the real worlds problems of fighting application complexity (forgotten complex insert/update propagation), forgotten sequence retrieval etc before making a statement like that.

    I think Oracle Corp would be interested in your real world figures too.

    Otherwise it's just FUD.
    Well, in this case if it is FUD it is FUD shared by Tom Kyte! I don't have any concrete figures either, and I certainly often do use triggers to populate columns from sequences. But it seems clear there will be some overhead using triggers, since Oracle has to retrieve the trigger definition and run it in addition to retrieving the next sequence value.

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Chaps,

    Read my post again, of actual overhead against your proof of the real worlds problems of fighting application complexity (forgotten complex insert/update propagation). I do not relish the idea of having the data rules scattered around client apps and or pl/sql packages. Scale that up to a 1,000,000+ line client application and you see the problem.

    The statement as the trigger causes overhead (and you'd better avoid that). is what I object to. An all encompassing statement like that is not good for anybody.

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by billm
    The statement as the trigger causes overhead (and you'd better avoid that). is what I object to.
    So, triggers don't cause overhead?

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    it all depends on what the trigger is doing.
    are you reinventing the WHEEL by writing a trigger that Oracle already has
    developed an answer for? IE: Oracle Auditing vs. writing triggers to audit
    your database
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    There is inevitably some overhead in calling a trigger for each row inserted, that SELECTs someseq.NEXTVAL INTO :NEW.id, and perhaps in a datawarehouse load for millions of records you might be better off including the logic in the INSERT query (and resetting the sequence cache to 10000 or so while you're at it, as the default is just 20).

    However, just in case newbie2004 does NOT happen to be working in a datawarehouse bulk load environment, the overhead is likely to be modest, and think of the advantages of having the PK column set itself automatically whatever method is used to populate the table.

    I would certainly not recommend bulk-loading the table with missing keys and then updating in a separate step.

  13. #13
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Triggers have overhead. The suggestion that all triggers should be avoided because they have overhead is what I was objecting to.

    Can you imagine the resulting client code, applications etc?

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I sincerely apologize for causing such a mess here ... honestly, I never thought that triggers should be avoided generally; I guess Oracle corp. would have kicked them out long time ago if they were useless.

    On the other hand, I'm very glad as I've heard several opinions about this issue. I learn something new every time I come here, whether just reading or - sometimes - trying to help someone solve his/her problem (as Tony once said, that's a great way to learn more about Oracle).

    I found this forum a nice place to hang around and feel as if I knew some of you personally (which I don't). Although I think arguing can't be harmful, I'd be really sorry if any of you found this discussion uncomfortable in any way.

    Thank you. Play ball

  15. #15
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Littlefoot,

    I don't think it's a mess, I think it has got people thinking about exactly what trigger overhead there is. When triggers are appropriate and when they aren't. There is no need to apologise.

    I am sorry if my reply sounded aggresive, it was not intended to be. Maybe I wasn't particularly clear on where/why I objected. That is my fault. I will think more next time.

    I would hope that people on the forum would consider my typical contributions to be usually helpful, occassionally blunt, but very rarely aggresive.

    The main benefit of this forum for me, is the different ideas, different approaches and different experiences. Sometimes they will result in extremes of opinion, but they are all useful. Any detail can be found in Oracle's documentation, experiences require us to talk.

    Kind Regards
    Bill

    P.S. Sent you a PM (apology) about my assumptions of English abbreviations :-)
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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