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.
Microsoft AccessOracle Database

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
PortletPaul

can you provide the query being executed by the form?
IrogSinta

Double the apostrophe.  For example  
Anne''s Grocery store

Open in new window

Helena Marková

As an addition to IrogSinta answer - do it in Pre-Query trigger.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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
IrogSinta

Can you post your query?
PortletPaul

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
PortletPaul

>>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
Helena Marková

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

thank you Henka! Exactly what is needed - some code ;)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy