Solved

ORACLE 11G - GET RID AN APOSTROPHE IN A STRING

Posted on 2011-03-09
17
892 Views
Last Modified: 2012-05-11
In Crystal Reports I have a sub report where the title is passed from the main report.  The main report passed this title INTRODUCTION TO THE SCHOOL'S DRUG ABUSE POLICY
When this value is passed to the sub report the following error appears
Failed to retrieve data from the database
Details: SQL Exception [SQL State] 4200 [Error Message] ORA-00907 missing right parenthesis [Database Vendor Code 907]

My sub report sql is SELECT * FROM table WHERE title = ‘{?title_passed_from_main_report}’
In this case the title is INTRODUCTION TO THE SCHOOL'S DRUG ABUSE POLICY

IN ORACLE or CRYSTAL, how can I replace the single quote in the title with a character?
     
0
Comment
Question by:cookiejar
  • 4
  • 4
  • 3
  • +4
17 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 
LVL 4

Expert Comment

by:leewv1
Comment Utility
Can you try INTRODUCTION TO THE SCHOOL||chr(39)||S DRUG ABUSE POLICY
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
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

0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
In the above SCHOOL''S  should have two connsecurtive single quotes, not one double quote
0
 

Author Comment

by:cookiejar
Comment Utility
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_23994926.html
0
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
Comment Utility
yes, i tried in my oracle i can

select replace(a,'''','?') from table

here I use 4 (four)  single quote in arow -
as a result from my string
tt'1 (in data) i get output tt?1

It meas that you can use
replace(your_column_name, '''','anything')

here you have four signle quotes - and it will reoplace one single quote in your data into
"anything" in this case.
I'm sure you can replace and then insert replaced string
like

insert into table values (replace(you_colum_nmae,'''','"'))

and here i have four single quotes in the first case and signle quote, double quote, signle quote in the second case
and it should insert the same struing with double quote instead of signle quote
 
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 100

Expert Comment

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

mlmcc
0
 

Author Comment

by:cookiejar
Comment Utility
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}")

0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You may need to change it to

Where are you adding the SQL for the subreport?

mlmcc
0
 

Author Comment

by:cookiejar
Comment Utility
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.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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

0
 

Author Comment

by:cookiejar
Comment Utility
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.


0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
That or enclose the title in " "

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now