Search code examples
oracle-apex

Oracle APEX - Storing color picker value

I am trying to save the color picker value to a column.

The user can pick color for dashboard like this: enter image description here

I created a dynamic action for Save button and its action is Submit Page. I also created Process after submit to update the table. I set PL/SQL code like this:

UPDATE USR 
SET SETTING_PREF = '{"attention": &P7_ATTENTION., "property": &P7_PROPERTY., "not_reported": &P7_NOT_REPORTED., "off_property": &P7_OFF_PROPERTY., "carts_in_use": &P7_CARTS_IN_USE., "carts_idle": &P7_CARTS_IDLE., "show_item_num": &P7_SHOW_ITEM_NUM., "language": &P7_LANGUAGE.}'
WHERE EMAIL = :APP_USER;

But somehow, the values do not seem to be saved to the SETTING_PREF column. There is no error message after I click the save button, so I'm assuming that I am not retrieving the correct values from color picker section. Could anyone tell me how to solve this problem?

Solution

  • The &ITEM. notation cannot be used within pl/sql. Within pl/sql use the bind variable notation (:ITEM). Change the statement to (up to you to complete the json):

    UPDATE USR 
SET SETTING_PREF = apex_string.format('{"attention": %0,"property": %1, "not_reported": %2}', :P7_ATTENTION, :P7_PROPERTY, :P7_NOT_REPORTED)
WHERE EMAIL = :APP_USER;

    The api apex_string.format isn't needed but it avoids having to concat that long string with all the bind variables.

    Also... why the dynamic action ? Just define a page process with the when-button-pressed condition. Dynamic action is overkill.