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
27
votes
12 answers

Hidden features of PL/SQL

In light of the "Hidden features of..." series of questions, what little-known features of PL/SQL have become useful to you? Edit: Features specific to PL/SQL are preferred over features of Oracle's SQL syntax. However, because PL/SQL can use most…
Adam Paynter
  • 46,244
  • 33
  • 149
  • 164
26
votes
5 answers

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 NUMBER datatype. On cursory testing, this appears…
BYS2
  • 5,199
  • 5
  • 24
  • 32
26
votes
5 answers

Abort a PL/SQL program

How do I get a PL/SQL program to end halfway through? I haven't been able to find any way to gracefully end the program if an exception occurs - if I handle it, it loops back into the code. Basically what I want to do is force the app not to run in…
Margaret
  • 5,749
  • 20
  • 56
  • 72
26
votes
5 answers

Is there a way to create multiple triggers in one script?

I am trying to create multiple triggers with only uploading one script into an Oracle DB / APEX workspace, and running it once. Here is a brief script compared to the one im trying to use: create or replace trigger "BI_TEC_ROLES" before…
Kamron K.
  • 594
  • 1
  • 10
  • 18
26
votes
3 answers

Execute anonymous pl/sql block and get resultset in java

I would like to execute the anonymous PL/SQL and need to get the resultset object. I got the code which can be done by using cursors inside the PL/SQL block. But the PL/SQL block itself will come from the database as text. So I can't edit that…
RajKP
  • 263
  • 1
  • 3
  • 6
26
votes
4 answers

How do I find out when a stored procedure was last modified or compiled in Oracle?

I'm preferably looking for a SQL query to accomplish this, but other options might be useful too.
ninesided
  • 23,085
  • 14
  • 83
  • 107
26
votes
3 answers

Create an Oracle function that returns a table

I'm trying to create a function in package that returns a table. I hope to call the function once in the package, but be able to re-use its data mulitple times. While I know I create temp tables in Oracle, I was hoping to keep things DRY. So far,…
craig
  • 25,664
  • 27
  • 119
  • 205
26
votes
3 answers

How to remove leading zeroes from day and month values in Oracle, when parsing to string using to_char function?

I want to retrieve a date without the leading zeroes in front of the day and month values in a select statement. If I execute the following query select to_char(sysdate, 'dd.mm.yyyy') from dual; I will get 21.03.2014 as a result. Moreover, if…
Yulian
  • 6,262
  • 10
  • 65
  • 92
26
votes
4 answers

How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?

I have this table: ALLITEMS --------------- ItemId | Areas --------------- 1 | EAST 2 | EAST 3 | SOUTH 4 | WEST The DDL: drop table allitems; Create Table Allitems(ItemId Int,areas Varchar2(20)); Insert Into…
Liao
  • 1,225
  • 3
  • 14
  • 22
26
votes
1 answer

Rudimentary issue: basic PL/SQL console output?

I am using SQL Developer and want to output the contents of a variable to the console using DBMS_OUTPUT.PUT_LINE(). I am running the following code that adds the numbers 1 through 5 inclusive but I'm not seeing any output. SET SERVEROUTPUT…
user1260503
25
votes
9 answers

Can We use threading in PL/SQL?

Is there any feature of asynchronous calling in PL/SQL? Suppose I am in a block of code would like to call a procedure multiple times and wouldn't bother when and what the procedure returns? BEGIN myProc(1,100); myProc(101,200); …
Burhan
  • 687
  • 2
  • 8
  • 13
25
votes
7 answers

Use dbms_output.put_line in Datagrip for .sql files

I started to use Datagrip for my PL/SQL (school) projects that need the use of DBMS_OUTPUT.PUT_LINE. Before this I was using Oracle SQL developer and I was able to use DBMS_OUTPUT by adding the following: SET serveroutput ON; There is a related…
Cezar Cobuz
  • 1,077
  • 1
  • 12
  • 34
25
votes
2 answers

ways to avoid global temp tables in oracle

We just converted our sql server stored procedures to oracle procedures. Sql Server SP's were highly dependent on session tables (INSERT INTO #table1...) these tables got converted as global temporary tables in oracle. We ended up with aroun 500…
Omnipresent
  • 29,434
  • 47
  • 142
  • 186
25
votes
3 answers

oracle plsql: how to parse XML and insert into table

How to load a nested xml file into database table ? Tom
California Los angeles
…
Frank
  • 1,315
  • 7
  • 24
  • 43
25
votes
4 answers

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 some tests (based on these tests from…
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
1 2 3
…
99 100