Download Oracle Instant Client: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html Unzip the file after download, then add the instant client folder to the system path. To use the instant client to connect to Oracle database, you need to create a tnsnames.ora ... Read more
Category Archives: Oracle
Some ETL (Extract-Transform-Load) tools for Oracle
Withdata Software provide some ETL (Extract-Transform-Load) tools for Oracle: FileToDB Load TXT, CSV, TSV, XML, JSON, Excel, SQL, RDF, INI data to Oracle DBToFile Export Oracle data to TXT, CSV, TSV, XML, JSON, Excel, SQL files DBCopier Copy data between Oracle and other rational ... Read more
How to get index column names of a table from Oracle
SELECT i.index_name,c.column_name FROM user_ind_columns c, user_indexes i where c.index_name=i.index_name and c.table_name=i.table_name and i.TABLE_NAME='my_table_name' and i.TABLE_OWNER='my_schema_name' See also: How to get index column names of a table from DB2 How to get index column names of ... Read more
How to get primary key of a table from Oracle
SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.table_name = cols.table_name AND cons.owner = cols.owner AND cons.table_name='my_table_name' AND cons.owner='my_schema_name' See ... Read more
About Oracle error “OUI-10137”
When install (reinstall) Oracle 12c on windows server 2008 r2 64bit, if you meet this error “oui-10137”, you should remove folder “c:\program files\oracle”, then try again. ... Read more
How to execute sql file via command line for Oracle
Use sql*plus run SQL file sqlplus username/password@databasename @/path/to/test.sql If you want to see whats going on like this, including any error messages, you need connect to SQL*Plus with sqlplus username/password@databasename Then run the script from the SQL*Plus prompt: set echo ... Read more
How to limit the number of rows returned by an Oracle query after ordering
In MySQL, you can use “Limit n,m”, like this: select * from sometable order by name limit 20,10 How to do in Oracle? select * from ( select a.*, ROWNUM rnum from ( <your_query_goes_here, with order by> ) a where ROWNUM <= :MAX_ROW_TO_FETCH ) where rnum >= ... Read more
How to generate a CREATE TABLE statement for a given table in Oracle
select dbms_metadata.get_ddl('TABLE', 'the_table_name', 'the_schema_name') from dual Replace “the_table_name” and “the_schema_name” with yours. See also: How to generate a CREATE TABLE statement for a given table in SQL Server How to generate a CREATE TABLE statement for a ... Read more
Remove duplicate rows in Oracle
How to delete all duplicate rows and leave only one of them, in Oracle? DELETE FROM my_table WHERE rowid not in (SELECT MIN(rowid) FROM my_table GROUP BY column1, column2, column3...) ; Where column1, column2, etc. is the key you want to use. Relative links: Remove duplicate rows in ... Read more
Replace (Update/Insert) a row into Oracle table – Merge into
In Mysql, if you want to either updates or inserts a row in a table, depending if the table already has a row that matches the data, you can use “ON DUPLICATE KEY UPDATE”. How to do it in Oracle? Use “merge into” . MERGE INTO employees USING dual ON ( "id"=123456 ... Read more