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
29
votes
3 answers

Solution to "cannot perform a DML operation inside a query"?

I am using a Data Analysis tool and the requirement I have was to accept a value from the user, pass that as a parameter and store it in a table. Pretty straighforward so I sat to write this create or replace procedure complex(datainput in…
Joshua1729
  • 775
  • 4
  • 10
  • 19
29
votes
4 answers

How to get list of all the procedure inside a package oracle

Can I get the name of all the function inside a package. Suppose I have a package PKG_OWA and I want to list all the procedure inside the package.
शेखर
  • 17,412
  • 13
  • 61
  • 117
29
votes
10 answers

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 breaks etc): How can I configure it to show…
Richard Knop
  • 81,041
  • 149
  • 392
  • 552
29
votes
6 answers

UTL_FILE.FOPEN() procedure not accepting path for directory?

I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest! > ERROR at line 1: ORA-29280: invalid > directory path ORA-06512: at > "SYS.UTL_FILE", line 18 ORA-06512: at > "SYS.UTL_FILE", line 424…
Vineet
  • 5,029
  • 10
  • 29
  • 34
29
votes
1 answer

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 from dual), c.blah, d.foo from claim c left join d…
user3808188
  • 557
  • 2
  • 8
  • 20
29
votes
2 answers

percentile_disc vs percentile_cont

What is difference between PERCENTILE_DISC and PERCENTILE_CONT, I have a table ### select * from childstat FIRSTNAME GENDER BIRTHDATE HEIGHT WEIGHT --------------------------------------------------…
Nisar
  • 5,708
  • 17
  • 68
  • 83
29
votes
12 answers

Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?

I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored…
AJ.
  • 16,368
  • 20
  • 95
  • 150
29
votes
2 answers

How can I handle different data types in an Entity-Attribute-Value design (e.g. single table with multiple columns or multiple tables per data type)?

I want to create a patient/sample metadata table using an entity-attribute-value (EAV) approach. Question: How should I handle the varying column type of the value (e.g. string, numeric, or foreign key to dictionary table) based on the…
lebolo
  • 2,120
  • 4
  • 29
  • 44
28
votes
16 answers

What is the difference between explicit and implicit cursors in Oracle?

I am a bit rusty on my cursor lingo in PL/SQL. Anyone know this?
Brian G
  • 53,704
  • 58
  • 125
  • 140
28
votes
4 answers

PL/SQL check if query returns empty

I'm writing a procedure, and i need to check whether my select query returned an empty record or not. (In this example whether there is no x,y shelf) How can i do that? I tried this: temp shelves.loadability%TYPE := NULL; BEGIN select loadability…
WonderCsabo
  • 11,947
  • 13
  • 63
  • 105
28
votes
1 answer

LISTAGG function with two columns

I have one table like this (report) -------------------------------------------------- | user_id | Department | Position | Record_id | -------------------------------------------------- | 1 | Science | Professor | 1001 | | 1 …
Jaanna
  • 1,620
  • 9
  • 26
  • 46
27
votes
4 answers

Escaping single quote in PLSQL

I want PLSQL to generate strings like: COMMENT ON COLUMN TABLE.COLUMN IS 'comment from database'; My solution is: declare str_comment varchar2(4000); begin for rec in (select table_name, column_name, description from description_table) loop …
reforrer
  • 735
  • 7
  • 13
  • 18
27
votes
2 answers

Scope of Oracle package level variables

Given the following Oracle (10g) package definition: create or replace PACKAGE "foo" AS bar VARCHAR2(32000) := NULL; END; what is the scope of bar? Does each session get its own foo.bar, or is foo.bar global across sessions? Can you quote me…
tpdi
  • 34,554
  • 11
  • 80
  • 120
27
votes
4 answers

pl/sql stored procedure: parameter name same as column name

I have a Stored Procedure like this procedure P_IssueUpdate ( Id in integer, ModifiedDate in date, Solution in varchar2 ) AS BEGIN update T_Issue Set ModifiedDate = ModifiedDate, Solution = Solution where id = id; END…
gsharp
  • 27,557
  • 22
  • 88
  • 134
27
votes
11 answers

How to check any missing number from a series of numbers?

I am doing a project creating an admission system for a college; the technologies are Java and Oracle. In one of the tables, pre-generated serial numbers are stored. Later, against those serial numbers, the applicant's form data will be entered. My…
Samcoder
  • 345
  • 1
  • 4
  • 14
1 2 3
…
99 100