Withdata Software

Category: Oracle

  • Use Oracle Instant Client

    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 file, see this: https://docs.oracle.com/en/database/oracle/oracle-database/18/ntcli/specifying-connection-by-configuring-tnsnames.ora-file.html#GUID-D039649B-3A41-4BC8-BC29-EB1F9B0B6792

  • 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 databases MongoToDB Ease your MongoDB to Oracle converting work DBBlobEditor Edit…

  • 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 a table from SQL Server How to get index column names of a table from MySQL How to get index…

  • 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 also: How to get primary key of a table from PostgreSQL How to get primary key of a table from SQLite How to get primary key of…

  • 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.

  • 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 on @/path/to/test.sql

  • 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 >= :MIN_ROW_TO_FETCH like: select * from ( select a.*,…

  • 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 given table in Mysql How to generate a CREATE TABLE statement for a given table in SQLite Some…

  • 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 MySQL Remove duplicate rows in SQL Server…

  • 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 ) WHEN MATCHED THEN…