Solved

Apostrophes break my query?

Posted on 2000-05-16
4
1,669 Views
Last Modified: 2008-02-26
Hi......
I'm using Perl-DBI to extract data from Oracle.
My selects and inserts have apostrophes in them.
This breaks my sql statement with...........
"quoted string not properly terrminated."
help, the same happens when I try and escape the \' in sqlplus.
cheers
0
Comment
Question by:rickyr
  • 2
4 Comments
 
LVL 2

Expert Comment

by:davdonin
ID: 2813965
If you want an apostrophe inside of your statemnt you have to double them, e.g: 'this ''is a'' string with ''apostrophes'''.
Hope this helps.
0
 
LVL 9

Accepted Solution

by:
dbalaski earned 50 total points
ID: 2814010
Hi,  
Pretty easy to do this,  two methods outline

Method #1 Use double apostrophes inside  the string to insert:
example:  (All done in a database for verification of results)

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> create table test (field1  varchar2(200));
Table created.
     
** note the double '' in the field1 insert statement

SQL> insert into test values ('Radha''s Database');
1 row created.

SQL> commit;

SQL> select * from test;
FIELD1
---------------------------
Radha's Database
        **  here it is

Second Method:   Say you want to encapsultate a value in apostrophes from a table,   use character string 39 appended to the field.  Example:
SQL> select chr(39)||name||chr(39) from v$database;

CHR(39)||NA
-----------
'RADHA_T1'
**            **  here they are!

Hope this answers everything okay!  
Let me know how it works for you!
dB
0
 
LVL 3

Author Comment

by:rickyr
ID: 2945728
Answer accepted
0
 
LVL 3

Author Comment

by:rickyr
ID: 2945729
Thanks, see my new Q about loging users out!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

867 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

21 Experts available now in Live!

Get 1:1 Help Now