What are the list of PL/SQL Scalar Data types ?

scalar datatypes are those which  hold a single value and do not have any internal components they are classified into below categories.

  • The SQL data types
  • REF CURSOR, explained in "Cursor Variables"
  • User-defined subtypes

Oracle PL/SQL LONG Datatype limitations

LONG datatype store variable-length character strings containing up to 2 gigabytes -1, or power(2,31)-1 bytes.

The use of LONG datatype is subject to the following restriction.

•A table can contain only one LONG column.

•You cannot create an object type with a LONG attribute.

•LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

•Index can't be created on LONG columns.

•In regular expressions LONG datatype can't be specified.

•Stored function can't return a LONG value.

•You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

•LONG and LONG RAW columns can't be replicated.

•All LONG columns, updated tables, and locked tables must be located on the same database within an SQL statement.

•LONG column can't appear in GROUP BY, ORDER BY clause, UNIQUE / DISTINCT operator or CONNECT BY clause in SELECT statements.

In addition, LONG columns cannot appear in these parts of SQL statements:

  • GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

  • The UNIQUE operator of a SELECT statement
  • The column list of a CREATE CLUSTER statement
  • The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
  • SQL built-in functions, expressions, or conditions
  • SELECT lists of queries containing GROUP BY clauses
  • SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
  • SELECT lists of CREATE TABLE ... AS SELECT statements
  • ALTER TABLE ... MOVE statements
  • SELECT lists in subqueries in INSERT statements