PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum NEW! Additional Oracle Whitepapers and Reports, available free for a limited time to PSOUG visitors.

CODE Oracle Code Library. JOBS Find Or Post Oracle Jobs. Search the Reference Library pages: Free Oracle Magazine Subscriptions and Oracle White Papers.

Schemas SYS and SYSTEM schemas. SYS and SYSTEM schemas. The the way primary and secondary datafiles are mapped in SQL Server does not relate to how data files are mapped in Oracle. The log file concept and architecture are completely different. Log Files Not Used For Rollback. Referential ON DELETE CASCADE. Referential ON DELETE SET NULL. REF Nested Table Constraint. Database Linkages Linked Server.

Functions user defined Function. IN and OUT Parameters Only. IN, OUT, and IN-OUT Parameters. Materialized Views no equivalent.

Materialized Views of any type. Operators user defined no equivalent. Procedures user defined Procedure. Surrogate Key Generator no equivalent. Global Temporary Table con commit delete rows. Global Temporary Table on commit preserve rows.

Type Body with Methods. Date Operators no equivalent. Hierarchical Operators no equivalent. Multiset Operators no equivalent. DDL Triggers no equivalent. Instead-Of Triggers VIEW TRIGGER. System Triggers no equivalent. Table Triggers no equivalent. Analytic Functions Running average. Running count by partition. Population covariance of a set of pairs. Sample covariance of a set of pairs. Cumulative distribution in a group.

Rank within a group without gaps. Row ranked first by DENSE RANK. First value of an ordered set. Provides access to a row by offset. Row ranked last by DENSE RANK. Last value of an ordered set. Maximum value by partition. Minimum value by partition. Divides an ordered dataset into buckets. An inverse distribution function. Rank of a value in a group. Computes ratio of a value to the sum of a set. Assigns row numbers by partition. Square root of the population variance.

Cumulative sample standard deviation. Population variance of a set. Sample variance of a set. Variance of an expression. Collection Functions Number of elements in a nested table. Creates a nested table from selected rows. Creates a nested table of nonempty subsets. Of the specified cardinality. Converts a nested table into a unique set.

Conversion Functions ASCII string into the DB character set. Bitvector to a number. One data type to another. String to a unicode string. One character set to another. Unicode string to a string. Char containing hexidecimal digits to raw. Number into a Day-to-Second interval. Number into a Year-to-Month interval.

Raw into a hexidecimal containing string. Raw into a hexidecimal containing 'N' string. Raw into a hexidecimal object REF. An SCN to its timestamp equivalent. Convert to CHAR or VARCHAR2 data type. Convert to CLOB data type. Convert to DATE data type. Convert to Day-to-Second Interval data type.

LONG or LONG RAW to LOB data type. Single byte to corresponding multi-byte. Convert to NCHAR data type. Convert to NCLOB data type. Convert to NUMBER data type. Multi-byte to corresponding single byte. Convert to TIMESTAMP data type. To TIMESTAMP WITH TIMEZONE data type. Convert to Year-to-Month Interval data type. String to UTF8 or UTF Convert values to a date. Date-Time Functions Date addition.

Current date and time low precision. Current date and time high precision. Current date and time low second. Hours since a starting date and time.

Minutes since a starting date and time. Seconds since a starting date and time. Weeks since a starting date and time. Years since a starting date and time.

Extract part from date-time or interval. Alter time zone information. Largest of a set of dates. Smallest of a set of dates. Quarter of the calendar year. Rounds date to unit specified. Current session's time zone. Convert date part to name.

Convert date part to number. Name of the day of the week. Integer representing the day of the week. Integer representing the hour. Integer representing the minute. Integer representing the month of the year. Integer representing the seconds.

Integer representing the year. Data Mining Functions Cluster ID of the cluster with highest probability. Degree of confidence of membership of a row. Varray of objects of possible clusters. Feature ID with highest coefficient value. Varray of objects of all possible features. Value of a given feature.

Best prediction for the specified model. Cost measure of a given prediction. XML with model specific scoring. Probability for a given prediction. Varray of objects with all possible classes. Name of language in use. Value with the named context namespace. Username in the current context. Miscellaneous Functions Null BLOB. Length of an Forex learning ratekau column.

ID of NLS character set. Name of NLS character set from ID. Numeric identifier of current exception code. Error message of current exception code. Hierarchical path of column from root to node. Function that builds descending index values.

Index leaf block ID scan. Model Functions Use left side value on right how to earn money on gaia calculation.

