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
6 answers

How to abstract out subqueries?

I have a query that needs to check that all fields have values are in a list of valid codes. Right now I'm calling the same subquery over and over and over again. I want to abstract the subquery out so that it is faster and the code isn't repeated.…
Michael Holman
  • 901
  • 1
  • 10
  • 26
4
votes
4 answers

Change status automatically in oracle table

I have two table user (user_id, username, status ) status --> (1 - active, 0 - passive) user_work (user_work_id, user_id, status) status --> (1 - active, 0 - passive) How set automatically in user table status = 0 if in user_work table don't…
Pointer
  • 2,123
  • 3
  • 32
  • 59
4
votes
1 answer

PL SQL - convert a numerical value into varchar (percent)

I have a query that uses the outputs(R1,R2) of two sub queries in order to divide them: select a.R1/b.R2*100.0 as Result from (query1) a, (query2) b The division's output is a (decimal) number as well as the R1,R2 outputs. I want to add to the…
veg123
  • 43
  • 1
  • 1
  • 3
4
votes
3 answers

How to perform string interpolation in Oracle PLSQL and SQL. Given a text template with named placeholders, substitute those with variables

Question: How to perform string interpolation is Oracle using PLSQL? Given a template with named parameters enclosed within curly braces then substitute named parameters for variables. Functionality similar to languages like C# string interpolation,…
Rax
  • 665
  • 8
  • 19
4
votes
2 answers

Oracle Apex get cookie from an ajax request

How can I get the value of a cookie in oracle from a request that was originated with ajax from a non-apex page (inside an apex server)? I wanted to start by creating a function that returns the value of the login cookie and use that function to…
nick zoum
  • 7,216
  • 7
  • 36
  • 80
4
votes
2 answers

PLSQL turn clob containing json into table

Is there a way to turn a clob containing JSON Object into table for example I have a clob containing [{"a":"1","b":"1"; "a":"2", "b":"2"; "a":"2","b":"2"}] I want to turn this into a table to join it with other tables in my database. is there a way…
Kyujiin
  • 99
  • 2
  • 10
4
votes
3 answers

Find which PL/SQL Package called a SQL_ID

We have a certain SQL_ID that has performance issues (ex. cd123812jedjdu). This was shown from the AWR report to be called from PKG1. However, PKG1 calls so many packages inside, like below: PKG1.MAIN -> PKG1.PROC1 -> -> PKG2.PROC1 -> ->…
Migs Isip
  • 1,450
  • 3
  • 23
  • 50
4
votes
3 answers

How to expand decimal places of a number to a minimum in Oracle PLSQL?

I cant figure out how to select the following: 123 -> 123.00000 123.12 -> 123.12000 123.123456 -> 123.123456 I would like to expand the number of decimal places to for example 5 decimal places (minimum) If there are no decimal places at…
Stephan Schielke
  • 2,744
  • 7
  • 34
  • 40
4
votes
3 answers

How do I run Oracle plsql procedure from Lisp?

How do I get started?
Lauri Oherd
  • 1,383
  • 1
  • 12
  • 14
4
votes
1 answer

ANYDATA with Collections based on rowtype

I just discovered Oracle's ANYDATA and ANYTYPE and thought it would be useful to generic-ify some very repetitive code I have. I tried a very simple code sample that looks like this: declare --simple test query cursor myCurs is …
FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
4
votes
1 answer

Invoke a REST API (JSON) in PLSQL

I want to invoke a restful API (JSON) in plsql. The JSON Payload is as follows: { "data": { "content": "encrypted content", "signature": "JKQWJK34K32JJEK2JQWJ5678", "dataDescription": { "codeType": "0", "encryptCode": "1", "zipCode":…
Famo
  • 41
  • 1
  • 1
  • 5
4
votes
2 answers

Column names in an empty Oracle REF CURSOR

In PL/SQL, I can use a trick like this one here to find out name/value pairs for every column in a row from a REF CURSOR: TO_CHAR of an Oracle PL/SQL TABLE type That's an awesome trick. But it doesn't work when the REF CURSOR is empty, such as this…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
4
votes
3 answers

Automatically inserting column names for table in TOAD for Oracle

I am looking for a way to automatically insert column names in TOAD for Oracle to make it easier for writing queries. Ideally, I would like to type something like the following: select * from myTable; Then, when I right-click on *, I would have the…
Jeffrey Kevin Pry
  • 3,266
  • 3
  • 35
  • 67
4
votes
1 answer

SQL: Group by "business days"

I would like to group a search result by days, but unfortunately, the definition is not a day from midnight to midnight (00:00-24:00), but from 06:00 to 06:00. Any easy solution? If possible in PL-SQL
rdmueller
  • 10,742
  • 10
  • 69
  • 126
4
votes
1 answer

Mutating tables error with foreign key on delete

In our database we have a table, in which records are referenced by id from about 4 other tables. These 'child' tables have a foreign key to the 'master' table, with 'on delete set null'. All tables have a mutating-tables system (ie: package with…
Tom
  • 6,988
  • 1
  • 26
  • 40
1 2 3
…
99 100