Hours format in Oracle

0
votes

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

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

My table was constructed as follows:

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
/

I cannot modify the DATE

My form uses the folowing sources:

select 
"SCHEDULE_NUMBER",
"SCHEDULE_NUMBER" SCHEDULE_NUMBER_DISPLAY,
"MACHINE_NO",
"MACHINE_USE_DATE",
"START_TIME" as b,
TO_CHAR(b, 'HH24:MI:SS'),
"END_TIME",
"PROCESS",
"BATCH_NO",
"MATERIAL_BAR_NO",
"TECHNICIAN_STAFF_NO"
from "#OWNER#"."MACHINE_SCHEDULE"

However it gives the erorr:

"Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic columns'' checkbox below the region source to proceed without parsing. ORA-00904: "B": invalid identifier"

If I change the 'b' variable to START_TIME it gives the error:

query column #5 (TO_CHAR("START_TIME",'HH24:MI:SS')) is invalid, use column alias

3
sqloracleoracle-apextype-conversion
It tells you to give the column an alias, what happens when you do it ? ie TO_CHAR("START_TIME", 'HH24:MI:SS') as "ST", – A.B.Cade
Are you sure it's not the double quotes that are messing you up? – Dan Bracuk
@DanBracuk The double quotes are generated by Oracle when creating columns so I cannot change it, AFAIK. – Anish B
@AnishB You can take out the double quotes - if nothing else, it makes it cleaner to read. In fact, for anything other than simple statements, I always write them in Toad first then just paste them into APEX when I'm happy with them. – Paul Walker

3 Answers

0
votes

In addition:

-- This will never work --
SELECT SYSDATE as b, to_char(b, 'HH24:MI:SS') tme
 FROM dual
/

Output: ORA-00904: "B": invalid identifier


-- This will work --
SELECT to_char(b, 'HH24:MI:SS') tme
  FROM
 (
  SELECT SYSDATE as b
    FROM dual
 )
/

Output: 10:09:02
1
votes

When we use an expression in a select statement Oracle uses the expression to derive an identifier, because each column in the projection must have a unique name. Check the column headings in SQL*Plus to see thi sin action.

However some clients don't like these derived identifiers and want nice names which are compliant with Oracle's naming conventions. It appears Forms Builder is one such tool.

So that error message,

query column #5 (TO_CHAR("START_TIME",'HH24:MI:SS')) is invalid, use column alias

is telling you to give the expression an alias i.e. 

"START_TIME" ,
TO_CHAR("START_TIME", 'HH24:MI:SS') as b,

Only I suggest you use something more meaningful, such as START_TIME_FMT.

I cannot enter any detail in the new column. It is displayed as "NULL"

I pesume that behaviour is because it is a derived field. So what you need to do is remove it from the query and use a non-table item instead. Populate it with the Formatted START_TIME when you retrieve records from the database and use a trigger to copy its value to the (hidden) START_TIME column after insert or update.

0
votes

The double quotes round the field in the TO_CHAR are causing you problems. In fact, you don't need any of them. The query builder in APEX goes a bit mad with the double quotes!

Try this:

select 
SCHEDULE_NUMBER,
SCHEDULE_NUMBER SCHEDULE_NUMBER_DISPLAY,
MACHINE_NO,
MACHINE_USE_DATE,
START_TIME as b,
TO_CHAR(START_TIME,'HH24:MI:SS'),
END_TIME,
PROCESS,
BATCH_NO,
MATERIAL_BAR_NO,
TECHNICIAN_STAFF_NO
from #OWNER#.MACHINE_SCHEDULE