Hi, Is there any way to obtain only the rows that had changed in a datasource without loop over all rows?

asked 25 Feb '13, 11:56

Jorge%20Alonso's gravatar image

Jorge Alonso
112
accept rate: 0%


Hi Jorge,

You can use api_datasource.getQuery API to get a subset of the datasource row set. Note that Formspider adds two additional columns to the SQL retrieved with this API:

DS_ROWID: Row's framework assigned datasource row ID
DS_ROWSTATUS: Row's current DML status. Valid values are:

  • api_datasource.ROW_STATUS_DELETED : Deleted row
  • api_datasource.ROW_STATUS_TEMP: New row which is not modified yet
  • api_datasource.ROW_STATUS_NEW: New row which is modified
  • api_datasource.ROW_STATUS_UPDATED: Updated row
  • NULL: The row is just queried from the database and no update or delete operation is preformed yet

In your case the subset will be the changed rows, which can be found by filtering the DS_ROWSTATUS column. For example, you can obtain only the updated rows of a datasource named "EMPLOYEES1" with the following code snippet:

declare
  type t_emp is record (employee_id employees.employee_id%type, first_name employees.first_name%type, ds_rowid number);
  type tt_emp is table of t_emp index by binary_integer;
  v_emp_t tt_emp;
begin
  execute immediate
    'select employee_id, first_name, ds_rowid
     from ' || api_datasource.getQuery('EMPLOYEES1') ||'
     where ds_rowstatus=:1'
  bulk collect into v_emp_t
  using api_datasource.ROW_STATUS_UPDATED;
  ... 
end;

Best Regards,
Ibrahim

link

answered 26 Feb '13, 02:55

Ibrahim%20Sandalli's gravatar image

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

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

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

Tags:

×60

Asked: 25 Feb '13, 11:56

Seen: 997 times

Last updated: 26 Feb '13, 02:55


© Copyright Gerger 2017