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
42
votes
5 answers

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 the limit is 4000 bytes Can I increase the size…
Ajay Gupta
  • 464
  • 1
  • 5
  • 10
41
votes
2 answers

Subtracting Number of Days from a Date in PL/SQL

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!
user2706266
  • 439
  • 1
  • 4
  • 7
41
votes
4 answers

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?
Bastien Vandamme
  • 17,659
  • 30
  • 118
  • 200
40
votes
17 answers

PLSQL :NEW and :OLD

Can anyone help me understand when to use :NEW and :OLD in PLSQL blocks, I'm finding it very difficult to understand their usage.
Pravin
  • 677
  • 2
  • 7
  • 22
40
votes
3 answers

if (select count(column) from table) > 0 then

I need to check a condition. i.e: if (condition)> 0 then update table else do not update end if Do I need to store the result into a variable using select into? e.g: declare valucount integer begin select count(column) into valuecount from…
user1339913
  • 1,017
  • 3
  • 15
  • 36
39
votes
10 answers

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 column increment automatically? When I try to…
Shaji
  • 741
  • 2
  • 9
  • 23
39
votes
4 answers

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 links on Google related to "%Rowtype". Is the…
Matt M
  • 3,699
  • 5
  • 48
  • 76
39
votes
1 answer

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 literal string in the code. For context, this query is…
tomaytotomato
  • 3,788
  • 16
  • 64
  • 119
39
votes
2 answers

MODIFY COLUMN in oracle - How to check if a column is nullable before setting to nullable?

I'm trying to fill in for a colleague in doing some Oracle work, and ran into a snag. In attempting to write a script to modify a column to nullable, I ran into the lovely ORA-01451 error: ORA-01451: column to be modified to NULL cannot be modified…
Jay S
  • 7,904
  • 2
  • 39
  • 52
38
votes
5 answers

PL/SQL print out ref cursor returned by a stored procedure

How can I fetch from a ref cursor that is returned from a stored procedure (OUT variable) and print the resulting rows to STDOUT in SQL*PLUS? ORACLE stored procedure: PROCEDURE GetGrantListByPI(p_firstname IN VARCHAR2, p_lastname IN…
elpisu
  • 759
  • 3
  • 11
  • 18
38
votes
3 answers

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, field_four, field_five I was testing the indexes I…
onejigtwojig
  • 4,771
  • 9
  • 32
  • 35
37
votes
4 answers

Oracle PL/SQL: how to get the stack trace, package name and procedure name

Sometimes the exception returns something like: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small". It's not so readable since it doesn't report neither the table, the column and the value it tried to write. it would be…
Revious
  • 7,816
  • 31
  • 98
  • 147
36
votes
5 answers

Oracle Pl/SQL: Loop through XMLTYPE nodes

I have a XMLTYPE with the following content: user1 user2 user3 …
pistacchio
  • 56,889
  • 107
  • 278
  • 420
36
votes
6 answers

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 require more permissions than allowed. My script…
Ambience
  • 363
  • 1
  • 3
  • 4
36
votes
10 answers

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; age n --- - 5 1 8 1 12 2 22 1 What…
Mark Harrison
  • 297,451
  • 125
  • 333
  • 465
1 2 3
…
99 100