Transcription
Oracle TimesTenIn-Memory DatabaseSQL Reference GuideRelease 7.0B31682-03
Copyright 1996, 2007, Oracle. All rights reserved.ALL SOFTWARE AND DOCUMENTATION (WHETHER INHARD COPY OR ELECTRONIC FORM) ENCLOSED AND ONTHE COMPACT DISC(S) ARE SUBJECT TO THE LICENSEAGREEMENT.The documentation stored on the compact disc(s) may be printed bylicensee for licensee’s internal use only. Except for the foregoing,no part of this documentation (whether in hard copy or electronicform) may be reproduced or transmitted in any form by any means,electronic or mechanical, including photocopying, recording, orany information storage and retrieval system, without the priorwritten permission of TimesTen Inc.Oracle, JD Edwards, PeopleSoft, Retek, TimesTen, the TimesTenicon, MicroLogging and Direct Data Access are trademarks or registered trademarks of Oracle Corporation and/or its affiliates. Othernames may be trademarks of their respective owners.The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure andare also protected by copyright, patent, and other intellectual andindustrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtaininteroperability with other independently created software or asspecified by law, is prohibited.The information contained in this document is subject to changewithout notice. If you find any problems in the documentation,please report them to us in writing. This document is not warrantedto be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs maybe reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.September 2007Printed in the United States of America
ContentsAbout this GuideTimesTen documentation . .Background reading . . . .Conventions used in this guideTechnical Support . . . . .1.1235Data TypesType specifications . . . . . . . . . . . . . . . . . . . . . . . . 8ANSI SQL data types . . . . . . . . . . . . . . . . . . . . . . . 15Types supported for backward compatibility in Oracle type mode . . . . . 18TimesTen type mapping . . . . . . . . . . . . . . . . . . . . . . 21Character data types . . . . . . . . . . . . . . . . . . . . . . . 24CHAR type . . . . . . . . . . . . . . . . . . . . . . . . . 24NCHAR type . . . . . . . . . . . . . . . . . . . . . . . . . 25VARCHAR2 type . . . . . . . . . . . . . . . . . . . . . . . 26NVARCHAR2 type . . . . . . . . . . . . . . . . . . . . . . 27Numeric data types . . . . . . . . . . . . . . . . . . . . . . . . 29Exact and approximate types. . . . . . . . . . . . . . . . . . . 29TT TINYINT type . . . . . . . . . . . . . . . . . . . . 29TT SMALLINT type . . . . . . . . . . . . . . . . . . . 30TT INTEGER type . . . . . . . . . . . . . . . . . . . . 30TT BIGINT type . . . . . . . . . . . . . . . . . . . . . 31NUMBER type . . . . . . . . . . . . . . . . . . . . . . 32Floating-Point numbers . . . . . . . . . . . . . . . . . . . 35BINARY FLOAT. . . . . . . . . . . . . . . . . . . . . 35BINARY DOUBLE . . . . . . . . . . . . . . . . . . . . 35FLOAT and FLOAT (n) . . . . . . . . . . . . . . . . . . 36Binary and Varbinary types. . . . . . . . . . . . . . . . . . . . . 37Numeric precedence . . . . . . . . . . . . . . . . . . . . . . . 38Datetime data types. . . . . . . . . . . . . . . . . . . . . . . . 39TIME type . . . . . . . . . . . . . . . . . . . . . . . . . . 39TT DATE type . . . . . . . . . . . . . . . . . . . . . . . . 39DATE type. . . . . . . . . . . . . . . . . . . . . . . . . . 39TT TIMESTAMP type . . . . . . . . . . . . . . . . . . . . . 39TIMESTAMP type . . . . . . . . . . . . . . . . . . . . . . 39TimesTen interval . . . . . . . . . . . . . . . . . . . . . . . . 40Using INTERVAL types . . . . . . . . . . . . . . . . . . . . 40Using DATE and TIME types . . . . . . . . . . . . . . . . . . 40Handling TIMEZONE conversions . . . . . . . . . . . . . . . . 41iii
23Date-time and interval types in arithmetic operations . . . . . . . . .Restrictions on date-time and interval arithmetic operations . . . .Storage requirements . . . . . . . . . . . . . . . . . . . . . . .Data type comparison rules . . . . . . . . . . . . . . . . . . . .Data conversion . . . . . . . . . . . . . . . . . . . . . . . . .Implicit Data Conversion . . . . . . . . . . . . . . . . . .NULL values . . . . . . . . . . . . . . . . . . . . . . . . . .INF and NAN . . . . . . . . . . . . . . . . . . . . . . . . .Overflow and truncation . . . . . . . . . . . . . . . . . . . . .Underflow . . . . . . . . . . . . . . . . . . . . . . . . . . .Replication limits . . . . . . . . . . . . . . . . . . . . . . . .TimesTen Type Mode (Backward Compatibility) . . . . . . . . . . . .Data types supported in TimesTen type mode . . . . . . . . . . . . .Oracle data types supported in TimesTen type mode . . . . . . . . . .4143444648484850535353555661Basic names . . . .Owner names . . . .Compound identifiersDynamic parameters .65656666Names.ExpressionsROWID specification . . . . . . . . . . . . . . . . . . . . . . 68ROWNUM specification . . . . . . . . . . . . . . . . . . . . . 69Expression specification . . . . . . . . . . . . . . . . . . . . . 70Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Aggregate functions . . . . . . . . . . . . . . . . . . . . . . . 76Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Format Models . . . . . . . . . . . . . . . . . . . . . . . . . 84Number format models . . . . . . . . . . . . . . . . . . . . 84Number format elements . . . . . . . . . . . . . . . . . . 84Datetime format models . . . . . . . . . . . . . . . . . . . . 88Datetime format elements . . . . . . . . . . . . . . . . . 88Format Model for ROUND and TRUNC Date Functions . . . . . . . 91Format Model for TO CHAR of TimesTen types . . . . . . . . . . 92ABS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94ADD MONTHS . . . . . . . . . . . . . . . . . . . . . . . . 95ASCIISTR . . . . . . . . . . . . . . . . . . . . . . . . . . . 97CASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98CAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100CHR . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101CEIL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102iv Oracle TimesTen In-Memory Database SQL Reference Guide
COALESCE . . . . . .CONCAT. . . . . . . .DECODE . . . . . . .EXTRACT . . . . . . .FLOOR . . . . . . . .GREATEST. . . . . . .LEAST . . . . . . . .LOWER and UPPER . . .LPAD . . . . . . . . .LTRIM. . . . . . . . .MOD . . . . . . . . .NCHR . . . . . . . . .NLSSORT . . . . . . .NUMTODSINTERVAL . .NUMTOYMINTERVAL .NVL . . . . . . . . .POWER . . . . . . . .ROUND (date) . . . . .ROUND (expression) . . .RPAD . . . . . . . . .RTRIM . . . . . . . .SIGN . . . . . . . . .SQRT . . . . . . . . .String functions . . . . .SUBSTR . . . . . .INSTR . . . . . . .LENGTH . . . . . .SYSDATE and GETDATE .TO CHAR . . . . . . .TO DATE . . . . . . .TO NUMBER . . . . .TRIM . . . . . . . . .TRUNC (date). . . . . .TRUNC (expression) . . .TT HASH . . . . . . .UNISTR . . . . . . . .USER functions . . . . .CURRENT USER . .USER. . . . . . . .SESSION USER . . .SYSTEM USER . . 153154155156156156156157v
4Search ConditionsSearch condition general syntax .ALL/ NOT IN predicate (subquery)ALL/NOT IN predicate (value list)ANY/ IN predicate (subquery) . .ANY/ IN predicate (value list) . .BETWEEN predicate. . . . . .Comparison predicate . . . . .EXISTS predicate . . . . . . .IS INFINITE predicate . . . . .IS NAN predicate . . . . . . .IS NULL predicate . . . . . .LIKE predicate . . . . . . . .NCHAR and NVARCHAR2 .5.159162164167170173175177179180181182184SQL StatementsAccess Control and SQL statements . . . . . . . . . . . . . . . . 186ALTER ACTIVE STANDBY PAIR . . . . . . . . . . . . . . . . 187ALTER CACHE GROUP . . . . . . . . . . . . . . . . . . . . 191ALTER REPLICATION . . . . . . . . . . . . . . . . . . . . 194ALTER SESSION . . . . . . . . . . . . . . . . . . . . . . . 206ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . 210ALTER USER . . . . . . . . . . . . . . . . . . . . . . . . 226COMMIT . . . . . . . . . . . . . . . . . . . . . . . . . . 228CREATE ACTIVE STANDBY PAIR . . . . . . . . . . . . . . . 229CREATE CACHE GROUP . . . . . . . . . . . . . . . . . . . 236User and system managed cache groups . . . . . . . . . . . . . 236CREATE READONLY CACHE GROUP . . . . . . . . . . . 237CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP238CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP. 238CREATE USERMANAGED CACHE GROUP . . . . . . . . . 239AUTOREFRESH in Cache Groups . . . . . . . . . . . . . . . 243CREATE INDEX . . . . . . . . . . . . . . . . . . . . . . . 250CREATE MATERIALIZED VIEW . . . . . . . . . . . . . . . . 254Restrictions on the materialized view and detail tables . . . . . . 254Restrictions on the MATERIALIZED VIEW query . . . . . . . 255CREATE REPLICATION . . . . . . . . . . . . . . . . . . . . 258CHECK CONFLICTS . . . . . . . . . . . . . . . . . . . . 266CREATE SEQUENCE . . . . . . . . . . . . . . . . . . . . . 275Incrementing SEQUENCE values with CURRVAL and NEXTVAL 276CREATE TABLE . . . . . . . . . . . . . . . . . . . . . . . 279Column Definition . . . . . . . . . . . . . . . . . . . . . 286vi Oracle TimesTen In-Memory Database SQL Reference Guide
CREATE USER . . . . . . . . . . . . . . . . . . . . . . . . 299CREATE VIEW . . . . . . . . . . . . . . . . . . . . . . . . 301Restrictions on the VIEW query . . . . . . . . . . . . . . . 301Restrictions on the VIEW . . . . . . . . . . . . . . . . . . 301DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . 303DROP ACTIVE STANDBY PAIR . . . . . . . . . . . . . . . . . 305DROP CACHE GROUP. . . . . . . . . . . . . . . . . . . . . 306DROP INDEX. . . . . . . . . . . . . . . . . . . . . . . . . 307DROP SEQUENCE . . . . . . . . . . . . . . . . . . . . . . 309DROP REPLICATION . . . . . . . . . . . . . . . . . . . . . 310DROP TABLE . . . . . . . . . . . . . . . . . . . . . . . . 311DROP USER . . . . . . . . . . . . . . . . . . . . . . . . . 312DROP VIEW . . . . . . . . . . . . . . . . . . . . . . . . . 313FLUSH CACHE GROUP . . . . . . . . . . . . . . . . . . . . 314GRANT . . . . . . . . . . . . . . . . . . . . . . . . . . . 316INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . 318SingleRowValues . . . . . . . . . . . . . . . . . . . . . . 319INSERT SELECT . . . . . . . . . . . . . . . . . . . . . . . 321LOAD CACHE GROUP . . . . . . . . . . . . . . . . . . . . 322MERGE . . . . . . . . . . . . . . . . . . . . . . . . . . . 325REFRESH CACHE GROUP . . . . . . . . . . . . . . . . . . . 329REVOKE . . . . . . . . . . . . . . . . . . . . . . . . . . 332ROLLBACK . . . . . . . . . . . . . . . . . . . . . . . . . 334SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . 335SelectList . . . . . . . . . . . . . . . . . . . . . . . . . 347TableSpec . . . . . . . . . . . . . . . . . . . . . . . . . 350DerivedTable . . . . . . . . . . . . . . . . . . . . . . . . 351JoinedTable . . . . . . . . . . . . . . . . . . . . . . . . 351TRUNCATE TABLE . . . . . . . . . . . . . . . . . . . . . . 354UNLOAD CACHE GROUP . . . . . . . . . . . . . . . . . . . 356UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . 358Join Update . . . . . . . . . . . . . . . . . . . . . . . . 3606Access Control PrivilegesPrivilege descriptions . . . . . . . . . . . . .Operations requiring instance Administrator privilegeSQL operations . . . . . . . . . . . . . .Utilities . . . . . . . . . . . . . . . . .Operations requiring ADMIN privilege . . . . . .Attributes . . . . . . . . . . . . . . . .Built-in Procedures . . . . . . . . . . . .SQL operations . . . . . . . . . . . . . .363364364364365365365366vii
Utilities. . . . . . . . . . . . . . . . . .Utility C API . . . . . . . . . . . . . . .XLA Functions. . . . . . . . . . . . . . .Operations requiring CONNECT privilege . . . . .Operations requiring CREATE DATASTORE privilegeOperations requiring DDL privilege . . . . . . . .Built-in Procedures . . . . . . . . . . . . .SQL operations . . . . . . . . . . . . . .Operations requiring WRITE privilege . . . . . . .Built-in Procedures . . . . . . . . . . . . .SQL operations . . . . . . . . . . . . . .XLA functions . . . . . . . . . . . . . . .Operations requiring SELECT privilege . . . . . .Built-in Procedures . . . . . . . . . . . . .SQL operations . . . . . . . . . . . . . .Utilities. . . . . . . . . . . . . . . . . 403404408409410.411415System and Replication TablesSystem table list. . . . . . . . . .Replication table list . . . . . . . .Tables reserved for internal or future useSYS.CACHE GROUP . . . . . . .SYS.COLUMNS . . . . . . . . .SYS.COL STATS . . . . . . . . .SYS.DUAL . . . . . . . . . . .SYS.INDEXES . . . . . . . . . .SYS.MONITOR . . . . . . . . .SYS.PLAN . . . . . . . . . . .SYS.SEQUENCES . . . . . . . .SYS.SYNONYMS . . . . . . . .SYS.TABLES . . . . . . . . . .SYS.TBL STATS . . . . . . . . .SYS.TCOL STATS . . . . . . . .SYS.TINDEXES . . . . . . . . .SYS.TRANSACTION LOG API . .SYS.TTABLES . . . . . . . . . .SYS.TTBL STATS . . . . . . . .SYS.VIEWS . . . . . . . . . . .SYS.XLASUBSCRIPTIONS . . . .TTREP.REPELEMENTS . . . . . .TTREP.REPLICATIONS . . . . . .viii Oracle TimesTen In-Memory Database SQL Reference Guide
TTREP.REPNETWORK. . .TTREP.REPPEERS . . . .TTREP.REPSTORES . . . .TTREP.REPSUBSCRIPTIONSTTREP.REPTABLES . . . .TTREP.TTSTORES . . . .8.416417420421423428Reserved WordsIndexix
x Oracle TimesTen In-Memory Database SQL Reference Guide
About this GuideOracle TimesTen In-Memory Database is a high-performance, in-memory datamanager that supports the ODBC (Open DataBase Connectivity) and JDBC (JavaDataBase Connectivity) interfaces.This guide is for application developers who use and administer TimesTen. Itprovides a reference for TimesTen SQL statements, expressions, and functions,including TimesTen SQL extensions.To work with this guide, you should understand how database systems work. Youshould also have knowledge of SQL (Structured Query Language). See“Background reading” on page 2 if you are not familiar with these interfaces.TimesTen documentationTimesTen documentation is available on the product distribution media and onthe Oracle Technology tion/timesten doc.html.Including this guide, the TimesTen documentation set consists of thesedocuments:Book TitlesDescriptionOracle TimesTen In-MemoryDatabase Installation GuideContains information needed to install and configureTimesTen on all supported platforms.Oracle TimesTen In-MemoryDatabase IntroductionDescribes all the available features in the OracleTimesTen In-Memory Database.Oracle TimesTen In-MemoryDatabase Operations GuideProvides information on configuring TimesTen andusing the ttIsql utility to manage a data store. Thisguide also provides a basic tutorial for TimesTen.Oracle TimesTen In-MemoryDatabase C Developer’s andReference Guideand theOracle TimesTen In-MemoryDatabase Java Developer’sand Reference GuideProvide information on how to use the full set ofavailable features in TimesTen to develop andimplement applications that use TimesTen.Oracle TimesTen In-MemoryDatabase API ReferenceGuideDescribes all TimesTen utilities, procedures, APIs andprovides a reference to other features of TimesTen.1
Oracle TimesTen In-MemoryDatabase SQL ReferenceGuideContains a complete reference to all TimesTen SQLstatements, expressions and functions, includingTimesTen SQL extensions.Oracle TimesTen In-MemoryDatabase Error Messagesand SNMP TrapsContains a complete reference to the TimesTen errormessages and information on using SNMP Traps withTimesTen.Oracle TimesTen In-MemoryDatabase TTClasses GuideDescribes how to use the TTClasses C API to usethe features available in TimesTen to develop andimplement applications.TimesTen to TimesTenReplication GuideProvides information to help you understand howTimesTen Replication works and step-by-stepinstructions and examples that show how to performthe most commonly needed tasks.This guide is for application developers who use andadminister TimesTen and for system administratorswho configure and manage TimesTen Replication.TimesTen Cache Connect toOracle GuideDescribes how to use Cache Connect to cache Oracledata in TimesTen data stores. This guide is fordevelopers who use and administer TimesTen forcaching Oracle data.Oracle TimesTen In-MemoryDatabase TroubleshootingProcedures GuideProvides information and solutions for handlingproblems that may arise while developing applicationsthat work with TimesTen, or while configuring ormanaging TimesTen.Background readingFor a Java reference, see: Horstmann, Cay and Gary Cornell. Core Java(TM) 2, Volume I-Fundamentals (7th Edition) (Core Java 2). Prentice Hall PTR; 7 edition(August 17, 2004).A list of books about ODBC and SQL is in the Microsoft ODBC manualincluded in your developer’s kit. Your developer’s kit includes the appropriateODBC manual for your platform: Microsoft ODBC 3.0 Programmer’s Reference and SDK Guide provides allrelevant information on ODBC for Windows developers.2 Oracle TimesTen In-Memory Database SQL Reference Guide
Microsoft ODBC 2.0 Programmer’s Reference and SDK Guide, includedonline in PDF format, provides information on ODBC for UNIX developers.For a conceptual overview and programming how-to of ODBC, see: Kyle Geiger. Inside ODBC. Redmond, WA: Microsoft Press. 1995.For a review of SQL, see: Melton, Jim and Simon, Alan R. Understanding the New SQL: A CompleteGuide. San Francisco, CA: Morgan Kaufmann Publishers. 1993. Groff, James R. / Weinberg, Paul N. SQL: The Complete Reference, SecondEdition. McGraw-Hill Osborne Media. 2002.For information about Unicode, see: The Unicode Consortium, The Unicode Standard, Version 5.0,Addison-Wesley Professional, 2006. The Unicode Consortium Home Page at http://www.unicode.orgConventions used in this guideTimesTen supports multiple platforms. Unless otherwise indicated, theinformation in this guide applies to all supported platforms. The term Windowsrefers to Windows 2000, Windows XP and Windows Server 2003. The termUNIX refers to Solaris, Linux, HP-UX, Tru64 and AIX.TimesTen documentation uses these typographical conventions:If you see.It means.code fontCode examples, filenames, and pathnames.For example, the .odbc.ini. or ttconnect.ini file.italic codefontA variable in a code example that you must replace.For example:Driver install dir/lib/libtten.slReplace install dir with the path of your TimesTeninstallation directory.About this Guide 3
TimesTen documentation uses these conventions in command line examples anddescriptions:If you see.It means.fixed widthitalicsVariable; must be replaced with an appropriate value. Insome cases, such as for parameter values in built-inprocedures, you may need to single quote (' ') the value.[ ]Square brackets indicate that an item in a command lineis optional.{ }Curly braces indicated that you must choose one of theitems separated by a vertical bar ( ) in a command line. A vertical bar (or pipe) separates arguments that you mayuse more than one argument on a single command line.An ellipsis (. . .) after an argument indicates that you mayuse more than one argument on a single command line.%The percent sign indicates the UNIX shell prompt.#The number (or pound) sign indicates the UNIX rootprompt.TimesTen documentation uses these variables to identify path, file and usernames:If you see.It means.install dirThe path that represents the directory where the currentrelease of TimesTen is installed.TTinstanceThe instance name for your specific installation ofTimesTen. Each installation of TimesTen must beidentified at install time with a unique alphanumericinstance name. This name appears in the install path. Theinstance name “giraffe” is used in examples in this guide.bits or bbTwo digits, either 32 or 64, that represent either the 32-bitor 64-bit operating system.release or rrTwo digits that represent the first two digits of the currentTimesTen release number, with or without a dot. Forexample, 51 or 7.0 represents TimesTen Release 7.0.jdk versionTwo digits that represent the version number of themajor JDK release. Specifically, 14 represent JDK 1.4;5 represents JDK 5.4 Oracle TimesTen In-Memory Database SQL Reference Guide
timestenA sample name for the TimesTen instance administrator.You can use any legal user name as the TimesTenadministrator. On Windows, the TimesTen instanceadministrator must be a member of the Administratorsgroup. Each TimesTen instance can have a uniqueinstance administrator name.DSNThe data source name.Technical SupportFor information about obtaining technical support for TimesTen products, go tothe following Web About this Guide 5
6 Oracle TimesTen In-Memory Database SQL Reference Guide
1Data TypesA data type defines a set of values. A reference to a data type specifies the set ofvalues that can occur in a given context.A data type is associated with each value retrieved from a table or computed inan expression and each constant.TimesTen follows the ODBC standard for type conversion.A discussion of this standard is not included in this guide. See Appendix D eitherin the Microsoft ODBC 2.0 Programmer’s Reference and SDK Guide or theMicrosoft ODBC 3.0 Developer’s Kit and Programmer’s Reference for moreinformation.If you are using the Cache Connect feature of TimesTen, see "Differencesbetween Oracle and TimesTen tables" in Chapter 8, “Compatibility BetweenTimesTen and Oracle” of the TimesTen Cache Connect to Oracle Guide. Thissection compares valid data types for creating cache group columns, as well astype conversions for passthrough queries.7
Type specificationsTimesTen supports the following data types in the default Oracle type mode. Thetype mode is a data store creation attribute. TypeMode 0 indicates Oracle typemode. TypeMode 1 indicates TimesTen mode.For more information on types modes, see "TypeMode" in Oracle TimesTen InMemory Database API Reference Guide.Data typeDescriptionCHAR[ACTER] [(n [BYTE CHAR])]Fixed-length character string of length n bytesor characters. Default is 1 byte.BYTE indicates that the column has bytelength semantics. Legal values for n rangefrom a minimum of 1 byte to a maximum8300 bytes.CHAR indicates that the column has characterlength semantics. The minimum CHAR lengthis 1 character. The maximum CHAR lengthdepends on how many characters fit in 8300bytes. This is determined by the databasecharacter set in use. For character setAL32UTF8, up to four bytes per charactermay be needed, so the CHAR length limitranges from 2075 to 8300 depending on thecharacter set.A zero-length string is interpreted as NULL.CHAR data is padded to the maximumcolumn size with trailing blanks. Blankpadded comparison semantics are used.Alternatively, specify ORA CHAR[(n [BYTE CHAR])].8 Oracle TimesTen In-Memory Database SQL Reference Guide
Data type (continued)DescriptionNCHAR[(n)]Fixed-length string of length n two-byteUnicode characters.The number of bytes required is 2* n where nis the specified number of characters. Ncharcharacter limits are 1/2 the byte limits so themaximum size is 4150. Default and minimumbytes of storage is 2n (2).A zero-length string is interpreted as NULL.NCHAR data is padded to the maximumcolumn size with U 0020 SPACE. Blankpadded comparison semantics are used.Alternatively, specify ORA NCHAR[(n)].VARCHAR[2] (n [BYTE CHAR])Variable-length character string havingmaximum length n bytes or characters.BYTE indicates that the column has bytelength semantics.Legal values for n rangefrom a minimum of 1 byte to a maximum4194304 (222) bytes. You must specify n.CHAR indicates that the column has characterlength semantics.A zero-length string is interpreted as NULL.Nonpadded comparison semantics are used.Do not use the VARCHAR type. Although it iscurrently synonymous with VARCHAR2, theVARCHAR type is scheduled to be redefined.Alternatively, specifyORA VARCHAR2 (n [BYTE CHAR]).Data Types 9
Data type (continued)DescriptionNVARCHAR2(n)Variable-length string of n two-byte Unicodecharacters.The number of bytes required is 2* n where nis the specified number of characters.NVARCHAR2 character limits are 1/2 thebyte limits so the maximum size is 2,097,152(221). You must specify n.A zero-length string is interpreted as NULL.Nonpadded comparison semantics are used.Alternatively, specifyORA NVARCHAR2(n).TT TINYINTUnsigned integer ranging from 0 to 255 (28-1).Use TT TINYINT rather than the NUMBERdata type. TT TINYINT is more compact andoffers faster performance than the NUMBERtype. If you need to store greater than 19 digitintegers, use NUMBER (p) where p 19.Since TT TINYINT is unsigned, the negationof a TT TINYINT is TT SMALLINT.TT SMALLINTA native signed 16 bit integer in the range –32,768 –(215) to 32,767 (215–1).Use TT SMALLINT rather than SMALLINT.SMALLINT maps to the NUMBER data type.TT SMALLINT is more compact and offersfaster performance than the NUMBER type. Ifyou need to store greater than 19 digitintegers, use NUMBER (p) where p 19.TT INT[EGER]A signed integer in the range –2,147,483,648 -(231) to 2,147,483,647 (231–1).TT INTEGER is a native signed integer datatype. Use TT INTEGER rather thanINTEGER. INTEGER maps to the NUMBERdata type. TT INTEGER is more compact andoffers faster performance than the NUMBERtype. If you need to store greater than 19 digitintegers, use NUMBER (p) where p 19.10 Oracle TimesTen In-Memory Database SQL Reference Guide
Data type (continued)DescriptionTT BIGINTA signed 8-byte integer inthe range-9,223,372,036,854,775,808-(263) to 9,223,372,036,854,775,807(263 - 1).Use TT BIGINT rather than the NUMBERdata type. TT BIGINT is more compact andoffers faster performance than the NUMBERtype. If you need to store greater than 19 digitintegers, use NUMBER (p) where p 19.NUMBER [(precision [,scale])]Number having precision and scale. Theprecision ranges from 1 to 38 decimal. Thescale ranges from -84 to 127. Both precisionand scale are optional.If you do not specify a precision or a scale,TimesTen assumes the maximum precision of38 and flexible scale.NUMBER supports scale precision andnegative scale.NUMBER stores zero as well as positive andnegative fixed numbers with absolute valuesfrom 1.0 x 10-130 to (but not including)1.0 x 10126. If you specify an arithmeticexpression whose value has an absolute valuegreater than or equal to 1.0 x 10126, thenTimesTen returns an error.BINARY FLOAT32-bit floating-point number.BINARY FLOAT is a single-precision nativefloating-point type. Supports Inf, -Inf andNaN values. BINARY FLOAT is anapproximate numeric value consisting of anexponent and mantissa. You can useExponential or E-notation. BINARY FLOAThas binary precision 24.Minimum positive finite value:1.17549E-38FMaximum positive finite value:3.40282E 38FData Types 11
Data type (continued)DescriptionBINARY DOUBLE64-bit floating -point number.BINARY DOUBLE is a double-precisionnative floating point number. Supports Inf,-Inf and Nan values. BINARY DOUBLE isan approximate numeric value consisting of anexponent and mantissa. You can useExponential or E-notation.BINARY DOUBLE has binary precision 53.Minimum positive finite value:2.22507485850720E-308Maximum positive finite value:1.79769313486231E 308BINARY (n)Fixed-length binary value of n bytes. Legalvalues for n range from 1 to 8300.BINARY data is padded to the maximumcolumn size with trailing zeroes.Alternatively, specify TT BINARY (n).VARBINARY (n)Variable-length binary value having maximumlength n bytes. Legal values for n range from 1to 4194304 (222).Alternatively, specify TT VARBINARY(n).TIMEA time of day between 00:00:00 (12 midnight)and 23:59:59 (11:59:59 pm), inclusive. Theformat
Microsoft ODBC 3.0 Programmer’s Reference and SDK Guide provides all relevant information on ODBC for Windows developers. Oracle TimesTen In-Memory Database SQL Reference Guide Contains a complete reference to all TimesTen SQL statements, expressions and functions, including TimesTen
