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

How to loop through columns with PL/SQL

I have searched through and found only this problem: Loop through columns SQL it's similar in some ways, but doesn't concern PL/SQL and Oracle Database, therefore I'm Asking new Question. I have a table with ca. 2000 rows and 600 columns. There are…
Jarosław Drabek
  • 359
  • 2
  • 4
  • 11
4
votes
2 answers

How to rename an Oracle XMLTYPE node

I have an XMLType in PL/SQL and I need to rename some of the nodes and some of the values. For example: foo bar I want to turn the above into this: …
wweicker
  • 4,833
  • 5
  • 35
  • 60
4
votes
5 answers

PLSQL : Get sum for each day of week and total sum for week in a single query

Let's say , I have a table, ClientTrade, like thus : ClientName , TradeDate , Quantity And I want to create a query in Oracle PLSQL which should return the result like this : (The days are derived from the TradeDate column and Mon = sum(Quantity)…
Learning
  • 8,029
  • 3
  • 35
  • 46
4
votes
2 answers

PL/SQL send email with attachment?

we have a table with files saved as BLOB I write a code that email these files as an attachment! everything works fine so far, but the files (EXCEL,PDF, ... what ever) are not readable by the programs, only text files and excel will open but after…
Data-Base
  • 8,418
  • 36
  • 74
  • 98
4
votes
3 answers

Dropping a table partition avoiding the error ORA-00054

I need your opinion in this situation. I’ll try to explain the scenario. I have a Windows service that stores data in an Oracle database periodically. The table where this data is being stored is partitioned by date (Interval-Date Range…
user978080
4
votes
3 answers

How to profile end to end performance of Oracle stored procedure

I want to know how long my stored procedure is taking to execute from the time an external process hits the database and says execute this to the time the database returns back to the process and says here ya go. Is there a simple easy way to do…
kralco626
  • 8,456
  • 38
  • 112
  • 169
4
votes
4 answers

Retrieving POST data from external source in a PL SQL Procedure

EDIT ~ I have answered my own question below in the EDIT section, not answering because i feel wrong if i get points for answering my own post =/ I am trying to find a way to pass parameters to this procedure via a URL create or replace…
Kamron K.
  • 594
  • 1
  • 10
  • 18
4
votes
4 answers

Can only one procedure within a PL/SQL package be run with AUTHID CURRENT_USER?

I have a PL/SQL package that does not specify an AUTHID (effectively making it AUTHID DEFINER). However, there is precisely one procedure within this package that needs to run as AUTHID CURRENT_USER. Is this possible, or must I create a separate,…
Adam Paynter
  • 46,244
  • 33
  • 149
  • 164
4
votes
1 answer

Oracle - Audit Trail

Does oracle have Audit Trail as an inbuilt functionality? Do i need to create separate table for Audit Log purpose to capture INSERT, UPDATE and DELETE changes?
Asdfg
  • 11,362
  • 24
  • 98
  • 175
4
votes
3 answers

Alternatives to PL/SQL? Are there any other programming language/IDE combos that offer autocomplete and syntax validation for (Oracle) SQL queries?

I'm looking for an alternative to PL/SQL. PL/SQL is mediocre at best but I haven't found anything that's even close to matching its productivity when writing Oracle database-centric scripts. So, are there any other programming languages that offer…
ivanatpr
  • 1,862
  • 14
  • 18
4
votes
3 answers

Type synonym in oracle

Is it possible to create a "type synonym" in oracle? Something like: CREATE PUBLIC SYNONYM EmailType FOR VARCHAR2(120);
Adilson de Almeida Jr
  • 2,761
  • 21
  • 37
4
votes
1 answer

problem with update query

I have a query UPDATE Table_1 SET Col1='Y' WHERE ROWID IN ( select ROWID from ( SELECT BUS_ID, row_number() over (partition by BUS_ID order by BUS_ID) dupe_count, rowid from Table_1 WHERE…
Niraj Choubey
  • 3,942
  • 18
  • 58
  • 93
4
votes
1 answer

plsql custom numeric format

I'm trying to format a numeric value always in the format "9 999,99" (notice the space). The problem is that oracle does not provide me a a numeric mask for getting this format. The closest I get is with to_char(value,'99990,99'), But no way of…
river0
  • 494
  • 2
  • 6
  • 11
4
votes
2 answers

Raising errors from Java stored procedures without "ORA-29532 Java call terminated by uncaught Java exception"

Suppose you have a Java class which defines a copyFile(String, String) method: public class FileSystem { public static void copyFile(String sourcePath, String destinationPath) throws IOException { // ignore the fact that I'm…
Adam Paynter
  • 46,244
  • 33
  • 149
  • 164
4
votes
1 answer

Is Oracle's syntax diagram for PL/SQL blocks wrong?

I suspect that the syntax diagram for a plsql_block as given in the Oracle® Database PL/SQL Language Reference for Relese 2 is wrong. (For reference, here's the current link to that document) The following piece of PL/SQL compiles fine: declare …
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
1 2 3
…
99 100