All Questions

Tagged with
Filter by
Sorted by
Tagged with
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?
Gold's user avatar
  • 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 ...
Jeris's user avatar
  • 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 ...
acoder's user avatar
  • 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 ...
Mike's user avatar
  • 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!
Arun Blr's user avatar
  • 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?
SQL_Student's user avatar
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 ...
rapt's user avatar
  • 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 ...
Sunil Chavan's user avatar
  • 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 ...
Ken's user avatar
  • 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 ...
RegedUser00x's user avatar
  • 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 ...
Dinu's user avatar
  • 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 ...
tismon's user avatar
  • 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,...
Brian Ramsay's user avatar
  • 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 ...
Brian's user avatar
  • 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.
PacificNW_Lover's user avatar
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 ...
Salvador's user avatar
  • 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 ...
BYS2's user avatar
  • 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 ...
wweicker's user avatar
  • 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?
sky scraper's user avatar
  • 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 ...
Yurtsevero's user avatar
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 ...
tomaytotomato's user avatar
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 (*) ...
user2133404's user avatar
  • 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 ...
user2246725's user avatar
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 ...
Ambience's user avatar
  • 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 ...
glenneroo's user avatar
  • 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 ...
Mike Christensen's user avatar
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 ...
Srini V's user avatar
  • 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,...
Nisar's user avatar
  • 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?
Learner's user avatar
  • 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 ...
Lukas Eder's user avatar
  • 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 (...
user1888543's user avatar
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 ...
Peter Penzov's user avatar
  • 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 ...
BYS2's user avatar
  • 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. ...
RGO's user avatar
  • 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 ...
Vipin's user avatar
  • 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 ...
Rachcha's user avatar
  • 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 ...
Peter Lang's user avatar
  • 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 ...
Vali S's user avatar
  • 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<>...
C.c's user avatar
  • 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?
Shubham Anand's user avatar
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? ...
Badal's user avatar
  • 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.
Karthikeyan Sukkoor's user avatar
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 ...
Matt Pascoe's user avatar
  • 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 ...
AppleGrew's user avatar
  • 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 ...
Mike Christensen's user avatar
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 ...
user755806's user avatar
  • 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. ...
user761758's user avatar
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 ...
Marvin's user avatar
  • 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?
Varun's user avatar
  • 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 ...
user avatar

1
2 3 4 5
157