EzDevInfo.com

Oracle: If Table Exists

I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS construct.

Specifically, whenever I want to drop a table in MySQL, I do something like

DROP TABLE IF EXISTS `table_name`;

This way, if the table doesn't exist, the DROP doesn't produce an error, and the script can continue.

Does Oracle have a similar mechanism? I realize I could use the following query to check if a table exists or not

SELECT * FROM dba_tables where table_name = 'table_name';

but the syntax for tying that together with a DROP is escaping me.

Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.

Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements.

Is there any possible way to combine these two things without writing complicated sub-selects?

I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of

WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'

I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

How to export query result to csv in Oracle SQL Developer?

I'm using Oracle SQL Developer 3.0. Trying to figure out how to export a query result to a text file (preferably CSV). Right clicking on the query results window doesn't give me any export options.

How do I reset a sequence in Oracle?

In PostgreSQL, I can do something like this:

ALTER SEQUENCE serial RESTART WITH 0;

Is there an Oracle equivalent?

Oracle: how to UPSERT (update or insert into a table?)

The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:

if table t has a row exists that has key X:
    update t set mystuff... where mykey=X
else
    insert into t mystuff...

Since Oracle doesn't have a specific UPSERT statement, what's the best way to do this?

How do I find duplicate values in a table in Oracle?

What's the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table?

For example: I have a JOBS table with the column JOB_NUMBER. How can I find out if I have any duplicate JOB_NUMBERs, and how many times they're duplicated?

What is the dual table in Oracle?

I've heard people referring to this table and was not sure what it was about.

Case insensitive searching in Oracle

The default behaviour of LIKE and the other comparison operators, = etc is case-sensitive.

Is it possible make them case-insensitive?

How can I create a copy of an Oracle table without copying the data?

I know the statement:

create table xyz_new as select * from xyz;

Which copies the structure and the data, but what if I just want the structure?

Oracle "Partition By" Keyword

Can someone please explain what the partition by keyword does and give a simple example of it in action, as well as why one would want to use it? I have a SQL query written by someone else and I'm trying to figure out what it does.

An example of partition by:

SELECT empno, deptno, COUNT(*) 
OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp

The examples I've seen online seem a bit too in-depth.

Oracle Differences between NVL and Coalesce

Are there non obvious differences between NVL and Coalesce in Oracle?

The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two parameters and returns the first if it is not null, otherwise it returns the second.

It seems that NVL may just be a 'Base Case" version of coalesce.

Am I missing something?

Update statement with inner join on Oracle

I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

The query is:

UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';

How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?

In MySQL, I can do this:

select * 
from sometable
order by name
limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.

In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this:

select * 
from sometable
where rownum <= 10
order by name

will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.

How to create id with AUTO_INCREMENT on Oracle?

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.

How can I create a column that behaves like auto increment in Oracle 11g?