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

Ref cursor with Execute immediate

I want to get the results in ref_cursor, but I am not able to do that. Please suggest me how to get the results in ref_cursor using Execute immediate CREATE OR REPLACE PROCEDURE TEST_PROC_QT ( p_name IN VARCHAR2, …
Naveen Chakravarthy
  • 819
  • 2
  • 15
  • 30
4
votes
3 answers

Problem with stored procedure in SQL Developer - no "refreshing" the changes made in the block

N.B, the problem I'm facing is not related to the business logic, but rather, with the stored procedured itself. it's a very weird problem I'm facing and I haven't had this kind of problem before. I'm modifying a stored procedure written in…
Marco Aurelio Fernandez Reyes
  • 2,071
  • 2
  • 9
  • 33
4
votes
1 answer

Materialized view fast refresh - insert and delete when updating base table

Hello fellow Stackoverflowers, TLDR: Are MVIEWs using UPDATE or DELETE + INSERT during refresh? some time ago I ran into an obscure thing when I was fiddling whit materialized views in Oracle. Here is my example: 2 base tables MVIEW logs for both…
kovalensue
  • 124
  • 11
4
votes
1 answer

Problem to enable oracle constraints

I'm trying for days to disable the constraints of all my tables, insert data and enable the constraints. It works but I did some tests and when I insert a row that doesnt respect a foreign key, there's no error message when I enable the constraints.…
Marc-André
  • 43
  • 1
  • 4
4
votes
1 answer

Oracle changing timestamp column value for Daylight Savings

I have a timestamp column which has value 12/02/2019 02:00:00. But when the months of daylight savings come the value automatically changes to 12/02/2019 01:00:00.Now if I make the timezone to Europe/Istanbul it becomes the original value. But I…
Hriday Pratim Bharali
  • 107
  • 6
4
votes
1 answer

NULL assigning to associative array

I wanted to assign NULL to an associative array. How can I do it? TYPE t_test IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; l_arr t_test; l_arr:=NULL-- Which is giving error.
TBose
  • 115
  • 2
  • 10
4
votes
1 answer

Get Output parameter from stored procedure without calling execute()

I want to call a PL/SQL stored procedure from within a Java program via an entity manager: StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("someProcedure"); Now my understanding was that I have to call execute()…
Phreneticus
  • 349
  • 2
  • 11
4
votes
3 answers

PL/SQL DB Deployment Script

I'm trying to write a deployment script to run with SQL*Plus in a CI/CD pipeline but I can't find my way around what seems to be a very basic issue. Here's a shortened version of the script release.sql: DECLARE vnum NUMBER; BEGIN SELECT…
HasaniH
  • 8,232
  • 6
  • 41
  • 59
4
votes
2 answers

generate XLS files using PL/SQL

I would like to generate XLS files using PL/SQL. This includes entring text into cells, coloring cells, bordering cells and merging cells. This may include also: different fonts, sizes, styles, alignings and colors of text different line…
Ales Kancilija
  • 894
  • 2
  • 10
  • 20
4
votes
2 answers

pl/sql DELETE is deleting all rows instead of selected rows

I have the trigger: create or replace TRIGGER JACKET_DELETE BEFORE DELETE ON JACKET FOR EACH ROW BEGIN DELETE FROM PORT WHERE EXISTS (SELECT * FROM port LEFT JOIN device on port.fkdevice = device.pkid where port.fkjacket = :old.pkid …
AsherMaximum
  • 942
  • 2
  • 11
  • 17
4
votes
1 answer

Oracle PL/SQL get server's IP v4?

How can I get the IP v4 Of the server by using PL/SQL ? UTL_INADDR.GET_HOST_ADDRESS gives me IPv6, while I need IPv4 what I did I disabled the IPv6 on the sever, still it's bringing me the the IPv6 of the "Tunnel adapter Teredo Tunneling…
Data-Base
  • 8,418
  • 36
  • 74
  • 98
4
votes
1 answer

TO_CHAR of an Oracle PL/SQL TABLE type

For debugging purposes, I'd like to be able to "TO_CHAR" an Oracle PL/SQL in-memory table. Here's a simplified example, of what I'd like to do: DECLARE TYPE T IS TABLE OF MY_TABLE%ROWTYPE INDEX BY PLS_INTEGER; V T; BEGIN -- .. -- Here, I'd…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
4
votes
2 answers

Calculate distance between two lat long points in Oracle

I'm trying to calculate the distance between two lat long points in Oracle using SDO_GEOM Package. Most of the solutions I read on the internet suggests below query: select sdo_geom.sdo_distance( sdo_geometry(2001, 4326, sdo_point_type(40.0, 10.0,…
Para Kan
  • 121
  • 3
  • 12
4
votes
1 answer

APEX: How to implement circular cascade select-lists (many-to-many)

When dealing with many-to-many relationships, how do you implement circular cascade logic for select-lists? As an example, I created a simple test app that tracks books and authors. (This is way simpler than my actual business scenario, and it shows…
Giffyguy
  • 20,378
  • 34
  • 97
  • 168
4
votes
3 answers

Repeating the same bind variable multiple times when using the OPEN...FOR dynamic SQL structure in Oracle PL/SQL

This is a follow on question to Vincent Malgrat's answer to this question. I can't find the correct syntax to use when you need to use the same bind variable multiple times when using OPEN...FOR dynamic SQL. You can see the syntax for EXECUTE…
J Dor
  • 307
  • 4
  • 13
1 2 3
…
99 100