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
92
votes
8 answers

How to select into a variable in PL/SQL when the result might be null?

Is there a way in to just run a query once to select into a variable, considering that the query might return nothing, then in that case the variable should be null. Currently, I can't do a select into a variable directly, since if the query returns…
Sapience
  • 1,545
  • 3
  • 14
  • 24
92
votes
9 answers

How can I loop through Exception getCause() to find root cause with detail message

I am trying to call saveOrUpdate() in hibernate to save data. Since columns have unique index, so its throws ConstraintViolationException when I look through via Eclipse debugger. Since root cause could be different for different exception while…
Aman Gupta
  • 5,548
  • 10
  • 52
  • 88
92
votes
6 answers

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 always -1. How do we get the number of records…
dthrasher
  • 40,656
  • 34
  • 113
  • 139
86
votes
5 answers

Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM

Is it possible to create user-defined exceptions and be able to change the SQLERRM? For example: DECLARE ex_custom EXCEPTION; BEGIN RAISE ex_custom; EXCEPTION WHEN ex_custom THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / The…
tgxiii
  • 1,355
  • 1
  • 15
  • 19
85
votes
11 answers

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 : BEGIN SELECT foo, bar FROM foobar; END; doesn't work.
GameFreak
  • 2,881
  • 7
  • 34
  • 38
78
votes
3 answers

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.DATE1 INTO V_DATE1, T1.DATE2 INTO V_DATE2, T1.DATE3…
Hélder Gonçalves
  • 3,822
  • 13
  • 38
  • 63
75
votes
7 answers

What is the difference between function and procedure in PL/SQL?

What is the difference between function and procedure in PL/SQL ?
Isabel Jinson
  • 8,541
  • 16
  • 59
  • 75
75
votes
4 answers

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);
Prabu
  • 4,097
  • 5
  • 45
  • 66
75
votes
11 answers

How do I get column datatype in Oracle with PL-SQL with low privileges?

I have "read only" access to a few tables in an Oracle database. I need to get schema information on some of the columns. I'd like to use something analogous to MS SQL's sp_help. I see the table I'm interested in listed in this query: SELECT * FROM…
James
  • 12,636
  • 12
  • 67
  • 104
74
votes
7 answers

How to execute an oracle stored procedure?

I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT.PUT_LINE('Test'); end it is created…
Rahbee Alvee
  • 1,924
  • 7
  • 26
  • 42
72
votes
6 answers

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 value
Nik Kashi
  • 4,447
  • 3
  • 40
  • 63
67
votes
13 answers

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?
ramachandrareddy
  • 671
  • 1
  • 5
  • 3
67
votes
3 answers

Get a list of all functions and procedures in an Oracle database

I'm comparing three Oracle schemas. I want to get a list of all the functions and procedures used in each database. Is this possible via a query? (preferably including a flag as to whether they compile or not) Ideally it would be great to have a…
AJM
  • 32,054
  • 48
  • 155
  • 243
67
votes
11 answers

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 like to use this function inside a SELECT statement…
Ula Krukar
  • 12,549
  • 20
  • 51
  • 65
66
votes
7 answers

Functions vs procedures in Oracle

What is the main difference between functions and procedures in Oracle? Why must I use procedures if I can do everything with functions? If I cannot call procedure in sql statement, ok, I'll write a function to do the same work. Procedures don't…
0bj3ct
  • 1,400
  • 4
  • 22
  • 51
1
2
3
…
99 100