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
32
votes
2 answers

Default Values to Stored Procedure in Oracle

I have a stored procedure as follows. CREATE OR REPLACE PROCEDURE TEST( X IN VARCHAR2 DEFAULT 'P', Y IN NUMBER DEFAULT 1) AS BEGIN DBMS_OUTPUT.PUT_LINE('X'|| X||'--'||'Y'||Y); END; When I execute the above procedure EXEC…
user1118468
  • 713
  • 4
  • 10
  • 19
32
votes
9 answers

How to split array list into equal parts?

Is there anyway to split ArrayList into different parts without knowing size of it until runtime? I know there is a method called: list.subList(a,b); but we need to explicitly mention staring and ending range of list. My problem is, we get a…
Pradeep Simha
  • 17,683
  • 18
  • 56
  • 107
32
votes
2 answers

What are some online websites to compile and run PL/SQL?

I want to execute programs of PL/SQL on-line for practice. Can any one suggest me any websites which will compile and run the code and show the result? Thank you.
Learner
  • 1,544
  • 8
  • 29
  • 55
31
votes
5 answers

Is it possible to CONTINUE a loop from an exception?

I have a fetch being executed inside of a loop. If this fetch fails (no data) I would like to CONTINUE the loop to the next record from within the EXCEPTION. Is this possible? I'm getting a ORA-06550 & PLS-00201 identifer CONTINUE must be…
ProfessionalAmateur
  • 4,447
  • 9
  • 46
  • 63
31
votes
6 answers

When should database synonyms be used?

I've got the syntax down but I'm wondering if somebody can provide an illustrative use case where database synonyms are very useful.
aw crud
  • 8,791
  • 19
  • 71
  • 115
31
votes
6 answers

Is there a way to get the line number where an exception was thrown?

Im working on a pl-sql script, in which I have about 10 TO_CHAR conversions. One of them is throwing an ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception. Currently, im logging the message with this piece of…
Tom
  • 43,810
  • 29
  • 138
  • 169
31
votes
3 answers

Oracle "ORA-01008: not all variables bound" Error w/ Parameters

This is the first time I've dealt with Oracle, and I'm having a hard time understanding why I'm receiving this error. I'm using Oracle's ODT.NET w/ C# with the following code in a query's where clause: WHERE table.Variable1 = :VarA AND (:VarB IS…
John
  • 17,163
  • 16
  • 65
  • 83
31
votes
8 answers

How do I print output in new line in PL/SQL?

How do I print a new line in PL/SQL? I'm after something similar to '\n' in the C language. Example: begin dbms_output.put_line('Hi, good morning friends'); end; I need the output is like this: hi, good morning friends
user1252398
  • 1,069
  • 7
  • 22
  • 29
30
votes
11 answers

Formatting Clear and readable SQL queries

I'm writing some SQL queries with several subqueries and lots of joins everywhere, both inside the subquery and the resulting table from the subquery. We're not using views so that's out of the question. After writing it I'm looking at it and…
MxLDevs
  • 19,048
  • 36
  • 123
  • 194
30
votes
5 answers

PLSQL Insert into with subquery and returning clause

I can't figure out the correct syntax for the following pseudo-sql: INSERT INTO some_table (column1, column2) SELECT col1_value, col2_value FROM other_table WHERE ... RETURNING id …
Stephan Schielke
  • 2,744
  • 7
  • 34
  • 40
30
votes
10 answers

get string from right hand side

I am writing a query in Oracle. I want to get a string from the right hand side but the string length is dynamic. Ex: 299123456789 I want to get 123456789 substr(PHONE_NUMBERS,-X,Y) X is different for each record. I tried…
Mehmet
  • 2,256
  • 9
  • 33
  • 47
30
votes
4 answers

ORA-00060: deadlock detected while waiting for resource

I have a series of scripts running in parallel as a nohup on an AIX server hosting oracle 10g. These scripts are written by somebody else and are meant to be executed concurrently. All the scripts are performing updates on a table. I am getting the…
wowrt
  • 737
  • 2
  • 8
  • 9
30
votes
3 answers

Oracle Error PLS-00323: subprogram or cursor is declared in a package specification and must be defined in the package body

Can someone help me put my pl/sql procedure in a package? I've tried and I'm struggling with it: This is what I have, for my package specification: CREATE OR REPLACE PACKAGE film_pkg IS title VARCHAR2(100); PROCEDURE get_films(fname VARCHAR2); END…
SqlNoob
  • 447
  • 2
  • 5
  • 7
30
votes
4 answers

PL/SQL Package invalidated

I have a script that makes use of a package (PKG_MY_PACKAGE). I will change some of the fields in a query in that package and then recompile it (I don't change or compile any other packages). I run the script and I get an error that looks like …
FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
30
votes
1 answer

What is the point of dbms_lob.createtemporary()?

I don't quite get the point of the dbms_lob.createtemporary() function. How is: DECLARE myclob CLOB; BEGIN myclob := 'foo'; END; any different from: DECLARE myclob CLOB; BEGIN dbms_lob.createtemporary( myclob, TRUE ); myclob :=…
eaolson
  • 14,717
  • 7
  • 43
  • 58
1 2 3
…
99 100