The concatenation operator manipulates character strings and CLOB data.
|| Concatenates character strings and CLOB data.
SELECT NAME || ‘ Belongs to ‘ || city FROM PERSONS ORDER BY NAME;
2.Hierarchical Query Operators
Two operators, PRIOR and CONNECT_BY_ROOT, are valid only in hierarchical queries.
In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator. PRIOR evaluates the immediately following expression for the parent row of the current row in a hierarchical query. PRIOR is most commonly used when comparing column values with the equality operator.
It is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.
Restriction on CONNECT_BY_ROOT
You cannot specify this operator in the START WITH condition or the CONNECT BY condition.
user-defined operators act like the built-in operators such as , and = . However, We can create them with the CREATE OPERATOR statement, and they are identified by user-defined names. They reside in the same namespace as tables, views, types, and standalone
After you have defined a new operator, you can use it in SQL statements like any other built-in operator.
CREATE OPERATOR Contains BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER USING text.contains;