CODEWITHSUNDEEP
oracle-databaseoracleforms
Saddam Meshaal
Saddam Meshaal

Reputation: 552

How to Get datablock default where that oracle forms generate with Enter-query action

I am working with oracle forms 6i.

Simply I am using database block with these items : 

Employees:    employee_id , job_id , department_id , manager_id

note: employees is the data-block name .

For example: when end user click enter-query button and write 50 in department_id item and then click execute-query button; data block will return all employees who are in department 50.

My question is : How can I get the WHERE CLAUSE that oracle forms generate when returned desired data?...

I used this code in pre-query trigger 

:parameters.whr:=get_block_property('employees',default_where);

But it returned no results

Upvotes: 3

Views: 7268

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

You're close, but not close enough. It is the GET_BLOCK_PROPERTY you need, but use its LAST_QUERY parameter. It will return SQL statement of the last query in the specified block.

Alternatively, use system variable :SYSTEM.LAST_QUERY (returns the same result).

Here's an example( the following code might be put in KEY-EXEQRY trigger of employees block ):

declare
  l_lastq          varchar2(4000);
  l_where_position number;
  l_where_clause   varchar2(4000);
begin
  execute_query;
  l_lastq := :system.last_query;
  l_where_position := instr(lower(l_lastq), 'where');

  if l_where_position > 0 then
     l_where_clause := substr(l_lastq, l_where_position, length(l_lastq));
     message('WHERE clause: ' || l_where_clause);
  end if;
end;

Upvotes: 5

Related Questions