What is the usage of tilde symbol (~) in Oracle. Please share me the output for the below query if we are using the column name and table name with two tilde symbols.

SELECT ~column_name~ from ~Table_name~

CodePudding user response：

From the Database Object Names and Qualifiers documentation:

6. Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

7. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.

   Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

In the query:

SELECT ~column_name~ from ~Table_name~

~column_name~ and ~Table_name~ are non-quoted identifiers as they are not surrounded by double-quotes ". However, since they do not start with an alphabetic character and they contain ~ characters (which are not alpha-numeric, _, $ or #) then the identifiers are invalid and the query will raise an exception, outputting:

ORA-00911: invalid character

and will not execute.

fiddle

CodePudding user response：

To directly answer the title of your question, tilde is rarely used as a PL/SQL not equals operator:

begin
    if 1 ~= 2 then
        dbms_output.put_line('Not equal');
    end if;
end;
/

As far as I know, that is the only valid use of tilde in Oracle, and as MTO explained, it certainly can't be used for object names without double quotes.