Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    11

    Unanswered: Error in mru internal routine: ORA-20001 And more

    Thanks so far guys,

    I feel like I am getting further and further with your help firstly!

    However, I've got another issue now when I am using the form wizard provided by oracle.

    The issue I have is when following the wizard it asks to set up a primary key column, but once I have I cannot add ROWS to it.

    To get an idea of what I mean here is the example:
    http://i49.tinypic.com/2hq6opg.jpg

    The schedule number cannot be added.

    Here is the SQL for the table:
    Code:
    CREATE TABLE  "MACHINE_SCHEDULE" 
       (	"SCHEDULE_NUMBER" VARCHAR2(40 CHAR), 
    	"MACHINE_NO" VARCHAR2(10 CHAR), 
    	"MACHINE_USE_DATE" DATE, 
    	"START_TIME" TIMESTAMP (3) WITH TIME ZONE, 
    	"END_TIME" TIMESTAMP (3) WITH TIME ZONE, 
    	"PROCESS" VARCHAR2(164 CHAR), 
    	"BATCH_NO" VARCHAR2(10 CHAR), 
    	"MATERIAL_BAR_NO" NUMBER(8,0), 
    	"TECHNICIAN_STAFF_NO" VARCHAR2(15 CHAR), 
    	 CONSTRAINT "MACHINE_SCHEDULE_PK" PRIMARY KEY ("SCHEDULE_NUMBER") ENABLE
       )
    /
    ALTER TABLE  "MACHINE_SCHEDULE" ADD CONSTRAINT "MACHINE_SCHEDULE_CON" FOREIGN KEY ("TECHNICIAN_STAFF_NO")
    	  REFERENCES  "MODULE_TECHNICIAN" ("TECHNICIAN_STAFF_NO") ENABLE
    /
    ALTER TABLE  "MACHINE_SCHEDULE" ADD CONSTRAINT "MACHINE_SCHEDULE_FK" FOREIGN KEY ("BATCH_NO")
    	  REFERENCES  "BATCH" ("BATCH_NO") ENABLE
    /
    ALTER TABLE  "MACHINE_SCHEDULE" ADD CONSTRAINT "MACHINE_SCHEDULE_FK2" FOREIGN KEY ("MATERIAL_BAR_NO")
    	  REFERENCES  "MATERIAL_BAR" ("MATERIAL_BAR_NO") ENABLE
    /
    
    CREATE OR REPLACE TRIGGER  "BI_MACHINE_SCHEDULE" 
      before insert on "MACHINE_SCHEDULE"               
      for each row  
    begin   
      if :NEW."SCHEDULE_INSTANT_NUMBER" is null then 
        select "MACHINE_SCHEDULE_SEQ".nextval into :NEW."SCHEDULE_INSTANT_NUMBER" from dual; 
      end if; 
    end; 
    
    /
    ALTER TRIGGER  "BI_MACHINE_SCHEDULE" DISABLE
    /

    However, when I am trying to add rows into the table I get the error:

    Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-01438: value larger than specified precision allowed for this column, insert into "GROUP7_1"."MACHINE_SCHEDULE" ( "SCHEDULE_NUMBER", "MACHINE_NO", "MACHINE_USE_DATE", "START_TIME", "END_TIME", "PROCESS", "BATCH_NO", "MATERIAL_BAR_NO", "TECHNICIAN_STAFF_NO") values ( :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9)
    Finally, I understand timestamps can be changed by having a to_char method so I can have just the HOURS displaying however it seems this option is not available and only TIME can be displayed.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    CHAR datatype should NEVER be used.

    if "SCHEDULE_NUMBER" really is a NUMBER, then it should NOT be established as a string.

    Code:
    01438, 00000, "value larger than specified precision allowed for this column"
    // *Cause: When inserting or updating records, a numeric value was entered 
    //         that exceeded the precision defined for the column.
    // *Action: Enter a value that complies with the numeric column's precision,
    //          or use the MODIFY option with the ALTER TABLE command to expand
    //          the precision.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2013
    Posts
    11
    Quote Originally Posted by anacedent View Post
    CHAR datatype should NEVER be used.

    if "SCHEDULE_NUMBER" really is a NUMBER, then it should NOT be established as a string.

    Code:
    01438, 00000, "value larger than specified precision allowed for this column"
    // *Cause: When inserting or updating records, a numeric value was entered 
    //         that exceeded the precision defined for the column.
    // *Action: Enter a value that complies with the numeric column's precision,
    //          or use the MODIFY option with the ALTER TABLE command to expand
    //          the precision.

    Thanks, also just wondering how did you manage to get such an explanation from Oracle?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    All *NIX Oracle versions include a utility called "oerr" which produces like below:

    Code:
    [oracle@localhost ~]$ oerr ora 60
    00060, 00000, "deadlock detected while waiting for resource"
    // *Cause:  Transactions deadlocked one another while waiting for resources.
    // *Action: Look at the trace file to see the transactions and resources
    //          involved. Retry if necessary.
    [oracle@localhost ~]$ oerr ora 1
    00001, 00000, "unique constraint (%s.%s) violated"
    // *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
    //         For Trusted Oracle configured in DBMS MAC mode, you may see
    //         this message if a duplicate entry exists at a different level.
    // *Action: Either remove the unique restriction or do not insert the key.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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