All Questions
9,954
questions
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
392
votes
6
answers
495k
views
What is the difference between SQL, PL-SQL and T-SQL?
What is the difference between SQL, PL-SQL and T-SQL?
Can anyone explain what the differences between these three are, and provide scenarios where each would be relevantly used?
- 13.4k
236
votes
15
answers
428k
views
What does it mean by select 1 from table?
I have seen many queries with something as follows.
Select 1
From table
What does this 1 mean, how will it be executed and, what will it return?
Also, in what type of scenarios, can this be used?
- 5,349
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
145
votes
18
answers
546k
views
Search All Fields In All Tables For A Specific Value (Oracle)
Is it possible to search every field of every table for a particular value in Oracle?
There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to ...
- 16.4k
139
votes
18
answers
1.3m
views
SQL error "ORA-01722: invalid number"
A very easy one for someone,
The following insert is giving me the
ORA-01722: invalid number
why?
INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694');
INSERT ...
- 1,505
131
votes
14
answers
354k
views
Splitting string into multiple rows in Oracle
I know this has been answered to some degree with PHP and MYSQL, but I was wondering if someone could teach me the simplest approach to splitting a string (comma delimited) into multiple rows in ...
- 1,311
92
votes
6
answers
187k
views
How can I get the number of records affected by a stored procedure?
For INSERT, UPDATE and DELETE SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is ...
- 41.3k
86
votes
11
answers
230k
views
Is it possible to output a SELECT statement from a PL/SQL block?
How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT?
For example how to do a SELECT like:
SELECT foo, bar FROM foobar;
Hint :
...
- 2,911
81
votes
3
answers
154k
views
Select multiple columns into multiple variables
How can I do in one select with multiple columns and put each column in a variable?
Something like this:
--code here
V_DATE1 T1.DATE1%TYPE;
V_DATE2 T1.DATE2%TYPE;
V_DATE3 T1.DATE3%TYPE;
SELECT T1....
- 3,902
76
votes
4
answers
185k
views
How to check if a column exists before adding it to an existing table in PL/SQL?
How do I add a simple check before adding a column to a table for an oracle db? I've included the SQL that I'm using to add the column.
ALTER TABLE db.tablename
ADD columnname NVARCHAR2(30);
- 4,147
76
votes
6
answers
266k
views
Different CURRENT_TIMESTAMP and SYSDATE in oracle
After executing this SQL in oracle 10g:
SELECT SYSDATE, CURRENT_TIMESTAMP FROM DUAL
I receive this strange output:
What is cause of the difference in time?
The server time is equal of SYSDATE ...
- 4,526
75
votes
7
answers
241k
views
What is the difference between function and procedure in PL/SQL?
What is the difference between function and procedure in PL/SQL ?
- 8,601
69
votes
14
answers
202k
views
SQL to generate a list of numbers from 1 to 100
Using the DUAL table, how can I get a list of numbers from 1 to 100?
- 691
67
votes
11
answers
317k
views
How to use BOOLEAN type in SELECT statement
I have a PL/SQL function with BOOLEAN in parameter:
function get_something(name in varchar2, ignore_notfound in boolean);
This function is a part of 3rd party tool, I cannot change this.
I would ...
- 12.8k
65
votes
8
answers
132k
views
What does the colon sign ":" do in a SQL query?
What does : stand for in a query?
INSERT INTO MyTable (ID) VALUES (:myId)
How does it fetch the desired value?
Edit: Also what is that sign called? I wanted to search on google, but what's the name ...
- 11.3k
62
votes
8
answers
573k
views
How to query a CLOB column in Oracle
I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value.
I tried using DBMS_LOB.substr(column) and ...
- 19.1k
62
votes
2
answers
87k
views
IS vs AS keywords for PL/SQL Oracle Function or Procedure Creation [duplicate]
I have been trying to find out what the difference is between the IS and AS keywords in PL/SQL when creating an Oracle function or procedure.
I have searched and have been unable to find any ...
- 13.7k
61
votes
5
answers
231k
views
PL/SQL block problem: No data found error
SET SERVEROUTPUT ON
DECLARE
v_student_id NUMBER := &sv_student_id;
v_section_id NUMBER := 89;
v_final_grade NUMBER;
v_letter_grade CHAR(1);
BEGIN
SELECT final_grade
INTO ...
- 631
56
votes
8
answers
155k
views
Proper way of checking if row exists in table in PL/SQL block
I was writing some tasks yesterday and it struck me that I don't really know THE PROPER and ACCEPTED way of checking if row exists in table when I'm using PL/SQL.
For examples sake let's use table:
...
- 820
55
votes
5
answers
266k
views
create table with sequence.nextval in oracle [duplicate]
i created a sequence using the following query,
create sequence qname_id_seq start with 1 increment by 1 nocache;
Now when i try to create a table which uses the above sequence, it is throwing the ...
- 811
55
votes
6
answers
84k
views
Is there any way to flush output from PL/SQL in Oracle?
I have an SQL script that is called from within a shell script and takes a long time to run. It currently contains dbms_output.put_line statements at various points. The output from these print ...
- 943
55
votes
3
answers
670k
views
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end...
undefine numero
set serveroutput ...
- 671
53
votes
8
answers
274k
views
How can you tell if a value is not numeric in Oracle?
I have the following code that returns an error message if my value is invalid. I would like to give the same error message if the value given is not numeric.
IF(option_id = 0021) THEN
IF((...
- 2,213
47
votes
5
answers
230k
views
How to return multiple rows from the stored procedure? (Oracle PL/SQL)
I want to create a stored procedure with one argument which will return different sets of records depending on the argument. What is the way to do this? Can I call it from plain SQL?
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
6
answers
293k
views
What is the max size of VARCHAR2 in PL/SQL and SQL?
I am on Oracle 10g. In a requirement I need to increase the size of a pl/sql VARCHAR2 variable. It is already at 4000 size. I have read that
in PL/SQL, VARCHAR2 can be up to 32767 bytes. For SQL ...
- 494
44
votes
8
answers
162k
views
Check if a variable is null in plsql
I want to check if a variable is null. If it is null, then I want to set a value to that variable:
//data type of var is number
if Var = null then
var :=5;
endif
But I am geting error in it. ...
- 1,265
44
votes
2
answers
190k
views
IF EXISTS condition not working with PLSQL
I am trying to print the TEXT when condition is TRUE. The select code is perfectly working fine. It's showing 403 value when i only run select code. But I have to print some text when condition exists....
- 634
43
votes
2
answers
242k
views
Oracle SQL: Use sequence in insert with Select Statement
Basically I want to run the following query:
INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, avg_msrp, count)
SELECT
my_seq.nextval,
'2010',
...
- 25k
43
votes
10
answers
97k
views
Extracting the total number of seconds from an interval data-type
When subtracting timestamps the return value is an interval data-type. Is there an elegant way to convert this value into the total number of (milli/micro) seconds in the interval, i.e. an integer.
...
- 52.3k
42
votes
2
answers
218k
views
Subtracting Number of Days from a Date in PL/SQL [duplicate]
I would like to subtract a given x number of days from sysdate, can someone assist me on how to do that, I am using the PL/SQL language. THANKS!
- 449
42
votes
4
answers
153k
views
In Oracle, is it possible to INSERT or UPDATE a record through a view?
In Oracle, is it possible to INSERT or UPDATE a record (a row) through a view?
- 18.1k
40
votes
10
answers
204k
views
Oracle - Insert New Row with Auto Incremental ID
I have a workqueue table that has a workid column. The workID column has values that increment automatically. Is there a way I can run a query in the backend to insert a new row and have the workID ...
- 761
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
40
votes
3
answers
66k
views
Creating Indexes for Group By Fields?
Do you need to create an index for fields of group by fields in an Oracle database?
For example:
select *
from some_table
where field_one is not null and field_two = ?
group by field_three, ...
- 4,821
39
votes
3
answers
85k
views
What does "%Type" mean in Oracle sql?
I'm getting my first experience with Oracle and TOAD (I know SSMS). I came across this "%Type" next to an input parameter in an update procedure and I have no idea what it is or what it means. I found ...
- 3,739
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
10
answers
75k
views
Oracle: how to "group by" over a range?
If I have a table like this:
pkey age
---- ---
1 8
2 5
3 12
4 12
5 22
I can "group by" to get a count of each age.
select age,count(*) n from tbl group by age;
...
- 301k
36
votes
6
answers
127k
views
Delete all contents in a schema in Oracle
Is possible to delete all contents in scheme in Oracle? I found this script:
Begin
for c in (select table_name from user_tables) loop
execute immediate ('drop table "'||c.table_name||'&...
- 438
35
votes
5
answers
113k
views
SQL recursive query on self referencing table (Oracle)
Lets assume I have this sample data:
| Name | ID | PARENT_ID |
-----------------------------
| a1 | 1 | null |
| b2 | 2 | null |
| c3 | 3 | null |
| a1.d4 | ...
- 29.7k
34
votes
9
answers
71k
views
Truncate table in Oracle getting errors
I got the problem is when I run following command in Oracle, I encounter the error.
Truncate table mytable;
Errors:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
I ...
- 13.5k
33
votes
8
answers
220k
views
How do I print output in new line in PL/SQL?
How do I print a new line in PL/SQL? I'm after something similar to '\n' in the C language.
Example:
begin
dbms_output.put_line('Hi, good morning friends');
end;
I need the output is like ...
- 1,119
33
votes
2
answers
132k
views
Oracle PLS-00363: expression '' cannot be used as an assignment target
Hello not sure why Im getting this error.
Basically I get it in these three lines:
PLS-00363: expression 'p_temp_foo.editable.modified_by' cannot be used as an assignment target
PLS-00363: expression ...
- 10.1k
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
10
answers
100k
views
Ugly formatting in SQL*Plus
It is really annoying that when I run a select command in SQL*Plus such as:
SELECT * FROM books;
The output is really badly formatted and unreadable (row cells are not in a row but separated by line ...
- 82.4k
31
votes
3
answers
124k
views
Oracle "ORA-01008: not all variables bound" Error w/ Parameters
This is the first time I've dealt with Oracle, and I'm having a hard time understanding why I'm receiving this error.
I'm using Oracle's ODT.NET w/ C# with the following code in a query's where ...
- 17.4k
31
votes
1
answer
27k
views
Using SELECT inside COALESCE
How do I correct the following SQL code, specifically the COALESCE part?
insert into Stmt G (ID,blah,foo)
select
coalesce(SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER, select StmtSeq.nextval ...
- 577
31
votes
6
answers
30k
views
When should database synonyms be used?
I've got the syntax down but I'm wondering if somebody can provide an illustrative use case where database synonyms are very useful.
- 8,841