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.
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.
PortletPaul
can you provide the query being executed by the form?
Double the apostrophe. For example
Anne''s Grocery store
As an addition to IrogSinta answer - do it in Pre-Query trigger.
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
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
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
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
thank you Henka! Exactly what is needed - some code ;)