Let's clarify what you mean by "single quotes". First, there is the basic, "straight" single-quote character ' (which is used both as an opening and a closing quote), part of the standard ASCII set, at position 39. Then, there are "calligraphic" left and right single-quote characters - which are represented in Unicode and other character sets (but are NOT part of the basic ASCII character set).
The ASCII() function, contrary to its name, has very little to do with ASCII, except that in the case of characters actually included in the ASCII set, the function returns their ASCII code. In general, the function returns the decimal representation of the character in the database encoding. Note that the Oracle documentation itself is wrong: it says "the representation in the character set", but in fact it's the encoded value, not the code point of the character. This makes a huge difference in Unicode. (The fact that ASCII() returns the ASCII code of ASCII characters is only due to the fact that in most if not all character encodings, the ASCII characters are given an encoding equal to their ASCII code. This is very convenient for compatibility, but it is not a theoretical requirement - it's just a practical matter.)
The right single-quote character ’ is encoded at position 146 in the character set CP1252, which in Oracle is called WE8MSWIN1252. This, very likely, was the database character set in your old database.
The same character has the encoding 14844057 in the UTF-8 encoding of Unicode. Your database "character set" (really, encoding) is very likely AL32UTF8.
You will be able to find the database "character set" in both databases, and confirm what I said, with
select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
You can talk to your DBA to confirm that the new database uses a different character set from the old.
As for the single quote character (really: right single-quote character, the one we are talking about), in Unicode it is a multi-byte character. If it is rejected by varchar2(1), this means that your character length semantics is BYTE rather than CHAR. Try varchar2(1 char) (making explicit the meaning of 1) instead. You can see an illustration at the end of this answer.
The same assignment may have worked OK in the old database, regardless of its length semantics, because in CP1252 the right single-quote character is encoded as single-byte.
So, here's the example. The character I am assigning is the right single-quote character, my database character encoding is AL32UTF8, and my NLS_LENGTH_SEMANTICS is BYTE.
Fails:
declare
x varchar2(1);
begin
x := '’';
end;
/
Error starting at line : 1 in command -
declare
x varchar2(1);
begin
x := '’';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
Works:
declare
x varchar2(1 char);
begin
x := '’';
end;
/
PL/SQL procedure successfully completed.
