Stack Overflow
Stack Overflow

Questions tagged [plsql]

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well. Questions with regular DML or DDL statements should be tagged with "sql" and "oracle", NOT with "plsql".

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural language extension for SQL.

Oracle clearly distinguishes between SQL and PL/SQL so PL/SQL should not be confused with regular SQL. Plain DML statements should be tagged with and .

Questions about PL/SQL should probably be tagged as well.

Oracle Documentation: 12c Release 1

New PL/SQL Features in 12c Release 1

This is a series of articles written by Steven Feuerstein and published in Oracle Magazine.

Oracle Documentation: 11g Release 2

Oracle Documentation: Previous releases

Wisdom from the Stack

PL/SQL Tutorial

PL/SQL 101

This is a twelve part series of articles on understanding and using PL/SQL. It is written by Steven Feuerstein and published in Oracle Magazine.

  1. Building with Blocks
  2. Controlling the Flow of Execution
  3. Working with Strings
  4. Working with Numbers in PL/SQL
  5. Working with Dates in PL/SQL
  6. Error Management
  7. Working with Records
  8. Working with Collections
  9. Bulk Processing with BULK COLLECT and FORALL
  10. The Data Dictionary: Make Views Work for You
  11. Wrap Your Code in a Neat Package
  12. Working with Cursors

Book recommendations

28559 questions
444
votes
28 answers

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…
selfawaresoup
  • 15,473
  • 7
  • 36
  • 47
386
votes
6 answers

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?
Goober
  • 13,146
  • 50
  • 126
  • 195
224
votes
15 answers

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?
Microsoft Developer
  • 5,229
  • 22
  • 93
  • 142
200
votes
6 answers

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).
AJ.
  • 13,461
  • 19
  • 51
  • 63
188
votes
6 answers

Number of rows affected by an UPDATE in PL/SQL

I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number…
Thomas Lötzer
  • 24,832
  • 16
  • 69
  • 55
155
votes
5 answers

PL/SQL, how to escape single quote in a string?

In the Oracle PL/SQL, how to escape single quote in a string ? I tried this way, it doesn't work. declare stmt varchar2(2000); begin for i in 1021 .. 6020 loop stmt := 'insert into MY_TBL (Col) values(\'ER0002\')'; …
user595234
  • 6,007
  • 25
  • 77
  • 101
148
votes
6 answers

Oracle PL/SQL - How to create a simple array variable?

I'd like to create an in-memory array variable that can be used in my PL/SQL code. I can't find any collections in Oracle PL/SQL that uses pure memory, they all seem to be associated with tables. I'm looking to do something like this in my PL/SQL…
contactmatt
  • 18,116
  • 40
  • 128
  • 186
142
votes
4 answers

What is the Oracle equivalent of SQL Server's IsNull() function?

In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL?
Goran
  • 1,744
  • 3
  • 12
  • 12
139
votes
17 answers

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 query. But the only thing I know is that a value…
Chris Conway
  • 16,269
  • 23
  • 96
  • 113
137
votes
17 answers

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 INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith…
Phillip Gibson
  • 1,485
  • 2
  • 10
  • 5
134
votes
8 answers

DBMS_OUTPUT.PUT_LINE not printing

When executing the following code, it just says the procedure is completed and doesn't print the infomation i want it to (firstName, lastName) and then the other values from the select query in a table below. CREATE OR REPLACE PROCEDURE…
dexter
  • 1,347
  • 2
  • 9
  • 7
130
votes
14 answers

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 Oracle 10g (preferably) and 11g. The table is as…
marshalllaw
  • 1,301
  • 2
  • 9
  • 3
112
votes
9 answers

Printing the value of a variable in SQL Developer

I wanted to print the value of a particular variable which is inside an anonymous block. I am using Oracle SQL Developer. I tried using dbms_output.put_line. But it is not working. The code which I am using is shown below. SET SERVEROUTPUT…
988875
  • 1,477
  • 3
  • 12
  • 12
109
votes
8 answers

Oracle SQL Query for listing all Schemas in a DB

I wanted to delete some unused schemas on our oracle DB. How can I query for all schema names ?
vicsz
  • 9,552
  • 16
  • 69
  • 101
99
votes
7 answers

How can I drop a "not null" constraint in Oracle when I don't know the name of the constraint?

I have a database which has a NOT NULL constraint on a field, and I want to remove this constraint. The complicating factor is that this constraint has a system-defined name, and that constraint's name differs between the production server,…
Chris Farmer
  • 24,974
  • 34
  • 121
  • 164
1
2 3
…
99 100