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

SQL query to get group by and distinct values at the same time

I'm having trouble trying to define the SQL query for this table: There's a table of patients and their weight readings recorded on visits with the following columns: patient ID weight reading visit ID (one per visit) In other words, if in two…
wsb3383
  • 3,841
  • 12
  • 44
  • 59
4
votes
3 answers

IF-ELSE statement: create a column depending on another one

In the table JAN07, I have a column TEMPO_INTERCORSO (number(10,0)) and I want to create another column ANTE_POST (number(1)) that is '0' when TEMPO_INTERCORSO > 0 and '1' otherwise. IF (TEMPO_INTERCORSO > 0) THEN UPDATE JAN07 SET ANTE_POST =…
Andrea Ianni
  • 829
  • 12
  • 24
4
votes
3 answers

Oracle: Pure PL/SQL data extraction and anonymization using temporary tables, read-only permissions

I am trying to create a PL/SQL script that extracts a root "object" together with all children and other relevant information from an oracle production database. The purpose is to create a set of test-data to recreate issues that are encountered in…
cornergraf
  • 562
  • 8
  • 22
4
votes
2 answers

How Can We Delete A Table Row Automatically After a Specific Time in PL SQL

I have Tow table with Identical Columns Given Below: Table-1 PAYROLLFILE: Table-2 TEMP_PAYROLLFILE: I have written a PL SQL Function, which inserts a duplicate row from Table PAYROLLFILE to TEMP_PAYROLLFILE and remove the original row from Table…
user5005768Himadree
  • 1,375
  • 3
  • 23
  • 61
4
votes
1 answer

How to catch all exceptions in PL/SQL script?

Currently I'm writing an update script with error handling. A part of this script contains a call to a table that might not exists. So I'd like to catch the exception. However, when running the script in SQL Developer, it does not seem to catch the…
Herman Cordes
  • 4,628
  • 9
  • 51
  • 87
4
votes
2 answers

ORA-01403 - no data found -- even though cursor SELECT statement is running fine

declare CURSOR C1 IS Select to_date(DateRange,'dd-mm-rrrr') DateRange from (select to_date('01-JAN-2016','DD-MON-RRRR') - 1 + level as DateRange from DUAL where (TO_DATE('01-JAN-2016', 'DD-MON-RRRR') - 1 + level) <=…
Oracle_UNIX_Aspirant
  • 41
  • 1
  • 8
4
votes
1 answer

ORA-04084: cannot change NEW values for this trigger type

I'm trying to turn pl/sql trigger that calculates the total of some cells in the table when the tale is changed. This is the code: ALTER session SET nls_date_format='dd/mm/yyyy'; CREATE OR REPLACE TRIGGER TOTAL AFTER UPDATE OR INSERT ON…
RD7
  • 628
  • 4
  • 9
  • 20
4
votes
1 answer

PLS-00103: Encountered the symbol "DECLARE"

i am trying to execute or run the following pl/sql script: SET serveroutput on; CREATE OR REPLACE PROCEDURE findAvg (p_category IN products.category_id% TYPE, c OUT NUMBER) AS BEGIN SELECT NVL ((SELECT AVG(LIST_PRICE) FROM products WHERE p_category…
RD7
  • 628
  • 4
  • 9
  • 20
4
votes
1 answer

oracle procedure with case It depends from parameter

Hi I want to make a procedure like following: CREATE OR REPLACE PROCEDURE SOL.INSERT_LD_NEXTPROCESS (vgroupid NUMBER) IS VPERIODID VARCHAR2 (10); vPROCSESSID NUMBER; CURSOR c IS SELECT COMPANYID, GROUPID, PERIODID, …
Giorgos
  • 637
  • 3
  • 13
  • 25
4
votes
1 answer

Caching array length before a loop in PL-SQL

I was wondering if in PL-SQL caching the length of an array used in a FOR LOOP will have a significant impact for the procedures performance? So will this example: ln_count := lna_avcs.COUNT; FOR i in 1..ln_count LOOP --do something END LOOP; be…
Andrei Maieras
  • 696
  • 5
  • 15
  • 34
4
votes
3 answers

Oracle get distict values from array of type number

I have an array type defined below - TYPE INPUT_ARRAY_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; I have defined a variable of the above type as - temp INPUT_ARRAY_NUM; Populated them with below values - temp(0) := 1; temp(1) := 1; temp(2) :=…
Alex David
  • 585
  • 1
  • 11
  • 32
4
votes
5 answers

PL/SQL: Creating objects

I have to create a PL/SQL package and have been delivered a sample package but trying to follow it I couldn't create an object CREATE OR REPLACE TYPE OBJ_PERSONA_SIGNANT AS OBJECT ( db_id NUMBER(6), usuaricreacio …
Johns
  • 81
  • 5
4
votes
4 answers

Variable/Literal replacement for PL/SQL Cursors?

I often have to debug cursors in Oracle PL/SQL. My problem is that I end up with a few hundered lines big cursors with like 50+ variables and constants. I'm searching for a way to get a version of the statement where constants and variables are…
gobnepla
  • 644
  • 1
  • 5
  • 16
4
votes
3 answers

PL\SQL XMLTable performance

I have a function that parses a XMLTYPE variable, and for each message, append a CLOB with a specific structure for each tag in the XMLTYPE variable. Like this: FUNCTION myFunc (px_Header IN VARCHAR2, …
milheiros
  • 621
  • 2
  • 14
  • 34
4
votes
1 answer

SQL Developer Oracle, how to call procedure?

I have delcared function like this: CREATE or replace PROCEDURE proc ( P_ID IN INTEGER, NAME OUT CHAR, SURNAME OUT CHAR, TOTAL OUT CHAR ) AS BEGIN SELECT NAME, SURNAME, sum(TOTAL) AS TOT INTO NAME,SURNAME,TOTAL …
dreamPr
  • 321
  • 1
  • 2
  • 14
1 2 3
…
99
100