Link to home
Avatar of Akai123
Akai123

asked on

oracle forms 10g -Query Execution

Hi,
I have oracle form having fields like 1) Store Name and 2) Store Number. When I query the form using Store Number then  form is retriving the store name for any given Store Number.

But when I query by store name having with apostraphe(Anne's Grocery store, Jeffy's Lube --etc)  the form is failing to retrive any information.

in the database the some  store name column contains apostrphe in it (Anne's Grocery store, Jeffy's Lube) . It is not permitted to change\clean  the database data side.

How to fix code in the form so that form can query by store name contains apostrphe  without any issues.
Avatar of PortletPaul
PortletPaul
Flag of Australia image

can you provide the query being executed by the form?
Double the apostrophe.  For example  
Anne''s Grocery store

Open in new window

As an addition to IrogSinta answer - do it in Pre-Query trigger.
Avatar of Akai123
Akai123

ASKER

In database store name is stored as Anne's Grocery store, Jeff's backery...
user want to query by store name. He is entering Anne's Grocery store in Oracle form's store name field. When he query form is not bringing any data.
When user enter Ann% and query it then form is bringing the record. I need to fix the code in the pre query trigger.
The form is building where clause like --where store_name = ' Anne's Grocery store'  then query is failing.
I case of  situation when user entering  Ann% in pre query  the form is building where clause like --where store_name  like 'Ann%' and bringing records.

user want to enter full store name which is  Anne's Grocery like as in the database and want to get information for the query.
Please help me
Can you post your query?
can you provide the code of the pre-query trigger?

There are 2 approaches to this: replace single the quote with two quotes

or, use a 10g quote feature: compare these:

select
  'Anne''s Grocery store'
, 'Jeffy''s Lube '
, q'{Anne's Grocery store}'
, q'{Jeffy's Lube}'
from dual
 
or, like this:
select
*
from contacts
where details = 'Anne''s Grocery store'
or details = 'Jeffy''s Lube'
;
select
*
from contacts
where details = q'{Anne's Grocery store}'
or details = q'{Jeffy's Lube}'
;
see both in action here: http://sqlfiddle.com/#!4/9bf75/1
Avatar of Akai123

ASKER

I tried q' in sql it is fine but in the form somehow it did not work.
I have an oracle form with 10 fields , In pre query mode user inputs data in the form fields and form will build Query and that query will be executed on the database.

when user enter  Anne's Grocery in the store name field and then  query the form  code   is building where clause ( --where store_name = ' Anne's Grocery store' )  then query is failing.

My requirement is simple. when the user enter Anne's Grocery in the store name and executes query the form should bring data without fail.
>>My requirement is simple.
yes it is, so are the 2 provided escaping methods, which you probably now know will both work in SQL

the hard part is getting Oracle Forms' to work the way that is needed, if it is dynamically generating that query - and you cannot intercept/alter the way it is being generated, then I may not be able help further I'm afraid.

http://docs.oracle.com/cd/B12166_01/web/B10470_01/basics.htm
search for apostrophe on this page
ASKER CERTIFIED SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
thank you Henka! Exactly what is needed - some code ;)