Understanding the Issue with Apostrophes

The most probable case with the need to use apostrophes is when dealing with string types. Before understanding how to deal with apostrophes let us understand what string type means and how is it related to apostrophes.

Strings are a datatype of PL/SQL. There are various types of string available in PL/SQL, a few of them are as follows:

• CHAR
• VARCHAR2
• RAW
• NCHAR
• NVARCHAR2
• ROWID
• UROWID

Let us create a sample table to see how to insert string values.

CREATE TABLE test (
    name VARCHAR2(15),
    id INT
);

Now let us insert string values into it. To do that we will surround the values in quotes.

INSERT ALL
        INTO test(name, id) VALUES ('Dhruv', 1) 
        INTO test(name, id) VALUES ('Aayush', 2)
SELECT * FROM DUAL;

SELECT * FROM test;

Output:

Note: Don’t worry about DUAL, it is just syntax for inserting values. DUAL comes at places where you have nothing to select from. Like in MySQL, you can write “SELECT ‘abc’” but in PL/SQL you need to write “SELECT ‘abc’ FROM DUAL”. The INSERT ALL statement used above requires a SELECT statement and DUAL is used to fill the empty FROM.

Notice how the integers don’t need quotes around them while we need to surround the strings in quotes.

Now let us try to insert a value with an apostrophe in it.

INSERT INTO test VALUES ('Aaka'sh', 3);

The database throws an error

Output:

Although the error does not directly mean it is related to the extra apostrophe in the string, it is caused by that. The database treats the value `’Aaka’sh’` as two separate words including one complete word `’Aaka’` and another value starting from `sh’` but not complete yet. Therefore, it asks for a missing comma to separate these values.

PL/SQL, or Procedural Language/Structured Query Language, is an extension of SQL used for writing procedural code within Oracle databases. It is a proprietary procedural extension of SQL developed by Oracle Corporation specifically for Oracle Database. It combines the power of SQL with procedural constructs like loops, conditions, and exception handling. It is a blocked programming language, programming units can be named or unnamed blocks. Unnamed blocks are never stored in the database.