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

    Hours format Datatype not working

    I am trying to make a form in oracle application express with a column called START_TIME and END_TIME.

    Now when I try to view the form I can only see it as NULL and cannot enter any HOURS or MINUTES.

    My table was constructed as follows:
    Code:
        CREATE TABLE  "MACHINE_SCHEDULE" 
           (	"SCHEDULE_NUMBER" VARCHAR2(100 CHAR), 
        	"MACHINE_NO" VARCHAR2(10 CHAR), 
        	"MACHINE_USE_DATE" DATE, 
        	"START_TIME" DATE, 
        	"END_TIME" DATE, 
        	"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
        /
    My form uses the folowing sources:

    Code:
        select 
        "SCHEDULE_NUMBER",
        "SCHEDULE_NUMBER" SCHEDULE_NUMBER_DISPLAY,
        "MACHINE_NO",
        "MACHINE_USE_DATE",
        TO_CHAR("START_TIME", 'HH24:MI:SS') as "START_TIME_FMT",
        "END_TIME",
        "PROCESS",
        "BATCH_NO",
        "MATERIAL_BAR_NO",
        "TECHNICIAN_STAFF_NO"
        from "#OWNER#"."MACHINE_SCHEDULE"
    However it gives the erorr:

    The error I receive is :Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-01400: cannot insert NULL into ("GROUP7_1"."MACHINE_SCHEDULE"."SCHEDULE_NUMBER "), insert into "GROUP7_1"."MACHINE_SCHEDULE" ( "SCHEDULE_NUMBER", "MACHINE_NO", "MACHINE_USE_DATE", "END_TIME", "PROCESS", "BATCH_NO", "MATERIAL_BAR_NO", "TECHNICIAN_STAFF_NO") values ( :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8)
    Last edited by MicroB; 02-19-13 at 08:56.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,005
    What do you call "oracle application builder"? I don't think that it exists. If it does, I never heard of it.

  3. #3
    Join Date
    Feb 2013
    Posts
    11
    Quote Originally Posted by Littlefoot View Post
    What do you call "oracle application builder"? I don't think that it exists. If it does, I never heard of it.
    Sorry meant application express

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,622
    > ALTER TRIGGER "BI_MACHINE_SCHEDULE" DISABLE

    Line above could affect what occurs & result in posted error being thrown.
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  5. #5
    Join Date
    Feb 2013
    Posts
    11
    Quote Originally Posted by anacedent View Post
    > ALTER TRIGGER "BI_MACHINE_SCHEDULE" DISABLE

    Line above could affect what occurs & result in posted error being thrown.
    I have done so but still get the same error.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,622
    I agree.
    if/when trigger is disabled & does not fire, SCHEDULE_NUMBER remains NULL & error results

    why do you have SCHEDULE_NUMBER & MACHINE_NO as VARCHAR2 & not NUMBER datatype?
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

Posting Permissions

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