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

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero prompt 'Type # between 100 and…
meligira
  • 631
  • 1
  • 6
  • 8
49
votes
10 answers

How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?

I need to debug in pl/sql to figure times of procedures, I want to use: SELECT systimestamp FROM dual INTO time_db; DBMS_OUTPUT.PUT_LINE('time before procedure ' || time_db); but I don't understand where the output goes to and how can I redirect it…
aye
49
votes
7 answers

Get resultset from oracle stored procedure

I'm working on converting a stored procedure from SQL server to Oracle. This stored procedure provides a direct resultset. I mean that if you call the stored procedure in eg Management Studio you directly obtain the resultset. By converting to…
jwdehaan
  • 1,445
  • 3
  • 13
  • 25
47
votes
6 answers

Get counts of all tables in a schema

I am trying to get the record counts of all tables in a schema. I am having trouble writing the PL/SQL. Here is what I have done so far, but I am getting errors. Please suggest any changes: DECLARE v_owner varchar2(40); v_table_name…
ozzboy
  • 2,672
  • 8
  • 42
  • 69
47
votes
5 answers

How to return multiple rows from the stored procedure? (Oracle PL/SQL)

I want to create a stored procedure with one argument which will return different sets of records depending on the argument. What is the way to do this? Can I call it from plain SQL?
Aleksey
45
votes
5 answers

PL/MySQL does it exist?

In Oracle there is PL/SQL, a powerful imperative language. Is there anything similar for MySQL?
user1081596
  • 917
  • 1
  • 9
  • 16
45
votes
5 answers

Using bind variables with dynamic SELECT INTO clause in PL/SQL

I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL. For example, I know that this is valid: CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) RETURN NUMBER IS …
BYS2
  • 5,199
  • 5
  • 24
  • 32
45
votes
2 answers

Passing an array of data as an input parameter to an Oracle procedure

I'm trying to pass an array of (varchar) data into an Oracle procedure. The Oracle procedure would be either called from SQL*Plus or from another PL/SQL procedure like so: BEGIN pr_perform_task('1','2','3','4'); END; pr_perform_task will read each…
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
44
votes
12 answers

Sleep function in ORACLE

I need execute an SQL query in ORACLE it takes a certain amount of time. So I wrote this function: CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP ( TIME_ IN NUMBER ) RETURN INTEGER IS BEGIN DBMS_LOCK.sleep(seconds => TIME_); RETURN 1; …
Salvador
  • 16,132
  • 33
  • 143
  • 245
43
votes
4 answers

How to catch a unique constraint error in a PL/SQL block?

Say I have an Oracle PL/SQL block that inserts a record into a table and need to recover from a unique constraint error, like this: begin insert into some_table ('some', 'values'); exception when ... update some_table set value =…
Thiago Arrais
  • 33,360
  • 7
  • 30
  • 34
43
votes
2 answers

PL/SQL exception handling: do nothing (ignore exception)

This is a question I am asked very frequently. Since I couldn't find any exact duplicate on stackoverflow, I thought I'd post it as a reference. Question: In PL/SQL, I know how to catch exceptions and execute code when they are caught, and how to…
silentsurfer
  • 1,998
  • 2
  • 17
  • 29
43
votes
8 answers

Check if a variable is null in plsql

I want to check if a variable is null. If it is null, then I want to set a value to that variable: //data type of var is number if Var = null then var :=5; endif But I am geting error in it. How can I check if a variable is null? I am using…
user223541
  • 1,265
  • 7
  • 19
  • 31
43
votes
2 answers

IF EXISTS condition not working with PLSQL

I am trying to print the TEXT when condition is TRUE. The select code is perfectly working fine. It's showing 403 value when i only run select code. But I have to print some text when condition exists. What's the problem with following…
nirmalgyanwali
  • 624
  • 1
  • 6
  • 14
43
votes
10 answers

Extracting the total number of seconds from an interval data-type

When subtracting timestamps the return value is an interval data-type. Is there an elegant way to convert this value into the total number of (milli/micro) seconds in the interval, i.e. an integer. The following would work, but it's not very…
Ben
  • 51,770
  • 36
  • 127
  • 149
42
votes
2 answers

Oracle SQL: Use sequence in insert with Select Statement

Basically I want to run the following query: INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, avg_msrp, count) SELECT my_seq.nextval, '2010', '12', 'ALL', 'ALL', region, …
Muhd
  • 24,305
  • 22
  • 61
  • 78
1 2 3
…
99 100