Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Timestamp database value is represented as invalid item data value

sgalaxyJan 12 2022

Hi all,
I developed a sample form in https://apex.oracle.com (apex version 21.2).
The form is based on a single db table containing a timestamp data column among the others.
Above the form region, i developed a region of type 'static content' consisting of some items (as a criteria region).
The purpose of these items is to set the values to the form region relative items so as the
'create' button to save the new record into the database.

In order to accomplish my purpose, i set up a dynamic action to the last item in the static content region.
The properties of this dynamic action is as follow:
when dynamic action = true, Set Value

  set type : sql statement  

affected items : the name of items in the form region which are hidden. Their values are are populated using the sql statement.
The sql statement is :
select CSC_CUST_CALC_KEY, PERIOD_ID, ACCT_COMPANY, CSCUSTM_CSBASIC_TS
from exp_ermh_csbasic_all csb
join h_timo tim
on tim.timo = csb.bs_rate
where CSC_CUST_CALC_KEY = :P2_1_CR
and period_id = :P2_2_CR
and bs_rate = :p2_3_CR;

All item values from the above sql statements are transferred correctly to the hidden data items in the form region (i confirmed that
using the web browser console, writing : apex.item("<item_name>").getValue()). The exception is the item 'CSCUSTM_CSBASIC_TS' (which is
a timestamp database column). The value written to the apex hidden form item is '12/07/2008 07:04:59,504116' instead of '12/7/2008 07:04:59,504116'.
The result is the error 'ORA - 01843: not a valid month' when trying to save the record in the database.
Is there any workaround?

Note: I defined the format mask for the timestamp item in form as: dd/mm/yyyy hh24:mi:ss,ff

Comments

Arpad Kiss

I think you wanted DESC, not DEPT.
DESC tablename;

Pictures are worth many words, show us

image.png

user527460

Hi, @thatjeffsmith-oracle ,
Here is the picture from SQL Developer version 21.4.3.x,
Picklist is not showing with table alias 'a' as shown here.

image.png

That query to get the column list might take a half-second, be patient
Windows 10/21.4.3
image.png

user527460

hi @thatjeffsmith-oracle ,
No luck, i waited for a min but pick list not showing. I tried to change theme to dark as showing in your image, still no pick list pops up.

ask for it multiple times, if the query takes too long, we give up

Robert Kilmer

Try Ctrl-Space.

670554

Ctrl-Space is not working in Version 22.2.1.234

it brings up a different menu.

thatJeffSmith-Oracle

23.1.1 is latest and this is your example in 23.1.1

1 - 9

Post Details

Added on Jan 12 2022
5 comments
166 views