All Questions
7,846
questions
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
144
votes
8
answers
590k
views
Display names of all constraints for a table in Oracle SQL
I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL.
The problem is that to drop a constraint for the column of a particular table I need to ...
- 2,425
93
votes
5
answers
295k
views
How to create a new schema/new user in Oracle Database 11g?
I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle ...
- 1,065
84
votes
3
answers
600k
views
Left Outer Join using + sign in Oracle 11g
Can any one tell me whether below 2 queries are an example of Left Outer Join or Right Outer Join??
Table Part:
Name Null? Type
PART_ID NOT NULL VARCHAR2(4)
SUPPLIER_ID ...
- 7,746
80
votes
6
answers
452k
views
How to find the users list in oracle 11g db?
How to find out the users list, which is all created in the oracle 11g database. Is there any command to find out the users list which we can execute from the Command line interface!
- 809
68
votes
6
answers
410k
views
Add days Oracle SQL
SELECT ORDER_NUM, CUSTOMER_NUM, CUSTOMER_NAME, ADD_DAYS (ORDER_DATE, 20)
FROM CUSTOMER, ORDERS;
Oracle Express says ADD_DAYS invalid? Any ideas what Am I doing wrong?
- 681
66
votes
7
answers
219k
views
How can I select from list of values in Oracle
I am referring to this stackoverflow answer:
How can I select from list of values in SQL Server
How could something similar be done in Oracle?
I've seen the other answers on this page that use UNION ...
- 11.9k
64
votes
5
answers
244k
views
SQL to add column and comment in table in single command
I am using Oracle 11g for my web application. I want to add a column and a comment to an existing table. I can do that easily with the below commands
ALTER TABLE product ADD product_description ...
- 2,954
63
votes
3
answers
81k
views
Using the DISTINCT keyword causes this error: not a SELECTed expression
I have a query that looks something like this:
SELECT DISTINCT share.rooms
FROM Shares share
left join share.rooms.buildingAdditions.buildings.buildingInfoses as bi
... //where clause omitted
ORDER ...
- 835
59
votes
4
answers
540k
views
Calling a stored procedure in Oracle with IN and OUT parameters
I have this procedure:
CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER)
AS BEGIN
SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr;
END;
So when I run it like ...
- 2,313
54
votes
9
answers
469k
views
Convert timestamp to date in Oracle SQL
How can we convert timestamp to date?
The table has a field, start_ts which is of the timestamp format:
'05/13/2016 4:58:11.123456 PM'
I need to query the table and find the maximum and min ...
- 883
49
votes
5
answers
136k
views
How to get rightmost 10 places of a string in oracle
I am trying to fetch an id from an oracle table. It's something like TN0001234567890345. What I want is to sort the values according to the right most 10 positions (e.g. 4567890345). I am using Oracle ...
- 511
48
votes
6
answers
373k
views
How can I get the number of days between 2 dates in Oracle 11g? [duplicate]
I'm trying to find an integer number of days between two dates in Oracle 11g.
I can get close by doing
select sysdate - to_date('2009-10-01', 'yyyy-mm-dd') from dual
but this returns an interval,...
- 7,575
47
votes
5
answers
269k
views
INSERT SELECT statement in Oracle 11G
I'm trying to run a very simple sql statement in Oracle 11g.
insert into table1 (col1, col2) values (select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2);
Very simple query. Cartesian join old ...
- 711
46
votes
12
answers
197k
views
How to increase buffer size in Oracle SQL Developer to view all records?
How to increase buffer size in Oracle SQL Developer to view all records (there seems to be a certain limit set at default)? Any screen shots and/or tips will be very helpful.
- 5,144
46
votes
12
answers
242k
views
Sleep function in ORACLE
I need execute an SQL query in ORACLE it takes a certain amount of time.
So I wrote this function:
CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP
(
TIME_ IN NUMBER
)
RETURN INTEGER IS
BEGIN
...
- 16.3k
45
votes
5
answers
257k
views
Using bind variables with dynamic SELECT INTO clause in PL/SQL
I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL.
For example, I know that this is valid:
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc ...
- 5,229
45
votes
8
answers
85k
views
BETWEEN clause versus <= AND >=
Is there a performance difference between using a BETWEEN clause or using <= AND >= comparisons?
i.e. these two queries:
SELECT *
FROM table
WHERE year BETWEEN '2005' AND '2010';
...and
...
- 4,873
41
votes
9
answers
306k
views
How to get the last row of an Oracle table
I want to get the last row, which I inserted into a table in an Oracle 11g Express database.
How can I do this?
- 2,124
40
votes
7
answers
440k
views
SQL Error: ORA-00942 table or view does not exist
I use SQL developer and i made a connection to my database with the system user, after I created a user and made a another connection with that user with all needed privileges.
But when I try to ...
- 436
40
votes
1
answer
210k
views
PLS-00428: an INTO clause is expected in this SELECT statement
I am wanting to store a Rownum as a variable rather than use a costly Join.
I need to get this from a Select statement as the Rownum will be different on various environments so it cannot be a ...
- 3,918
39
votes
1
answer
167k
views
copy from one database to another using oracle sql developer - connection failed
I am trying to copy a table from one database to another using oracle sql developer. I have the username, password and SIDs.
copy from uname1/password1@SID1 to uname2/pwd2@SID2 insert table1 (*) ...
- 1,818
38
votes
4
answers
385k
views
Oracle SQL query for Date format
I always get confused with date format in ORACLE SQL query and spend minutes together to google, Can someone explain me the simplest way to tackle when we have different format of date in database ...
- 490
37
votes
6
answers
66k
views
Dropping all user tables/sequences in Oracle
As part of our build process and evolving database, I'm trying to create a script which will remove all of the tables and sequences for a user. I don't want to do recreate the user as this will ...
- 373
36
votes
3
answers
181k
views
How to echo text during SQL script execution in SQLPLUS
I have a batch file which runs a SQL script in sqlplus and sends the output to a log file:
sqlplus user/pw < RowCount.sql > RowCount.log
My log file contains this:
Connected to:
Oracle ...
- 1,976
36
votes
5
answers
199k
views
Oracle - How to create a materialized view with FAST REFRESH and JOINS
So I'm pretty sure Oracle supports this, so I have no idea what I'm doing wrong. This code works:
CREATE MATERIALIZED VIEW MV_Test
NOLOGGING
CACHE
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
...
- 90.2k
34
votes
11
answers
59k
views
Oracle Joins - Comparison between conventional syntax VS ANSI Syntax
Preamble
As of late, I see too many geeks commenting on Oracle questions saying "Do not use (+) operator, rather use JOIN syntax".
Question
I do see that both work well. But what is the real ...
- 11.2k
33
votes
2
answers
47k
views
Insert into from CTE
WITH DTL AS
(SELECT CMPI_CODE,
CMN_CDTY_MTRL,
CMI_WT_FACTOR,
CMI_CNTRCT_RATE,
'PL',
PRESENT_PRICE,
TRM_CODE,
ROUND(((NVL(PRESENT_PRICE,...
- 5,848
33
votes
2
answers
125k
views
What are some online compilers to compile and run PL/SQL? [closed]
I want to execute programs of PL/SQL on-line for practice.
Can any one suggest me any websites which will compile and run the code and show the result?
- 1,604
31
votes
2
answers
9k
views
Curious issue with Oracle UNION and ORDER BY
The following query is perfectly valid in pretty much every database (give or take a dual dummy table), including Oracle:
select 'A' as x from dual union all
select 'B' from dual
order by x asc
...
- 216k
30
votes
3
answers
131k
views
How to add a sequence column to an existing table with records
I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (...
- 381
29
votes
5
answers
251k
views
How to display Oracle schema size with SQL query?
I have a Oracle schema with 70+ tables. I want to create simple page which can display the HDD space occupied by the tables. How I can get this value with SQL query?
P.S
And how I can get the Oracle ...
- 1,290
27
votes
5
answers
177k
views
Subtracting Dates in Oracle - Number or Interval Datatype?
I have a question about some of the internal workings for the Oracle DATE and INTERVAL datatypes. According to the Oracle 11.2 SQL Reference, when you subtract 2 DATE datatypes, the result will be a ...
- 5,229
27
votes
4
answers
21k
views
Selecting both MIN and MAX From the Table is slower than expected
I have a table MYTABLE with a date column SDATE which is the primary key of the table and has a unique index on it.
When I run this query:
SELECT MIN(SDATE) FROM MYTABLE
it gives answer instantly. ...
- 4,667
26
votes
3
answers
284k
views
How to find schema name in Oracle ? when you are connected in sql session using read only user
I am connected to a oracle database with a read only user and i used service name while Setting up connection in sql developer hence i dont know SID ( schema ).
How can i find out schema name which i ...
- 5,043
25
votes
3
answers
65k
views
How to add a not null constraint on column containing null values
I have a table with a column that contains a few null values. I want to add a NOT NULL constraint on that column without updating the existing nulls to a non-null value. I want to keep the existing ...
- 8,696
25
votes
4
answers
23k
views
Performance of SUBSTR on CLOB
I have a PL/SQL procedure that does a lot of SUBSTRs on a VARCHAR2 parameter. I would like to remove the length limit, so I tried to change it to CLOB.
Works fine, but performance suffers, so I did ...
- 54.9k
24
votes
6
answers
175k
views
Extract number from string with Oracle function
I need to create an Oracle DB function that takes a string as parameter. The string contains letters and numbers. I need to extract all the numbers from this string. For example, if I have a string ...
- 1,461
24
votes
8
answers
115k
views
Oracle PL/SQL string compare issue
I have the following Oracle PL/SQL codes that may be rusty from you guys perspective:
DECLARE
str1 varchar2(4000);
str2 varchar2(4000);
BEGIN
str1:='';
str2:='sdd';
IF(str1<>...
- 1,935
24
votes
2
answers
33k
views
can a unique constraint column have 2 or more null values? (oracle)
Is it possible to have 2 or more null values in unique constraint column?
- 601
23
votes
3
answers
121k
views
Using pivot on multiple columns of an Oracle row
I have the following sample data in an Oracle table (tab1) and I am trying to convert rows to columns. I know how to use Oracle pivot on one column. But is it possible to apply it to multiple columns? ...
- 285
23
votes
3
answers
98k
views
How to select top five or 'N' rows in Oracle 11g [duplicate]
select distinct ani_digit, ani_business_line from cta_tq_matrix_exp limit 5
I want to select top five rows from my resultset. if I used above query, getting syntax error.
21
votes
4
answers
63k
views
Is there a way to give a subquery an alias in Oracle 11g SQL?
Is there a way to give a subquery in Oracle 11g an alias like:
select *
from
(select client_ref_id, request from some_table where message_type = 1) abc,
(select client_ref_id, response from ...
- 8,831
21
votes
5
answers
23k
views
What does the (+) operator mean in a where-clause of PL/SQL?
I have a SQL query something like:-
Select Table1.attr1, Table1.attr2, Table2.attr3, Table2.attr4
From Tab1 Tabel1, Tab2 Tabel2
Where Tabel1.Attr = Tabel2.Attr (+)
So what does the above mean? Is ...
- 9,430
20
votes
3
answers
26k
views
Can you have an INNER JOIN without the ON keyword?
While debugging into some Oracle code, I came across this query:
SELECT TPM_TASK.TASKID FROM TPM_GROUP
INNER JOIN TPM_USERGROUPS ON TPM_GROUP.GROUPID = TPM_USERGROUPS.GROUPID
INNER JOIN TPM_TASK
...
- 90.2k
19
votes
4
answers
166k
views
How to call Oracle MD5 hash function?
I have below code. I am using Oracle 11g.
SELECT DBMS_OBFUSCATION_TOOLKIT.md5 (input => UTL_RAW.cast_to_raw(
FIRST_NAME
||LAST_NAME
)) md5_key ,
FIRST_NAME ,
LAST_NAME
FROM C_NAME_TAB
...
- 6,733
19
votes
1
answer
134k
views
Oracle - ORA-01489: result of string concatenation is too long [duplicate]
Created one view within which one field(of 6) is a concatenation of two fields from the source table. Created a second view that uses listagg to potentially combine the results of the concatenation. ...
- 575
18
votes
5
answers
89k
views
How to use a dynamic parameter in a IN clause of a JPA named query?
my problem is about this kind of query :
select * from SOMETABLE where SOMEFIELD in ('STRING1','STRING2');
the previous code works fine within Sql Developer.
The same static query also works fine ...
- 1,682
18
votes
2
answers
71k
views
Rollback a committed transaction
Is there any way to rollback a committed transaction in oracle 11g
I have made a delete from table in db and committed it, now I want to rollback the committed change. Is there any way to do it?
- 5,001
18
votes
4
answers
28k
views
what does order 1,2 do
Imagine we have following table
create table t
(item number,
bin number,
primary key (bin , item) );
I have used insert into command to insert several values into table t, now i am ...
user466534