I have a combobox in a grid, that is dependent to the values of another field in the grid. I also have a universal action to requery the listdatasource on each combo box each time the combobox is getting the focus. Doing this I am 100% sure that each combo box has the latest updated data available.

The simplest solution is to use a parameter and write another action only for this combobox, but I am trying to keep actions at minimum, so I thought that is a good idea to get the value of the field by using api_datasource.getcolumnvalue in the where clause of the query. But the query cannot be parsed. Can I use api_datasource.getcolumnvalue functions in queries or the are doing doing DML operations?

Thank you.

asked 12 Sep '12, 03:34

Vlisidis%20Akis's gravatar image

Vlisidis Akis
accept rate: 5%

I created a new datasource using the following query: select field1, field2 from some_view where text_field = api_datasource.getColumnValueTX('dataset.field') As I said my goal was to reuse my pl/sql refreshComboBoxDatasource procedure in all comboboxes. The problem started on one combobox placed on a grid, where I needed to have the line's value. I solved the problem using some pl/sql:

1) I modified the query to: select field1, field2 from some_view where text_field = :param1

2) I created a table to map the required parameters of datasources to fields: create table FS$DSPARAMS ( dsp_datasource VARCHAR2(100) not null, --Target Datasource name dsp_param VARCHAR2(100) not null, --Target Datasource parameter dsp_source VARCHAR2(100) not null, --Source Datasource.Field dsp_type VARCHAR2(1) not null --T=Text,N=Number,D=Date ) In order to bind the parameter :param1 of datasource combo_ds1 to the field fld1 of datasource source_ds1, all is required is to add a record like this: dsp_datasource:'combo_ds1', dsp_param:'param1', dsp_source:'combo_ds1.fld1', dsp_type:'T' (Text)

3) Created a procedure to apply the binding: procedure setBindVariables(pDatasource in varchar2) is begin for i in (select dsp_param, dsp_source, dsp_type from fs$dsparams where dsp_datasource = pDatasource ) loop case i.dsp_type when 'T' then api_datasource.setBindVar( in_datasourceDotbindVar_tx => pDatasource || '.' || i.dsp_param, in_value_tx => api_datasource.getColumnValueTX(i.dsp_source) ); when 'N' then api_datasource.setBindVar( in_datasourceDotbindVar_tx => pDatasource || '.' || i.dsp_param, in_value_nr => api_datasource.getColumnValueNR(i.dsp_source) ); when 'D' then api_datasource.setBindVar( in_datasourceDotbindVar_tx => pDatasource || '.' || i.dsp_param, in_value_dt => api_datasource.getColumnValueDT(i.dsp_source) ); end case; end loop; end;

And finally call this procedure before api_datasource.executeQuery.It works very well for any datasource. So my refreshComboDatasource is like this: procedure refreshComboDatasource is fDatasourceName varchar2(100); begin /get the firing component's datasource / fDatasourceName := getComboDatasource(api_application.getClientEvent().objectName_tx); setBindVariables(fDatasourceName); api_datasource.executeQuery(fDatasourceName); end; getComboDatasource is a function that returns the listdatasource propery of a combobox.

I hope my solution to be usefull to someone....


answered 12 Sep '12, 07:48

Vlisidis%20Akis's gravatar image

Vlisidis Akis
accept rate: 5%

Hi Vlisidis,

You can use api_datasource.getColumnValue API in the queries of Datasource Definitions. We generally use bind variables in order to pass parameters to the Datasource Definition queries.

If you send your query and where clause, we can take a look at it.


answered 12 Sep '12, 03:52

Ugur%20Kocak's gravatar image

Ugur Kocak ♦
accept rate: 23%

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: 12 Sep '12, 03:34

Seen: 1,259 times

Last updated: 12 Sep '12, 07:48

© Copyright Gerger 2017