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
65
votes
4 answers

BEGIN - END block atomic transactions in PL/SQL

This information should be easy to find, but I haven't had any luck. When I have a BEGIN - END block in a PL/SQL, does it behave as an atomic transaction, that will try to commit on hitting the END block and if anything goes wrong rolls back the…
Jan Kratochvil
  • 2,307
  • 1
  • 21
  • 39
63
votes
8 answers

What does the colon sign ":" do in a SQL query?

What does : stand for in a query? INSERT INTO MyTable (ID) VALUES (:myId) How does it fetch the desired value? Edit: Also what is that sign called? I wanted to search on google, but what's the name for :?
Jla
  • 11,304
  • 14
  • 61
  • 84
62
votes
2 answers

IS vs AS keywords for PL/SQL Oracle Function or Procedure Creation

I have been trying to find out what the difference is between the IS and AS keywords in PL/SQL when creating an Oracle function or procedure. I have searched and have been unable to find any information on this. Does anyone know the difference?
Joel Cunningham
  • 13,620
  • 8
  • 43
  • 49
61
votes
4 answers

Truncating a table in a stored procedure

When I run the following in an Oracle shell it works fine truncate table table_name But when I try to put it in a stored procedure CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table table_name; END test; / it fails with ERROR line 3, col…
Klas Mellbourn
  • 42,571
  • 24
  • 140
  • 158
61
votes
5 answers

PL/SQL block problem: No data found error

SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := &sv_student_id; v_section_id NUMBER := 89; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id…
Orapps
  • 631
  • 2
  • 6
  • 8
60
votes
9 answers

Oracle: how to INSERT if a row doesn't exist

What is the easiest way to INSERT a row if it doesn't exist, in PL/SQL (oracle)? I want something like: IF NOT EXISTS (SELECT * FROM table WHERE name = 'jonny') THEN INSERT INTO table VALUES ("jonny", null); END IF; But it's not working. Note:…
Topera
  • 12,223
  • 15
  • 67
  • 104
60
votes
8 answers

How to query a CLOB column in Oracle

I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value. I tried using DBMS_LOB.substr(column) and i get the error ORA-06502: PL/SQL: numeric or…
Catfish
  • 18,876
  • 54
  • 209
  • 353
60
votes
12 answers

Is there a function to split a string in Oracle PL/SQL?

I need to write a procedure to normalize a record that have multiple tokens concatenated by one char. I need to obtain these tokens splitting the string and insert each one as a new record in a table. Does Oracle have something like a "split"…
Sam
  • 6,437
  • 6
  • 33
  • 41
57
votes
12 answers

Can you SELECT everything, but 1 or 2 fields, without writer's cramp?

Is it possible, in PLSQL, to select all of the fields in a table except for 1 or 2, without having to specify the fields you want? Example, the employee table has the fields: id firstname lastname hobbies Is it still possible to write a query…
Steve
  • 3,127
  • 14
  • 56
  • 96
57
votes
5 answers

Declaring a variable and setting its value from a SELECT query in Oracle

In SQL Server we can use this: DECLARE @variable INT; SELECT @variable= mycolumn from myTable; How can I do the same in Oracle? I'm currently attempting the following: DECLARE COMPID VARCHAR2(20); SELECT companyid INTO COMPID from app where…
Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322
56
votes
8 answers

Proper way of checking if row exists in table in PL/SQL block

I was writing some tasks yesterday and it struck me that I don't really know THE PROPER and ACCEPTED way of checking if row exists in table when I'm using PL/SQL. For examples sake let's use table: PERSON (ID, Name); Obviously I can't do (unless…
devBem
  • 820
  • 4
  • 11
  • 17
55
votes
5 answers

create table with sequence.nextval in oracle

i created a sequence using the following query, create sequence qname_id_seq start with 1 increment by 1 nocache; Now when i try to create a table which uses the above sequence, it is throwing the following error, Error report: SQL Error:…
Murali
  • 811
  • 2
  • 7
  • 12
54
votes
6 answers

Is there any way to flush output from PL/SQL in Oracle?

I have an SQL script that is called from within a shell script and takes a long time to run. It currently contains dbms_output.put_line statements at various points. The output from these print statements appear in the log files, but only once the…
baxter
  • 933
  • 1
  • 7
  • 10
52
votes
8 answers

How can you tell if a value is not numeric in Oracle?

I have the following code that returns an error message if my value is invalid. I would like to give the same error message if the value given is not numeric. IF(option_id = 0021) THEN IF((value<10000) or (value>7200000) or /* Numeric Check…
Kyle Williamson
  • 2,251
  • 6
  • 43
  • 75
51
votes
17 answers

Convert comma separated string to array in PL/SQL

How do I convert a comma separated string to a array? I have the input '1,2,3' , and I need to convert it into an array.
Suvonkar
  • 2,440
  • 12
  • 34
  • 44
1 2
3
…
99 100