ORACLE 11G - GET RID AN APOSTROPHE IN A STRING

first,  use bind variables and you won't have that issue.
You are "safely" falling into a SQL Injection problem.

I say safe because are failing rather than processing the sql.

second, if you don't use binds and the risk is acceptable
simply replace the apostrophe with a 2 before inserting into your query

that is,  make SCHOOL'S  become SCHOOL''S

I don't think you can use bind variables directly in crystal reports, at least not most versions, maybe in something very new.

You might want to consider putting the query in a stored procedure that returns a reference cursor for crystal to process

Can you try INTRODUCTION TO THE SCHOOL||chr(39)||S DRUG ABUSE POLICY

you can just use two single quotes intead of one - it will insert one single quiote, like that:

select * from table where title = 'INTRODUCTION TO THE SCHOOL''S DRUG ABUSE POLICY'

it will select correctly if there is only ioone sibngle quote in the actual string

You can isert stuyff in thwe same way

In the above SCHOOL''S  should have two connsecurtive single quotes, not one double quote

The data is being passed from the database to Crystal reports sql .  I have no control over changing the single quote to a double quote.  I have no way of knowing that the value being sent to Crystal has a single quote until the report fails.  Unless in my query, I could decode the value if a single quote exists change to a value that is accepted by ORACLE.

Could I somehow decipher if the value contains a single quote change the value to this for example, where title = 'INTRODUCTION TO THE SCHOOL||chr(39)||S DRUG ABUSE POLICY' within the SQL Statement.  If so, could someone show me how to do this?

Looks like Crystal has a REPLACE function.  Just replace a single quote to two single quotes with Crystal before you pass it to the database.

Not sure how you do this for you but here is an example using Crystal replace:
https://www.experts-exchange.com/questions/23994926/Crystal-Report-Replace-String-From-Start-till-End-Position.html

>>yes, i tried in my oracle i can

The problem here is the variable in question is still contained inside the Crystal Reports.  The replace needs to be done BEFORE it is handed off to Oracle.

You can change the selection formula in the subreport to be
 SELECT * FROM table WHERE title = "{?title_passed_from_main_report}"

mlmcc

Tmlmcc, The only problem is that the sql command in my sub report is bringing back people who do not have the title passed.


select name, dept
      from employee_table
      where dept.name = "{?dept_passed_from_main_report}"
and not exists(select 'x'
                              from courses
                                  where employee_table.emp_id = courses.emp_id
                                   and courses.title = "{?title_passed_from_main_report}")

You may need to change it to

Where are you adding the SQL for the subreport?

mlmcc

I use the DATABASE EXPERT Add Command and paste my sql there and create the parameters.    I am not familiar with doing it any other way.

If you are doing it through the ADD COMMAND all you need is

select name, dept
      from employee_table
      where dept.name = "{?dept_passed_from_main_report}"
and not exists(select 'x'
                              from courses
                                  where employee_table.emp_id = courses.emp_id
                                   and courses.title = {?title_passed_from_main_report})

mlmcc

What you have as the sql command statement is what I have as the SQL Command but when a title that contains an apostrophe is sent as a parameter value , the following error is encountered:
Failed to retrieve data from the database
Details: SQL Exception [SQL State] 4200 [Error Message] ORA-00907 missing right parenthesis [Database Vendor Code 907]

Are you saying remove the quotes from the parameter?   I did this, an error still occurs.

That or enclose the title in " "

mlmcc

You said that the SQL is in a subreport and the value with the ' is being passed from the main report to the parameter in the subreport.  If so, then you can check and, if necessary, change the value in the main report.  Let's say that the main report is reading that value in title_field and you have title_field linked to the subreport parameter.  You'd create a formula in the main report that modified title_field if necessary, and link that formula to the subreport parameter instead.

 for_yan suggested replacing one single quote with two.  You could do that using:

Replace ({title_field}, "'", "''")

 That's one single-quote inside of double-quotes, and then two single-quotes inside of double-quotes.

 If you just want to remove any single-quotes from the field:

Replace ({title_field}, "'", "")

 That's one single-quote inside of double-quotes, and then two double-quotes with nothing in between.

 Or put a character in the second set of double-quotes to replace any single-quotes with that character.

 James