https://sqlines.com/ 2026-05-06T06:28:33+00:00 https://sqlines.com/ https://sqlines.com/lib/images/favicon.ico text/html 2012-07-06T14:44:27+00:00 https://sqlines.com/oracle/all_tables?rev=1341585867&do=diff In Oracle, data dictionary views (catalog views) ALL_TABLES, DBA_TABLES and USER_TABLES contain information on tables: * ALL_TABLES - All tables accessible to the current user * DBA_TABLES - All tables in the database (SELECT ANY TABLE privilege or DBA role required) * USER_TABLES - All tables tables owned by the current user text/html 2014-06-23T18:04:49+00:00 https://sqlines.com/oracle/case_sensitivity_primary_key?rev=1403546689&do=diff In Oracle, primary keys are case-sensitive, so if two values differ only in case they can be used as two different primary keys. In SQL Server, by default, primary keys are case-insensitive and when you transfer data from Oracle to SQL Server and then try to create the primary key constraints, you may have duplicate key errors. text/html 2017-04-15T18:45:57+00:00 https://sqlines.com/oracle/character_sets_length_semantics?rev=1492281957&do=diff You can run the following query to see character set settings in your Oracle database: Oracle: -- Read character set and length semantics parameters SELECT parameter, value FROM nls_database_parameters WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_LENGTH_SEMANTICS', 'NLS_NCHAR_CHARACTERSET'); text/html 2012-09-26T13:42:17+00:00 https://sqlines.com/oracle/datatypes?rev=1348666937&do=diff The reference provides detailed technical information on migrating built-in data types from Oracle to Microsoft SQL Server and SQL Azure. Last Update: Oracle 11g Release 2 and Microsoft SQL Server 2012 All Data Types Data types in alphabetical order: text/html 2017-12-21T13:54:08+00:00 https://sqlines.com/oracle/date_timestamp_formats?rev=1513864448&do=diff You can run the following query to determine the default format for DATE and TIMESTAMP data type values in your Oracle database: Oracle: -- Read character set and length semantics parameters SELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE 'NLS_%_FORMAT'; text/html 2012-06-10T19:02:40+00:00 https://sqlines.com/oracle/default?rev=1339354960&do=diff The DEFAULT clause allows you to specify the default value for a column. The default value is assigned if you omit the column or specify DEFAULT keyword in a INSERT statement. DEFAULT Clause Conversion Details The DEFAULT clause in CREATE TABLE and ALTER TABLE statements in Oracle and SQL Server may use a different syntax and require conversion. text/html 2012-08-03T16:01:41+00:00 https://sqlines.com/oracle/function_based_indexes?rev=1344009701&do=diff In Oracle, you can create a function-based index that stores precomputed results of a function or expression applied to the table columns. Function-based indexes are used to increase the performance of queries that use functions in the WHERE clause. text/html 2020-11-18T19:05:11+00:00 https://sqlines.com/oracle/functions?rev=1605726311&do=diff All functions in alphabetical order: 1 ABS(n) 2 ACOS(n) 3 ADD_MONTHS(date, integer) 4 APPROX_COUNT (expr) 5 APPROX_COUNT_DISTINCT(expr) 6 APPROX_COUNT_DISTINCT_AGG(expr) 7 APPROX_COUNT_DISTINCT_DETAIL(expr) 8 APPROX_MEDIAN(expr) 9 APPROX_PERCENTILE(expr) 10 APPROX_PERCENTILE_AGG(expr) 11 APPROX_PERCENTILE_DETAIL(expr) 12 APPROX_RANK(expr) 13 APPROX_SUM(expr) 14 ASCIISTR(char) 15 ASCII(char) 16 ASIN(n) 17 ATAN(n) 18 ATAN2(n1 , n2) 19 AVG(exp… text/html 2012-07-16T20:10:35+00:00 https://sqlines.com/oracle/hex_constants?rev=1342469435&do=diff In Oracle, you can use TO_NUMBER function with x format to represent a hex constant (literal). In SQL Server, you can use the prefix 0x followed by hexadecimal digits. Oracle: -- Represent the hexadecimal constant ABC and use it in an expression SELECT TO_NUMBER('ABC', 'xxx') + 10 FROM dual; text/html 2012-06-04T19:08:16+00:00 https://sqlines.com/oracle/implicit_commit?rev=1338836896&do=diff Oracle issues an implicit COMMIT before and after any data definition language (DDL) statement. This does not happen in SQL Server. Let's create a table in Oracle and insert a row: Oracle: -- Create a table and insert a row CREATE TABLE states ( abbr CHAR(2), name VARCHAR2(90) ); -- Transaction will be in progress after this insert INSERT INTO states VALUES ('CA', 'California'); text/html 2017-11-30T15:10:28+00:00 https://sqlines.com/oracle/import_dump_file?rev=1512054628&do=diff You can use the Oracle IMP utility to import the content of a Oracle .dmp file. Change Schema/User If the dump file was created from one schema, but you want to import tables into another schema IMP allows you to change the schema/user name using FROMUSER and TOUSER options: text/html 2012-08-30T18:33:03+00:00 https://sqlines.com/oracle/insert_empty_string_to_fk_column?rev=1346351583&do=diff In Oracle, if you insert an empty string ('') to a foreign key column, Oracle inserts NULL that means there is no reference to the parent table, and INSERT statement executes successfully. SQL Server does not treat empty string and NULL in the same way, and insert statement fails. text/html 2012-08-30T09:15:35+00:00 https://sqlines.com/oracle/insert_empty_string_to_numeric_column?rev=1346318135&do=diff In Oracle, if you insert an empty string ('') to a NUMBER column, Oracle inserts NULL. In SQL Server, if you insert an empty string ('') to an integer column (INT i.e.), SQL Server inserts 0, if you insert an empty string to a decimal column (DECIMAL i.e.), the statement fails. text/html 2022-10-17T14:37:16+00:00 https://sqlines.com/oracle/load_blob_sqlloader?rev=1666017436&do=diff You can use the Oracle SQL*Loader utility to import the data from files (containing images i.e.) into an Oracle table. For example, let's consider a sample table: CREATE TABLE pictures ( id NUMBER(5), data BLOB ); You are given a set of image files (.png files i.e.) and want to load them into this table. text/html 2017-10-12T11:26:38+00:00 https://sqlines.com/oracle/object_types_collections_to_json?rev=1507807598&do=diff Oracle database has object-oriented programming features that, for example, allow you to store complex object types and even collections of objects (arrays of objects) in a single table column. JSON is a very popular way to get the same functionality in other databases and applications. Let's see how we can use a PL/SQL function to convert object types and collections to JSON string. text/html 2012-07-25T14:06:16+00:00 https://sqlines.com/oracle/ora-00001?rev=1343225176&do=diff Oracle raises ORA-00001 error when an unique constraint is violated by a INSERT or UPDATE statement that attempts to insert a duplicate key. SQL Server raises error 2627 in this case. Last Update: Oracle 11g R2 and Microsoft SQL Server 2012 Unique Constraint Violation in Oracle Assume there is a table with the primary key in Oracle: text/html 2012-05-29T09:52:15+00:00 https://sqlines.com/oracle/outer_joins?rev=1338285135&do=diff Oracle outer join operator (+) allows you to perform outer joins on two or more tables. Quick Example: -- Select all rows from cities table even if there is no matching row in counties table SELECT cities.name, countries.name FROM cities, countries WHERE cities.country_id = countries.id(+); text/html 2012-06-15T19:31:55+00:00 https://sqlines.com/oracle/pragma_autonomous_transaction?rev=1339788715&do=diff In Oracle, an autonomous transaction is an independent transaction started by another transaction (the main transaction). When the autonomous transaction performs a commit or roll back, its committed changes are visible to other transactions immediately, but it does not affect the main transaction that is still in progress. At the same time, the autonomous transaction does not see any uncommitted changes made by the main transaction. text/html 2012-01-11T14:14:34+00:00 https://sqlines.com/oracle/queries_expressions?rev=1326291274&do=diff Articles how to perform popular queries and expressions in Oracle. Last Update: Oracle 11g Release 2 Datetime Queries and Expressions * Get Number of Days Left in Month text/html 2012-07-05T19:03:26+00:00 https://sqlines.com/oracle/trailing_spaces_pk_columns?rev=1341515006&do=diff In Oracle, values in primary key columns of type VARCHAR2 and NVARCHAR2 may differ only in trailing spaces. Oracle treats such values as not equal and allows you to use them as different primary keys. When you transfer such data to SQL Server and then try to create the primary key constraints, SQL Server raises a duplicate key error.