a user can add multiple rows to a datasource set without committing each row. I only want to set the primary key when they press save and I have to update a few fields like date modified, modified by etc. So loop through the dataset would I have to to do something like remember what my current row is set current row to first row get row count of datasource for x in 1..rowcount loop if new procedure xxx if changed procedure yyy end loop; set current_row to remembered row

Am I trying to do more then necessary?

asked 16 Mar '14, 06:52

Raymond's gravatar image

accept rate: 0%

Hi Raymond,

If you update all rows and set the same value for the same fields, you can use achieve this through a single API call like:

api_datasource.setColumnValue('yourDatasource.dateModified', sysdate, api_datasource.ALL_ROWS);

The code above updates the value of the dateModified column for all rows existing in the datasource.

If you don't update all rows or the updated values are not the same for all the rows, it would be better to loop your datasource through api_datasource.getQuery or api_datasource.getRows APIs (you can find usage examples in API documentation) and set the column values using rowids, this way you don't need to change the current row of the datasource:

   v_rows_t api_datasource.tt_rows;
   v_row_id number;
   api_datasource.getRows('yourDatasource', v_rows_t);
   for i in 1..v_rows_t.count loop
     v_row_id := v_rows_t(i)('DS_ROWID').value_nr;
     api_datasource.setColumnValue('yourDatasource.someField', 'someValue', v_row_id);
   end loop;

Setting the column value using row_id prevents the need to change the current row and therefore remember the current row.

Hope this helps,


answered 20 Mar '14, 16:23

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
accept rate: 25%

Hi Ibrahim,

It seems your 2nd code updates all rows in the grid. What will happen if there are thousands of rows in the grid? Is there a way to fetch modified/inserted rows only or uncommitted rows only? I am looking for something like pre_update, pre_insert triggers of Oracle forms.



(22 Mar '14, 04:05) grajan777

Hi Raymond,

I would like to give my 2c on this. I always use views with instead of triggers when building data entry screens. If the issue is to just create default values for fields like created_by, creation_date and so on, then using a view with instead of triggers this issue is easy to solve. Just fill these values in the instead of trigger (and make these columns optional in the Formspider datasource definition).

Updating the ID (primary key) column i always do when i insert a row in the datasource. You would have to write code for that anyway, so after creating a row i immidiately update the ID with a number from a sequence. You cannot do that in the instead of trigger since the view PK column needs to be mandatory in the Formspider datasource definition, and when committing the datasource it will first check the mandatory columns.

Just my take on this...

Best regards,


answered 21 Mar '14, 07:11

Michiel%20A's gravatar image

Michiel A
accept rate: 13%

Hi All,

Thanks for your great explanation Michiel. I also recommend your approach if the issue is only about columns like created_by, creation_date..

Besides, it's possible to loop through only modified and new rows using api_datasource.getQuery API:

      type tt_rowids is table of number index by binary_integer;
      v_rowids_t tt_rowids;
      execute immediate
      'select ds_rowid
        from ' || api_datasource.getQuery('yourDatasource') ||'
        where (ds_rowStatus=:1 or ds_rowStatus=:2)'
      bulk collect into v_rowids_t
      using api_datasource.ROW_STATUS_NEW, api_datasource.ROW_STATUS_UPDATED;

      for i in 1..v_rowids_t.count loop
        api_datasource.setColumnValue('yourDatasource.someField', 'someValue', v_rowids_t(i));
      end loop;

Best Regards,


answered 22 Mar '14, 06:29

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
accept rate: 25%

edited 22 Mar '14, 06:31

Hi Ibrahim,

Thank you very much. Let me try this and God bless you.


(22 Mar '14, 07:28) grajan777

Thank you all for the reply and explanation

(25 Mar '14, 22:22) Raymond

Hi Ibrahim,

Thanks for your help. I have two questions in this regard.

  1. I need to process all the uncommitted datasources present in the form at the time of committing. For this I need the list of uncommitted datasources in the form. I am looking for something like api_application.getdirtydatasources. If not possible, is there any work around? With this I can get the same facility as in Oracle forms pre_insert and pre_update triggers where the table names are known and therefore it becomes easy to get the job done.

  2. How to get original row before any change was made? I need this to compare this with changed values for processing purpose.




answered 26 Mar '14, 00:35

George's gravatar image

accept rate: 0%

edited 26 Mar '14, 03:15

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 16 Mar '14, 06:52

Seen: 4,682 times

Last updated: 26 Mar '14, 03:15

© Copyright Gerger 2017