Update using date picker not working?

680183

Hello,

This might be simple for a lot of people here but I can't get it going...

The problem is that, I have a tabular form with 1 text field and 1 date field. I am using a PL/SQL anonymous block transaction to insert/update a view based on the field values. Insert is fine but update on the date field is not working - but no errors. The date field is a date picker('DD-MON-YYYY') column. Kindly help check the problem. Thank you.

Below are some codes that might be helpful

-- update statement in the block
update  del_ignore_conchold_v
set     conc_prog_name = apex_application.g_f03(v_row)
       ,remove_entry_date = apex_application.g_f04(v_row) 
where   row_id = apex_application.g_f02(v_row);

-- update trigger on the view
CREATE OR REPLACE TRIGGER APPS_TEST.del_ignore_conchold_v_upd_tr
   instead of update
   ON APPS_TEST.DEL_IGNORE_CONCHOLD_V referencing new as new old as old
   for each row
begin
   update [email protected]
      set conc_prog_name = :new.conc_prog_name,
          remove_entry_date = :new.remove_entry_date
    where conc_prog_name = :old.conc_prog_name
    and   remove_entry_date = :old.remove_entry_date;
end;

Thanks,
Rownald

ATD

Hi,

The value of apex_application.g_f04(v_row) would actually be a string not a date, so you would need to use TO_DATE(apex_application.g_f04(v_row),'DD-MON-YYYY') to convert it to a date.

Andy

680183

Hi Andy,

Thanks for the reply,.. I tried that and did not work. I am not sure if it's on my Apex code or my trigger since I tried executing and update statement outside(using toad) with date value as '12-OCT-2009' and works. Any other hint?

Thanks,
Rownald

ATD

Hi,

OK - I would still leave the TO_DATE() in there, though.

One thing you could check:

where   row_id = apex_application.g_f02(v_row);

This is used to determine what record(s) to update. If there are no records that match this, then no exception would be thrown as it is not an error as such.

If you added in:

wwv_flow.debug('f02=' || apex_application.g_f02(v_row));

immediately after the row that determines v_row, and run the page in debug mode, what message do you see in the debug output? Is that the value that you expected it to be?

Andy

680183

Hello,

I put a debug display before and after the update statement and I got 'AAE2DlAHKAAAA0qAAB', which is the row_id in value in the view.

Thanks,
Rownald

ATD

Hi,

You could try the outputting debug messages for apex_application.g_f03(v_row) and apex_application.g_f04(v_row)

Andy

680183

Hello,

The output for g_f03(v_row) and g_f04(v_row) appear to be fine. g_f03 only has the actual text and g_f04 has the date(from date picker field) with format like 22-OCT-2009. Below is an excerpt of the code & debug log - might be of help.

Thanks as always.

        if apex_application.g_f02(v_row) is not null then
            wwv_flow.debug('MY PROCESS: g_f02 ' || apex_application.g_f02(v_row)); 
            wwv_flow.debug('MY PROCESS: g_f03 ' || apex_application.g_f03(v_row));
            wwv_flow.debug('MY PROCESS: g_f04 ' || apex_application.g_f04(v_row));
            -- update stmt
            update  del_ignore_conchold_v
            set     conc_prog_name = apex_application.g_f03(v_row)
                   ,remove_entry_date = apex_application.g_f04(v_row) 
            where   row_id = apex_application.g_f02(v_row);
            wwv_flow.debug('MY PROCESS: g_f03 ' || apex_application.g_f03(v_row));
            wwv_flow.debug('MY PROCESS: g_f04 ' || apex_application.g_f04(v_row));

 -- debug log -- 
0.09: ...Do not run process "AddRows", process point=AFTER_SUBMIT, condition type=, when button pressed=ADD
0.09: ...Process "Save_Transaction": PLSQL (AFTER_SUBMIT) declare v_row binary_integer; begin for i in 1..apex_application.g_f01.count loop v_row := apex_application.g_f01(i); if apex_application.g_f02(v_row) is not null then wwv_flow.debug('MY PROCESS: g_f02 ' || apex_appli
0.09: MY PROCESS: g_f02 AAE2DlAHKAAAA0qAAB
0.10: MY PROCESS: g_f03 Delete data from temporary table
0.10: MY PROCESS: g_f04 22-OCT-2009
0.11: MY PROCESS: g_f03 Delete data from temporary table
0.11: MY PROCESS: g_f04 22-OCT-2009
0.15: ...Process "Reset Pagination": RESET_PAGINATION (AFTER_SUBMIT) reset_pagination
0.15: Branch point: AFTER_PROCESSING

Rownald

ATD

Hi,

Firstly, I would still use TO_DATE():

,remove_entry_date = TO_DATE(apex_application.g_f04(v_row),'DD-MON-YYYY')

If I read the rest of your code correctly, this is being updated on a remote table? If so, the date format being used there may not match the one in your database, so you would need to explicitly convert the string into a date.

Secondly, as the last two debug messages are being output, that would seem to indicate that no errors are being returned to the code. As the code only updates the same fields that the INSTEAD OF trigger itself would update, have you tried updating directly on [email protected]?

Andy

680183

Hi Andy,..

Yes, it's doing an update on a remote table thru a view (via db link)... we should be getting near...

BTW, I tried updating directly on the view and on the table - works okay.

Thanks,
Rownald

ATD

Hi,

When you updated "directly" was that in SQL Commands (or something similar) ? I would try doing it in your PL/SQL code instead of updating the view.

Andy