A real rave from the grave this one! Back in 1997 - but note the addendum...

A little reminder that it is possible to enter some quite clever queries via Oracle Forms. There are 3 distinct methods -
1. inequalities method
2. the '#' method
3. the 'substitution' or '&' method.

Note that this is not an Oracle Apps feature; rather it is a Forms feature

Inequalities method

Within a form, say People form: Enter a query. Within a chosen field enter either '>', '<', or '!=' followed by the actual value - meaning 'greater than', 'less than', or 'not equal to' the value. E.g:

In the date of birth field enter >31-DEC-1979 to return all records with a date of birth later than that date

'#' Method

Within a form say People form: Enter a Query and within your chosen field put '#' followed by a simple 'where' clause - although without the word 'WHERE'. E.g:

In the date of birth field enter

#IS NULL

When the query is executed, lo and behold all the records are returned with a null date of birth

Similarly entering

#BETWEEN '<date1>' AND '<date2>'

will return records where the date of birth falls between the 2 entered dates

You can also try using things like #IN ('x', 'y', 'z')

'&' Method

The restriction with the # method is the enter-able field size - if it's short you may not be able to fit the word 'BETWEEN' in there! A more versatile way is to use the '&' method. In query mode choose a field and enter the text '&x', or '&q' or '&dave' which ever suits you really! (what matters is the '&'). Then execute the query. A window entitled 'Query Where...' opens and you may then enter your full sql query before you hit <enter>. Again no 'WHERE' word is needed E.g the query text might read:

date_of_birth IS NULL and current_employee_flag = 'Y'

ADDENDUM

Nowadays this ability to perform '&' queries has by default been restricted. However, it can be turned back on. Get your friendly DBA to set the Unix parameter FORMS60_RESTRICT_ENTER_QUERY to FALSE then bounce the listener at a convenient point and you'll be back in business.

Thanks to Janet and John for helping me out this morning!!

I have tried the .#BETWEEN on the birth date in one of the folders and am getting an error that a right parenthesis is missing!

Could it be because I am using a wild card in the dates? e.g. between '01-AUG%' AND '30-AUG%' I have tried using brackets in the query in various places, but the same error is appearing.

Any help would be greatly appreciated.

Lordy! This must take an award for resurecting the oldest post on the forum!

To the subject at hand....I'm not sure the wildcard would work with date fields, as there is an implicit conversion that takes place to convert the date into the internally stored format in order to execute the query - providing a partial wildcard on the date may cause grief.
Have you tried supplying the whole date as a test?

Hi Baz,

Yes apologies, I did dust of a few cobwebs!!

The query did work when I entered the date as a whole. Thinking more about it, I suppose it was asking alot to use the wildcards in that instance!!

Will try an alternative method for that particular query.

Take care

Blimey, someone open a window, all the dust in here is choking!

If you wanted to find anyone with a DoB in August, then the following seems to work:

#LIKE '%AUG%'

No idea why though!

PS. You don't even need the like bit, as of course that's the condition by default anyway! ie just enter '%AUG%' in the DoB field and execute the query.

I understood Oracle had disabled this functionality in Apps as a security risk in 11.5 onwards. Certainly, I haven't managed to utilise the "&" functionality in any environment for a long time. There was some indication of a profile option to enable or disable it, but that never seemed to work either. Does the "&" now work again? Or, have I been reliably misinformed in the past?

I was unaware of the "#" option, so this is a handy hint/reminder.

Cheers.


I just read the rest of the first post. DOH!!! Re. the comment about the DBA option, I do remember this now, rather than it being a profile option; but as I mentioned, I have never managed to get this to work.

Strangely enough, me neither! Although I have seen for myself today that the unix env variable FORMS60_RESTRICT_ENTER_QUERY still exists, albeit set to TRUE.

The '#...' trick does indeed still work, subject to the restrictions of field query size. Also, it does not allow compound 'where' clauses, e.g. #LIKE '%AUG%' AND... the 'AND' bit triggers the same message as that encountered if you try the '&x' query.

Edited to PS: Having just done a search for that parameter, it seems that the switching off was in response to a SQL injection vulnerability:

http://www.red-database-security.com/wp/sql_injection_forms_us.pdf

I have a VERY vague memory that there is a second env variable involved, but no clue as to what it was. However, even then, it didn't work. I did quite a few tests on this back in the dim distant past, as I used the "&" function all the time, especially to control ordering in forms where Oracle seems to have no sequence at all, and was quite disappointed that it's functionality had disappeared.

If anyone figures out how to get it working again, It would be great to learn how.

Cheers,

Peter.

P.S. I have just used #IS NULL in the Approved Salary field of the Salary Management form, and it didn't work. Although, I did use a #xxx query in another Folders based form and it did work.

Is this a form specific option?

I agree we lost a significant weapon in our diagnostic armoury with the switching off of this parameter - I think they could have done more to restrict the execution of more damaging stuff like pl/sql, and left the true querying bit.

PS I too have the same problem on that form - having said that, I am having difficulties getting any kind of restriction to work based on criteria entered in the 'Approved Salary' field, so I don't think it's an issue to do with the '#' per se.

Yes, apologies, my initial example was a bit rubbish!!

The # function has proved to be a bit tempremental depending on the length of the field that you're using. For example, if you wanted to select some employees from the people folder using the #IN on the employee number field, you're restricted to a certain number of characters so can only query two employee numbers at a time....

Anyway, we've been able to provide the users with the ability to do what they need, so thanks for the help.