Hi all,

How can we give dynamic runtime where clause in formspider? In Oracle forms, I can store my where clause in a table column and set it as default_where clause at runtime. Some examples are as follows:

  1. USERID=:GLOBAL.USERID - filter userid based on value in global bind variable :GLOBAL.USERID.
  2. USERID=backend.GET_USERID - filter userid based on value returned by backend function.
  3. USERID=:BLOCK_NAME.COLUMN_NAME - filter userid based on column value in current form.

How this functionality can be used in formspider? Can you kindly explain with example? I tried the second example above but it did not work.


asked 10 Mar '13, 15:45

George's gravatar image

accept rate: 0%

Hi George,

Have a look at api_datasource.setDefaultQuery This api will allow you to do what you want to.

Best, Michiel


answered 11 Mar '13, 04:14

Michiel%20A's gravatar image

Michiel A
accept rate: 13%

Hi George,

You can use api_datasource.setDefaultQuery API to append the where clause stored in the table column. Correspondent expressions for your examples are as follows:

  1. USERID = api_session.getValueNR('GLOBAL.USERID')
  2. USERID = backend.GET_USERID
  3. USERID = api_datasource.getColumnValueNR('BLOCK_NAME.COLUMN_NAME') or USERID = api_component.getValueNR('BLOCK_NAME.COLUMN_NAME') following whether the block item is bound to a column or not.

In your case, you can use the following procedure to set the default where clause:

procedure setDefaultWhere is
  v_defaultQuery_tx varchar2(4000);
  v_defaultQuery_tx := 'select * from ('  
                        ||              api_datasource.getDefaultQuery('datasourceName')
                        ||            ') 
                        where USERID = backend.GET_USERID';
  api_datasource.setdefaultquery('datasourceName', v_defaultQuery_tx);

Hope this helps,


answered 11 Mar '13, 04:43

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
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



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: 10 Mar '13, 15:45

Seen: 2,473 times

Last updated: 11 Mar '13, 04:43

© Copyright Gerger 2017