Re: Forms 3 - Order by in Block Def'n

In article <4ki24r$d5_at_karri.bs.wa.gov.au>, Steve Corbett <stevec_at_fcs.wa.gov.au> says:
>
>Situation:
>two blocks in Form. block A is a control block. block B is based on a
>table. according to criteria entered in block A certain rows are returned
>in block B.
>Problem:
> i want to sort the rows in block B so that surnames that exactly match
>the search criteria in block A are sorted to the front. So i tried:
>order by decode(surname,:a.surname,1,2)
>
>Oracle complains that there is a right parenthesis missing because it
>does not like the '.' following the block name. If i am not allowed to
>use field names (or block names) in the order by, how else can i do this?
>(i tried using a :global - didnt work either)
>

There is clumsy solution using the #-function in Forms. The #-function is away the operator can add more conditions in sql-syntax during runform. For instance entering #fieldname='123' in enter-query-mode adds ' and (fieldname='123') to the base-select in the form.

This can be done with order by too. However there are some limitations. Consider in your pre-query trigger in block B then following:

 :b.field := '#1=1) order by decode(surname,''||:a.surname||'',1,2' ;

This will add the necessary decode to the select in the query, so the last part of the select will look like this:

  .. and (1=1) order by decode(surname,''||:a.surname||'',1,2')

:b.field must be a database-field, it must be the last field in the block, and the fields query-length must be adequate to keep the string. Finally Block B must not contain any order-by clause in the block's order-by definition.

This should solve your problem.

And then a little disaster about the #-function:

Consider having a form displaying sensitive data, where some user dont have access to all data ... you think.

If the user enter query mode and key in  

   #1=1) or (1=1

the base select will look like this:

.... where ..... and 1=1) or (1=1)

The or 1=1 will turn of all other conditions entered in the blocks where/order-by section and open a potential security gap.

This can be avoided off course by repeating the conditions in a post-query trigger refusing all records not meeting the blocks where/order-by, og by using views and implementing security on database-level, but how many security-sensitive systems has been implemented like that ??

Yours

Rene Brisson
Denmark
RBrisson_at_centrum.dk Received on Wed Apr 17 1996 - 00:00:00 CEST