CODEWITHSUNDEEP
oracle-databasequotes
user951487
user951487

Reputation: 855

Oracle quotes issue . Error missing right parenthesis

INSERT INTO myTable (SELECT 2, mem_type_actor ('Phillip D'Onofrio ,Val Kilmer') FROM DUAL)

I want to insert into myTable these records but because of the quotes of D'Onofrio I get the error:

ORA-00907: missing right parenthesis

How can I fix it ?

I edit my question:

Infact I don't take records like that, I have these sql codes:

 M_TERMS=Rocky,Carroll ; Chris,O'Donnell ; Daniela,Ruah ;
 M_STR:=REPLACE(REPLACE(''''||M_TERMS||'''',',',' '),';',','); 
 M_SQL:='INSERT INTO myTable (SELECT '||M_ID||' , mem_type_actor ('||M_STR||') FROM DUAL)';

So when a record is like that O'Donnell , ORA-00907: missing right parenthesis occurs

Upvotes: 1

Views: 1670

Answers (4)

Vineet Singh
Vineet Singh

Reputation: 347

There are two ways of using quotation marks in the oracle database 19c:

  • Using Q(quotation) before the beginning of the string and ! at the start and end of the string.
SELECT Q!' Today is my mother's birthday!' FROM DUAL;
  • Using double quotations(' ') marks. This is just a workaround.
SELECT 'Today is my mother''s birthday' FROM DUAL;

I hope this would have answered this question regarding quotation marks in the oracle database.

Upvotes: 0

Toon Koppelaars
Toon Koppelaars

Reputation: 154

Or use Oracle's q-quote operator: http://www.oracle-base.com/articles/10g/PlsqlEnhancements10g.php#quoting_mechanism_for_string_literals

Upvotes: 3

Marco
Marco

Reputation: 57583

You should use

INSERT INTO myTable 
    (SELECT 2, mem_type_actor ('Phillip D''Onofrio ,Val Kilmer') FROM DUAL)

using two single quotes in place of one inside string.

Upvotes: 4

tobiasbayer
tobiasbayer

Reputation: 10379

Use two single quote signs: D''Onofrio.

Upvotes: 3

Related Questions