All Questions
69,183
questions
1340
votes
16
answers
2.4m
views
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 ...
- 16.3k
1212
votes
26
answers
2.4m
views
Get list of all tables in Oracle?
How do I query an Oracle database to display the names of all tables in it?
- 15.8k
674
votes
35
answers
693k
views
Fetch the rows which have the Max value for a column for each distinct value of another column
Table:
UserId, Value, Date.
I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date.
How do I do this in SQL? (Preferably Oracle....
- 6,775
524
votes
18
answers
1.4m
views
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?
- 7,417
494
votes
34
answers
1.1m
views
How to select the nth row in a SQL database table?
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...
- 20.5k
461
votes
11
answers
641k
views
Can a foreign key be NULL and/or duplicate?
Please clarify two things for me:
Can a Foreign key be NULL?
Can a Foreign key be duplicate?
As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to ...
- 21.8k
455
votes
29
answers
805k
views
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 ...
- 15.7k
433
votes
8
answers
778k
views
What is the difference between Views and Materialized Views in Oracle?
What is the difference between Views and Materialized Views in Oracle?
- 81.1k
418
votes
16
answers
671k
views
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 ...
- 165k
381
votes
9
answers
849k
views
Best way to do multi-row insert in Oracle?
I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle.
INSERT INTO TMP_DIM_EXCH_RT
(EXCH_WH_KEY, ...
- 5,131
377
votes
16
answers
1.1m
views
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 ...
- 4,187
341
votes
11
answers
549k
views
Is there any boolean type in Oracle databases?
Is there any Boolean type in Oracle databases, similar to the BIT datatype in Ms SQL Server?
- 3,411
331
votes
11
answers
536k
views
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......
- 301k
330
votes
8
answers
1.4m
views
Update a table with data from another table
Table 1:
id name desc
-----------------------
1 a abc
2 b def
3 c adf
Table 2:
id name desc
-----------------------
1 x 123
2 y 345
In ...
- 25k
327
votes
13
answers
1.2m
views
How do I list all the columns in a table?
For the various popular database systems, how do you list all the columns in a table?
- 12.2k
316
votes
13
answers
860k
views
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 ...
- 13.1k
309
votes
21
answers
588k
views
Select count(*) from multiple tables
How can I select count(*) from two different tables (call them tab1 and tab2) having as result:
Count_1 Count_2
123 456
I've tried this:
select count(*) Count_1 from schema.tab1 union all ...
- 3,101
307
votes
17
answers
698k
views
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?
- 13.1k
304
votes
19
answers
146k
views
Inner join vs Where
Is there a difference in performance (in oracle) between
Select * from Table1 T1
Inner Join Table2 T2 On T1.ID = T2.ID
And
Select * from Table1 T1, Table2 T2
Where T1.ID = T2.ID
?
- 81.1k
299
votes
7
answers
646k
views
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 ...
- 21.1k
293
votes
9
answers
889k
views
How do I do top 1 in Oracle? [duplicate]
How do I do the following?
select top 1 Fname from MyTbl
In Oracle 11g?
- 61.6k
270
votes
7
answers
382k
views
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?
- 13.5k
244
votes
8
answers
286k
views
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 ...
- 16k
242
votes
10
answers
75k
views
Why does Oracle 9i treat an empty string as NULL?
I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is a valid datum, and ...
- 17.8k
232
votes
12
answers
396k
views
How can I find which tables reference a given table in Oracle SQL Developer?
In Oracle SQL Developer, if I'm viewing the information on a table, I can view the constraints, which let me see the foreign keys (and thus which tables are referenced by this table), and I can view ...
- 27.2k
230
votes
11
answers
737k
views
What's the difference between RANK() and DENSE_RANK() functions in oracle?
What's the difference between RANK() and DENSE_RANK() functions? How to find out nth salary in the following emptbl table?
DEPTNO EMPNAME SAL
------------------------------
10 rrr 10000....
- 7,368
229
votes
9
answers
172k
views
When do I need to use a semicolon vs a slash in Oracle SQL?
We have been having some debate this week at my company as to how we should write our SQL scripts.
Background:
Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order ...
- 4,830
223
votes
13
answers
382k
views
How to add 'ON DELETE CASCADE' in ALTER TABLE statement
I have a foreign key constraint in my table, I want to add ON DELETE CASCADE to it.
I have tried this:
alter table child_table_name
modify constraint fk_name
foreign key (child_column_name)
...
- 12.8k
219
votes
6
answers
525k
views
GROUP BY with MAX(DATE) [duplicate]
I'm trying to list the latest destination (MAX departure time) for each train in a table, for example:
Train Dest Time
1 HK 10:00
1 SH 12:00
1 SZ 14:...
- 2,251
219
votes
26
answers
895k
views
How can I get column names from a table in Oracle?
I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and ...
- 2,806
213
votes
10
answers
478k
views
SQL Query to concatenate column values from multiple rows in Oracle
Would it be possible to construct SQL to concatenate column values from
multiple rows?
The following is an example:
Table A
PID
A
B
C
Table B
PID SEQ Desc
A 1 Have
A 2 a ...
- 5,376
205
votes
18
answers
737k
views
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?
- 17.1k
205
votes
4
answers
375k
views
Oracle find a constraint
I have a constraint called users.SYS_C00381400. How do I find what that constraint is? Is there a way to query all constraints?
- 12.9k
205
votes
8
answers
697k
views
How to retrieve the current value of an oracle sequence without increment it?
Is there an SQL instruction to retrieve the value of a sequence that does not increment it.
Thanks.
EDIT AND CONCLUSION
As stated by Justin Cave It's not useful to try to "save" sequence number so
...
- 2,741
204
votes
6
answers
1.3m
views
Oracle SELECT TOP 10 records [duplicate]
I have an big problem with an SQL Statement in Oracle. I want to select the TOP 10 Records ordered by STORAGE_DB which aren't in a list from an other select statement.
This one works fine for all ...
- 20.5k
204
votes
4
answers
319k
views
Oracle "(+)" Operator
I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them.
The DBMS is Oracle.
I did not understand a statement which read like this:
select ...
from a,b
...
- 5,707
201
votes
24
answers
695k
views
Removing duplicate rows from table in Oracle
I'm testing something in Oracle and populated a table with some sample data, but in the process I accidentally loaded duplicate records, so now I can't create a primary key using some of the columns.
...
- 81.1k
200
votes
6
answers
429k
views
What is the string concatenation operator in Oracle?
What is the string concatenation operator in Oracle SQL?
Are there any "interesting" features I should be careful of?
(This seems obvious, but I couldn't find a previous question asking it).
- 13.6k
197
votes
10
answers
607k
views
ORA-00979 not a group by expression
I am getting ORA-00979 with the following query:
SELECT cr.review_sk, cr.cs_sk, cr.full_name,
tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt",
cs.cs_id, cr.tracking_number
from review cr, cs, fact ...
- 3,615
188
votes
11
answers
478k
views
Escaping ampersand character in SQL string [duplicate]
I am trying to query a certain row by name in my sql database and it has an ampersand. I tried to set an escape character and then escape the ampersand, but for some reason this isn't working and I'm ...
- 21.7k
188
votes
5
answers
380k
views
Difference between BYTE and CHAR in column datatypes
In Oracle, what is the difference between :
CREATE TABLE CLIENT
(
NAME VARCHAR2(11 BYTE),
ID_CLIENT NUMBER
)
and
CREATE TABLE CLIENT
(
NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11)
ID_CLIENT ...
- 47.2k
187
votes
15
answers
780k
views
How do I list all tables in a schema in Oracle SQL?
How do i list all tables in a schema in Oracle SQL?
- 33.3k
179
votes
5
answers
891k
views
Comparing Dates in Oracle SQL
I'm trying to get it to display the number of employees that are hired after June 20, 1994,
Select employee_id, count(*)
From Employee
Where to_char(employee_date_hired, 'DD-MON-YY') > 31-DEC-95;
...
- 1,791
174
votes
11
answers
260k
views
How can I confirm a database is Oracle & what version it is using SQL?
I'm building an installer for an application. The user gets to select a datasource they have configured and nominate what type of database it is. I want to confirm that the database type is indeed ...
- 2,073
171
votes
14
answers
927k
views
How to select only 1 row from oracle sql?
I want to use oracle syntax to select only 1 row from table DUAL. For example, I want to execute this query:
SELECT user
FROM DUAL
...and it'd have, like, 40 records. But I need only one record....
- 1,954
170
votes
11
answers
1.0m
views
How to declare variable and use it in the same Oracle SQL script?
I want to write reusable code and need to declare some variables at the beginning and reuse them in the script, such as:
DEFINE stupidvar = 'stupidvarcontent';
SELECT stupiddata
FROM stupidtable
...
- 3,939
168
votes
12
answers
355k
views
How to execute an .SQL script file using c#
I'm sure this question has been answered already, however I was unable to find an answer using the search tool.
Using c# I'd like to run a .sql file. The sql file contains multiple sql statements, ...
- 3,732
165
votes
17
answers
664k
views
How do I spool to a CSV formatted file using SQLPLUS?
I want to extract some queries to a CSV output format. Unfortunately, I can't use any fancy SQL client or any language to do it. I must use SQLPLUS.
How do I do it?
- 296k
163
votes
4
answers
464k
views
SELECT INTO using Oracle
I'm trying to do a SELECT INTO using Oracle. My query is:
SELECT * INTO new_table FROM old_table;
But I get the following error:
SQL Error: ORA-00905: missing keyword
00905. 00000 - "missing ...
- 69.3k
161
votes
2
answers
608k
views
update columns values with column of another table based on condition [duplicate]
I have two tables...
table1 ( id, item, price ) values:
id | item | price
-------------
10 | book | 20
20 | copy | 30
30 | pen | 10
....table2 ( id, item, price) values:
id | item | price
---...
- 3,043