Iterate a set number of times through data. Returns expr1 prior to execution. Reference prior model values. Null Handling Functions Evaluate one or both operands may be NULL. Returns NULL is expr1 and expr2 both NULL. Convert to string if NULL. Substitute if NULL or if NOT Currency forex forex forex info info knowforex.info trading. Numeric Handling Functions Absolute value.

Arc tangent1 divided by the arc tangent2. Compute AND operation on bits. Pearson's coefficient of correlation. Spearman's Rho correlation coefficient. Number of non-null values. Rank of row in an ordered group. Largest of multiple values. Tests if numeric conversion will work. Smallest of multiple values. Middle value of the set. Remainder from modulus using floor.

Returns alternate number if value not a number. Inverse distribution continuous dist. Inverse distribution discrete distribution model. Returns the value of Pi. Raise value to exponent power. Radians from a numeric expression. Linear regression - avg of the independent var. Linear regression - non-null number pairs. Linear regression - y intercept.

Linear regression - coefficient of determination. Linear regression - slope of the line. Linear regression - auxiliary function. Remainder from modulus using round. Round to integer place. Exact probability test for dichotomous variables. Crosstabulation analysis of nominal variables. Whether two values are significantly different. Value with the greatest frequency. One-way analysis of variance function.

Oracle Sybase Comparison

Wilcoxon Signed Ranks test of paired samples. Truncates to specified decimal places. Object Functions Object sap bit-xor of an argument. Creates a REF to an object row. Returns a REF of an object instance. Typeid of the most specific type of the operand. Returns object instance from an object table.

String Handling Functions Get the ASCII value of a character. Converts From One Character Set To Forex tnd eur. Capitalize first letter of each word in string.

Sap bit-xor a string into another string. Starting point of pattern in a string. Starting point in bytes of pattern in a string. Starting point in Unicode of pattern in a string. Starting point in UCS2 of pattern in a string. What is block deal session in stock market point in UCS4 of pattern in a string. Length of character string in characters.

Length of character string in bytes. Convert characters to lower case. NLS initial letter upper case. String of bytes used to sort a string. Concatenates a specified number of times. Replace part of a string with a string. Reverses a character expression. Similarity between two strings.

Pad right side of character string. Phonetic representation of character string. String replacing a number of characters. Substring in Unicode characters. Translate character string using character set. Change declared type of an expression. Left and right trim a string. Convert characters to upper case. XML Handling Functions Append value to target XML as a child node. Deletes node s matched by XPath expression.

Does specified node exist. Returns XMLType instance containing fragment. Returns a scalar value of the resultant node. Inserts value to target XML as a child node. Inserts value to target XML before named node. Relative path that leads to resource. Generates a URL of datatype DBURIType. Aggregates XML documents or fragments.

Returns XMLType containing an XML doc. Returns XMLType instance with updated value. Returns an aggregated XML document.

IFXOR

Returns concatenation of XML fragments. Parses and generates an XML instance. Generates an XML processing instruction.

Returns query results as XML. Returns Varray of top level nodes. Returns query of XML results as relational data. Applies XSLT to XML instance. REAL 63 binary digits. FLOAT binary digits.

Floating point numbers using native machine arithmetic. Date, Interval, Time,and Timezone Data Types. Date-Time to 1 nanosecond. TIMESTAMP WITH LOCAL TIMEZONE. Interval between dates in year and month. INTERVAL YEAR TO MONTH. Interval between dates in day and second. INTERVAL DAY TO SECOND. RAW or LONG RAW. RAW OR LONG RAW. Any named SQL type or transient type. An instance of a given type, with data, plus a description of the type. Values of the data instances can be of SQL built-in types as well as user-defined types.

Store URLs to external web pages or to files. Expose documents in the XML heirarchy. Supports the storage and management of audio data.

Supports storage and management of any type of media data, including audio, image and video data. Supports the storage and management of image data. C ompact representation of the color, texture, and shape information of image data. Supports the storage and management of video data. R epresents a feature that characterizes an image by its average color. C haracterizes an image by the relative frequencies of the colors exhibited by samples of the raw image. List containing up to four of the image feature.

Most significant colors of a rectangle. I nherent image characteristics such as height, width, and format. SQL Server has a totally different internal structure than Oracle has. In SQL Server, a table is basically a big linked-list and the data blocks are essentially the leaf-blocks of the cluster index. Those blocks are then doubly-linked back and forth so you can traverse the table in a full table scan or in an index range scan. In fact, an index range scan of the whole table is essentially physically the same as a full table scan.

I'm not sure exactly why, but SQL Server has always had trouble with corruption of these link-list pointers. Run DBCC to check and fix problems with these pointers. Reads do not block writes, writes do not block reads.

Triggers can not commit or rollback independently.