Oracle Docs Data Types & SQL Tools
.01234
actual data: .01234 col specified as: NUMBER(4,5) stored as?
0.00012
actual data: 1.2d-4 col specified as: NUMBER(2,5) stored as?
0.00001
actual data: 1.2e-5 col specified as: NUMBER(2,5) stored as?
123.89
actual data: 123.89 col specified as: NUMBER stored as?
124
actual data: 123.89 col specified as: NUMBER(3) stored as?
exceeds precision
actual data: 123.89 col specified as: NUMBER(3) stored as?
exceeds precision
actual data: 123.89 col specified as: NUMBER(4, 2) stored as?
123.89
actual data: 123.89 col specified as: NUMBER(5, 2) stored as?
100
actual data: 123.89 col specified as: NUMBER(6, -2) stored as?
123.9
actual data: 123.89 col specified as: NUMBER(6, 1) stored as?
BINARY_FLOAT
32-bit floating point number. This data type requires 4 bytes.
BLOB
A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
TRUE
A stored function cannot return a LONG value. T/F
FLOAT
A subtype of the NUMBER data type having precision p. value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits. value requires from 1 to 22 bytes.
TRUE
A table can contain only one LONG column. T/F?
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
Accepted values are 0 to 9. The default is 6. The size is fixed at 13 bytes. It has fractional seconds and an explicit time zone. The default format is determined explicitly by the NLS_TIMESTAMP_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE.
TIMESTAMP
Accepted values are 0 to 9. (fractional_seconds_precision) The default is 6. The size is 7 or 11 bytes, depending on the precision. It contains fractional seconds but does not have a time zone. Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. The default format is determined explicitly by the NLS_TIMESTAMP_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
numeric precedence
BINARY_FLOAT > BINARY_DOUBLE > NUMBER
UROWID [(size)]
Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
ROWID
Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudocolumn.
32767
Beginning with Oracle Database 12c, you can specify a maximum size of _____ bytes for the VARCHAR2, NVARCHAR2, and RAW data types. You can control whether your database supports this new maximum size by setting the initialization parameter MAX_STRING_SIZE
FALSE - CLOB, NCLOB, and BLOB values up to approximately 4000 bytes are stored *inline* if you enable storage in row at the time the LOB column is created. LOBs greater than 4000 bytes are always stored *externally*.
CLOB, NCLOB, and BLOB values up to approximately 4000 bytes are stored externally if you enable storage in row at the time the LOB column is created. LOBs greater than 4000 bytes are always stored internally.
6, 2, 2
CREATE TABLE time_table (start_time TIMESTAMP, duration_1 INTERVAL DAY (6) TO SECOND (5), duration_2 INTERVAL YEAR TO MONTH); The start_time column is of type TIMESTAMP. The implicit fractional seconds precision of TIMESTAMP is ______. The duration_1 column is of type INTERVAL DAY TO SECOND. The maximum number of digits in field DAY is 6 and the maximum number of digits in the fractional second is 5. The maximum number of digits in all other datetime fields is ____. The duration_2 column is of type INTERVAL YEAR TO MONTH. The maximum number of digits of the value in each field (YEAR and MONTH) is ___.
Text Literals
Case is insignificant in reserved words, keywords, identifiers, and parameters. However, case is significant in _____ and quoted names.
LONG
Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility.
BFILE
Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
INTERVAL, Numeric
Datetime Arithmetic Which operations are supported for +?
backward compatibility
Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for _________ _______________
one day
Each DATE value contains a time component, and the result of many date operations include a fraction. This fraction means a portion of ____ ___.
36
Each DATE value contains a time component, and the result of many date operations include a fraction. This fraction means a portion of one day. Example: 1.5 days is __ hours
Implicit Data Type Conversion
Each value subsequently placed in a column assumes the data type of the column. For example, if you insert '01-JAN-98' into a DATE column, then Oracle treats the '01-JAN-98' character string as a DATE value after verifying that it translates to a valid date.
01-MAY-2009
Examine the following query: SELECT TO_DATE('2009', 'YYYY') FROM DUAL; If the current month is May what does this query return?
CHAR [(size [BYTE | CHAR])]
Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte. BYTE and CHAR have the same semantics as for VARCHAR2. PADDED because
NCHAR[(size)]
Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
FALSE - For TIMESTAMP WITH TIME ZONE, the datetime value is always in UTC, so no conversion is necessary.
For both TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. T/F
TRUE
If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
FALSE - If the other operand has a fractional seconds value, then the fractional seconds value is lost.
If one operand is a DATE value or a numeric value, neither of which contains time zone or fractional seconds components, then: If the other operand has a fractional seconds value, then the fractional seconds value is kept. T/F
FALSE - If the other operand has a time zone value, then Oracle uses the session time zone in the returned value.
If one operand is a DATE value or a numeric value, neither of which contains time zone or fractional seconds components, then: If the other operand has a time zone value, then Oracle uses the local time zone in the returned value. T/F
TRUE
If one operand is a DATE value or a numeric value, neither of which contains time zone or fractional seconds components, then: Oracle implicitly converts the other operand to DATE data. The exception is multiplication of a numeric value times an interval, which returns an interval. T/F
TRUE
LOBs permit efficient, random, piece-wise access to and manipulation of data. T/F
FALSE - they cannot appear is lists of queries containing GROUP BY clauses
LONG columns can appear in these parts of SQL statements: SELECT lists of queries containing GROUP BY clauses
FALSE - they cannot appear in built-in functions, expressions, or conditions
LONG columns can appear in these parts of SQL statements: SQL built-in functions, expressions, or conditions
FALSE - they can appear in NULL and NOT NULL constraints
LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they cannot appear in NULL and NOT NULL constraints). T/F
TRUE
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 T/F
TRUE
LONG columns cannot appear in these parts of SQL statements: SELECT lists in subqueries in INSERT statements
TRUE
LONG columns cannot appear in these parts of SQL statements: SELECT lists of CREATE TABLE ... AS SELECT statements
TRUE
LONG columns cannot appear in these parts of SQL statements: SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators T/F
TRUE
LONG columns cannot appear in these parts of SQL statements: The UNIQUE operator of a SELECT statement
TRUE
LONG columns cannot be indexed. T/F
FALSE - LONG data cannot be specified in regular expressions.
LONG data can be specified in regular expressions. T/F
CLOB
Maximum size is (4 gigabytes - 1) * (database block size). A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set.
BINARY_FLOAT, BINARY_DOUBLE
Oracle Database provides two numeric data types exclusively for floating-point numbers. What are they?
TRUE
Oracle implicitly converts BINARY_FLOAT and BINARY_DOUBLE operands to NUMBER. T/F
RAW(size)
Raw binary data of length size bytes. You must specify size for a RAW value. Maximum size is: 32767 bytes if MAX_STRING_SIZE = EXTENDED 2000 bytes if MAX_STRING_SIZE = STANDARD Refer to "Extended Data Types" for more information on the MAX_STRING_SIZE initialization parameter.
LONG RAW
Raw binary data of variable length up to 2 gigabytes.
TRUE
Rowids contain the following information: The data block of the data file containing the row. The length of this string depends on your operating system. T/F
TRUE
Rowids contain the following information: The data object number, which is an identification number assigned to every database segment. You can retrieve the data object number from the data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number. T/F
FALSE - the first data file has the number 1 not 0
Rowids contain the following information: The database file containing the row. The first data file has the number 0. The length of this string depends on your operating system. T/F
TRUE
Rowids contain the following information: The row in the data block. T/F
29-FEB-08
SELECT TO_DATE('29-FEB-2004', 'DD-MON-YYYY') + TO_YMINTERVAL('4-0') FROM DUAL; What is the result?
ERROR - fails because adding one year to a date that exists only every four years is not valid.
SELECT TO_DATE('29-FEB-2004','DD-MON-YYYY') + TO_YMINTERVAL('1-0') FROM DUAL; What is the result?
ERROR - adding one month to a 31-day month would result in September 31, which is not a valid date.
SELECT TO_DATE('31-AUG-2004','DD-MON-YYYY') + TO_YMINTERVAL('0-1') FROM DUAL; What is the result?
10 minutes from now
SYSDATE + (10/1440) = ?
Tomorrow
SYSDATE + 1 = ?
one week ago
SYSDATE - 7 = ?
TRUE
Selecting a LOB from a table actually returns the LOB locator and not the entire LOB value. T/F
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
Stores a period of time in days, hours, minutes, and seconds, where day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. *The default is 2.* fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6. The size is fixed at 11 bytes.
INTERVAL YEAR [(year_precision)] TO MONTH
Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. *The default is 2.* The size is fixed at 5 bytes.
NCLOB
Stores national character set data. A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size).
the number of days since each employee was hired.
Subtracting the hire_date column of the sample table employees from SYSDATE returns ...
FALSE - BLOB, NCLOB, and CLOB values can be stored in *separate* tablespaces.
The LOB locator is stored in the table column, either with or without the actual LOB value. BLOB, NCLOB, and CLOB values can be stored in the same tablespaces. T/F
31-day month
The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a ___ ___ ___
default date values
The year is the current year, as returned by SYSDATE. The month is the current month, as returned by SYSDATE. The day is 01 (the first day of the month). The hour, minute, and second are all 0.
TZR, TZD
To resolve these boundary cases, Oracle uses the ___ and ___format elements
Object type
UDTs that are abstractions of the real-world entities, such as purchase orders, that application programs deal with. is a schema object with three kinds of components:
fractional seconds or a time zone
Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. DATE does not have _____________________________________________.
VARCHAR2
Variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is: 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED 4000 bytes or characters if MAX_STRING_SIZE = STANDARD
TIMEZONE_HOUR
What type of datetime field matches the parameters? Valid Values for Datetime: -12 to 14 (This range accommodates daylight saving time changes.) Not applicable for DATE or TIMESTAMP. Valid Values for INTERVAL: Not applicable
HOUR
What type of datetime field matches the parameters? Valid Values for Datetime: 00 to 23 Valid Values for INTERVAL: 0 to 23
MINUTE
What type of datetime field matches the parameters? Valid Values for Datetime: 00 to 59 Valid Values for INTERVAL: 0 to 59
TIMEZONE_MINUTE
What type of datetime field matches the parameters? Valid Values for Datetime: 00 to 59. Not applicable for DATE or TIMESTAMP. Valid Values for INTERVAL: Not applicable
SECOND
What type of datetime field matches the parameters? Valid Values for Datetime: 00 to 59.9(n), where 9(n) is the precision of time fractional seconds. The 9(n) portion is not applicable for DATE. Valid Values for INTERVAL: 0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
DAY
What type of datetime field matches the parameters? Valid Values for Datetime: 01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the current NLS calendar parameter) Valid Values for INTERVAL: Any positive or negative integer
TIMEZONE_ABBR
What type of datetime field matches the parameters? Valid Values for Datetime: Query the TZABBREV column of the V$TIMEZONE_NAMES data dictionary view. Not applicable for DATE or TIMESTAMP. Valid Values for INTERVAL: Not applicable
TIMEZONE_REGION
What type of datetime field matches the parameters? Valid Values for Datetime: Query the TZNAME column of the V$TIMEZONE_NAMES data dictionary view. Not applicable for DATE or TIMESTAMP. For a complete listing of all time zone region names, refer to Oracle Database Globalization Support Guide. Valid Values for INTERVAL: Not applicable
YEAR
What type of datetime field matches the parameters? valid values for datetime: -4712 to 9999 (excluding year 0) valid values for INTERVAL: Any positive or negative integer
MONTH
What type of datetime field matches the parameters? valid values for datetime: 01 to 12 valid values for INTERVAL: 0 to 11
TRUE
When you pass a timestamp, interval, or numeric value to a built-in function that was designed only for the DATE data type, Oracle implicitly converts the non-DATE value to a DATE value. T/F
FALSE - With the exception of *NCLOB*, you can define one or more LOB attributes in an object.
With the exception of CLOB, you can define one or more LOB attributes in an object. T/F
FALSE - Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on different databases. T/F
FALSE - You can access and populate rows of an inline LOB column (a LOB column stored in the database) or a LOB attribute (an attribute of an object type column stored in the database) simply by issuing an *INSERT or UPDATE* statement.
You can access and populate rows of an inline LOB column (a LOB column stored in the database) or a LOB attribute (an attribute of an object type column stored in the database) simply by issuing an ALTER or DELETE statement. T/F
FALSE - Due Date is missing double quotation marks; spaces are not allowed in aliases and require double quotes
You can add an interval value expression to a start time. Consider the sample table oe.orders with a column order_date. The following statement adds 30 days to the value of the order_date column: SELECT order_id, order_date + INTERVAL '30' DAY AS Due Date FROM orders ORDER BY order_id, Due Date; T/F
FALSE - you cannot create a object type with a LONG attr
You can create an object type with a LONG attribute. T/F
TRUE
You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
TRUE
You can define more than one LOB column in a table. T/F
TRUE
You can delete a row containing a LOB column or LOB attribute and thereby also delete the LOB value. For BFILEs, the actual operating system file is not deleted. T/F
TRUE
You can insert a new row or update an existing row that contains one or more LOB columns or an object with one or more LOB attributes. In update operations, you can set the internal LOB value to NULL, empty, or replace the entire LOB with data. You can set the BFILE to NULL or make it point to a different file. T/F
False - you cannot multiply or divide date or timestamp values
You can multiply or divide date or timestamp values. T/F
SELECT, SET, VALUES
You can reference LONG columns in SQL statements in these places: ____________ lists ___ clauses of UPDATE statements ______ clauses of INSERT statements
interval
You can use NUMBER constants in arithmetic operations on date and timestamp values, but not _____________ values. Oracle internally converts timestamp values to date values and interprets NUMBER constants in arithmetic datetime and interval expressions as *numbers of days.*
FALSE - You *can* select LOB columns and LOB attributes.
You cannot select LOB columns and LOB attributes. T/F
DATE
____ columns cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'
LOB
a special form of scalar data type representing a large scalar value of binary or character data. are subject to some restrictions that do not affect other scalar types because of their size. see: https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#BABHIJHI
.00013
actual data: .000127 col specified as: NUMBER(4,5) stored as?
.0000012
actual data: .0000012 col specified as: NUMBER(2,7) stored as?
.0000012
actual data: .00000123 col specified as: NUMBER(2,7) stored as?
.00012
actual data: .00012 col specified as: NUMBER(4,5) stored as?
SQL*Plus
an interactive and batch query tool that is installed with every Oracle Database server or client installation. It has a command-line user interface and a Web-based user interface
user-defined data type (UDT)
are data types use Oracle built-in data types and other user-defined data types as the building blocks of object types that model the structure and behavior of data in applications. The sections that follow describe the various categories of user-defined types.
Rowid
are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, and the plus sign (+) and forward slash (/). are not available directly. You can use the supplied package DBMS_ROWID to interpret rowid contents. The package functions extract and provide information on the four rowid elements listed above.
oracle built in data types
character, number, long, raw, datetime, large object, rowid
lob types
blob, clob, nclob, bfile (bcnb)
datatypes
built in, ANSI, user defined, oracle supplied
object attributes
built-in types or other user-defined types. Attributes model the structure of the real-world entity.
TIMESTAMP WITH LOCAL TIME ZONE
data stored in the database is normalized to the database time zone, and the time zone information is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. This data type is useful for date information that is always to be displayed in the time zone of the client system in a two-tier application.
nonscalar (sometimes called a "collection")
data type that contains a set of values
scalar
data type that contains an atomic value
object method
functions or procedures written in PL/SQL and stored in the database, or written in a language like C or Java and stored externally. Methods implement operations the application can perform on the real-world entity.
Oracle SQL Developer
graphical tool that lets you browse, create, edit, and delete (drop) database objects, edit and debug PL/SQL code, run SQL statements and scripts, manipulate and export data, and create and view reports can connect to any target Oracle Database schema schema using standard Oracle Database authentication can perform operations on objects in the database. an also connect to schemas for selected third-party (non-Oracle) databases; view metadata and data in these databases, and migrate these databases to Oracle.
NUMBER(p,s)
having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. value requires from 1 to 22 bytes.
object name
identifies the object type uniquely within that schema.
The Oracle precompilers, Pro*C/C++ and Pro*COBOL
interpret embedded SQL statements and translate them into statements that can be understood by C/C++ and COBOL compilers, respectively.
Oracle Application Express
is a hosted environment for developing and deploying database-related Web applications. SQL Workshop is a component of Oracle Application Express that lets you view and manage database objects from a Web browser. SQL Workshop offers quick access to a SQL command processor and a SQL script repository.
Oracle JDeveloper
is a multiple-platform integrated development environment supporting the complete lifecycle of development for Java, Web services, and SQL. It provides a graphical interface for executing and tuning SQL statements and a visual schema diagrammer (database modeler). It also supports editing, compiling, and debugging PL/SQL applications.
data type
is either scalar or nonscalar
The Oracle Call Interface (OCI)
lets you embed SQL statements in C programs.
boundary case
occurs during the period when daylight saving goes into or comes out of effect. For example, in the US-Pacific region, when daylight saving goes into effect, the time changes from 2:00 a.m. to 3:00 a.m. The one hour interval between 2 and 3 a.m. does not exist. When daylight saving goes out of effect, the time changes from 2:00 a.m. back to 1:00 a.m., and the one-hour interval between 1 and 2 a.m. is repeated.
fractional_seconds_precision
specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this data type, the value can be a number in the range 0 to 9. The default is 6.
character data types
store character (alphanumeric) data, which are words and free-form text, in the database character set or national character set. They are less restrictive than other data types and consequently have fewer properties. For example, character columns can store all alphanumeric values, but NUMBER columns can store only numeric values. is stored in strings with byte values corresponding to one of the character sets, such as 7-bit ASCII or EBCDIC, specified when the database was created. Oracle Database supports both single-byte and multibyte character sets.
object identifier (OID)
uniquely identifies an object and enables you to reference the object from other objects or from relational tables. A data type category called REF represents such references. A REF data type is a container for an object identifier. REF values are pointers to objects.
timestamp [with local time zone] and interval day to second
what are the 2 date types with fractional seconds precision
