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

PL/SQL Using CASE in WHERE clause

Good day Stackoverflow! I have a query that is giving me an error: "Missing Right Parenthesis", at least, so says SQL Developer. My query has a CASE statement within the WHERE clause that takes a parameter, and then executing a condition based on…
P.M.
  • 53
  • 1
  • 1
  • 7
4
votes
2 answers

PLS-00222: no function with name 'INFO_TYPE' exists in this scope

I am trying to return a table of records in 2 situations: using a function using a anonymous block When I am using the function, everything is working just fine but when I am trying to transform it into anonymous block i receive the above…
mikcutu
  • 1,013
  • 2
  • 17
  • 34
4
votes
1 answer

Is there a multithreading in Oracle PL/SQL?

Now I run data transformations sequentially in my PL/SQL ETL scripts. I've noticed that some transformations do not depend on each other. Is there a way to open a thread in PL/SQL and offload some of the processing so job finishes faster? I tried…
user6358648
4
votes
3 answers

PL/SQL Package level exception handling

I have rather simple question: is exception handling possible at the package level? And if yes, how to implement it? My package has procedures and functions in it, and in case of, let's say, a NO_DATA_FOUND exception I want to do the same thing in…
BeRightBack
  • 186
  • 3
  • 15
4
votes
3 answers

Is it possible to pass table name as a parameter in Oracle?

I want to create a stored procedure like this: PROCEDURE P_CUSTOMER_UPDATE ( pADSLTable IN Table, pAccountname IN NVARCHAR2, pStatus IN NUMBER, pNote IN NVARCHAR2, pEmail IN NVARCHAR2, pMobi IN NVARCHAR2, …
Delta76
  • 13,931
  • 30
  • 95
  • 128
4
votes
1 answer

How to call Oracle PL/SQL Function which has IN/OUT argument and returns BLOB through DB Link

I have an Oracle PL/SQL function (let name it GetRemoteBlob), which resides in a package on a remote database, takes one IN/OUT argument and returns BLOB. To this remote database I've a dblink. I'm not allowed to modify anything on the remote…
Ramanagom
  • 329
  • 2
  • 8
4
votes
5 answers

Oracle ceil for decimal numbers

When rounding up to 2 decimal places, the value 4.01132141 would be rounded to 4.02 because it exceeds 4.01. How can you do this in PL/SQL?
K Ratnajyothi
  • 125
  • 3
  • 4
  • 7
4
votes
1 answer

ORA-04091: table xx_xx is mutating, trigger/function may not see it

So i have to create a trigger which will log changes made to a table called 'passengerlist1' into an extra table called 'logs'. The logs table: create table logs ( p_name varchar(255), p_surname varchar(255), f_id number, time_stamp…
ibu
  • 150
  • 3
  • 17
4
votes
2 answers

Oracle convert accented characters in html format

I have a varchar2 field containing some text, including accented characters ( e.g. à,è,...) and other non literal characters (<,!,;...). I need to transform this field in html compliant format, by using one of the following output formats: input …
Aleksej
  • 22,443
  • 5
  • 33
  • 38
4
votes
1 answer

Table variable as in parameter to populate a table in oracle Stored procedure

Mostly I avoid table variables as input parameters for a stored procedure. Because I do not know how to handle them, but in this case I have no other option. I have a requirement where hundreds of records will be passed on to database from Oracle…
Burhan Khalid Butt
  • 275
  • 1
  • 7
  • 20
4
votes
2 answers

Setting the 'Order By' Clause from plsql procedure parameters

What is the best way to dynamically set the 'order by' column name and direction from parameters passed in to a plsql procedure?
haymansfield
  • 5,419
  • 4
  • 34
  • 51
4
votes
3 answers

Display results in output parameter in Toad

I have a stored procedure in Oracle and I'm using an out parameter in it.. I want to know how to display the output in Toad..
devang
  • 4,397
  • 7
  • 21
  • 11
4
votes
2 answers

Dynamic SQL within cursor

My dynamic sql below to alter a table & create columns based on the output of a query is giving error. Query : DECLARE CURSOR c1 is select distinct WP_NO from temp; cnum VARCHAR2(255); BEGIN FOR cnum in c1 LOOP EXECUTE IMMEDIATE…
Sam
  • 41
  • 2
4
votes
4 answers

Generate the dates in between Start and End Dates

I need to generate all dates in between two given dates. Here is the problem statement: Input: START_DATE END_DATE ---------- ---------- 01-FEB-16 03-FEB-16 01-FEB-16 04-FEB-16 01-FEB-16 05-FEB-16 01-FEB-16 03-FEB-16 11-FEB-16 …
Abhishek
  • 650
  • 1
  • 8
  • 31
4
votes
1 answer

How can you run the same query multiple times using loop in PL/SQL?

Using this code I'm not able to run an insert query multiple times; it asks for ID and name only one time ( if value in counter is > than 1 ). declare counter number := 0 ; begin counter := &counter ; while counter > 0 loop …
Ahtisham
  • 9,170
  • 4
  • 43
  • 57
1 2 3
…
99 100