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

Can I directly define a trigger in all_triggers table on a table?

I am performing an archival process on a huge database and it involves deleting the production active table and renaming another table to be the new production table. When dropping the production active table, the triggers also get deleted. So I am…
BreadBoard
  • 55
  • 6
4
votes
2 answers

Oracle PL/SQL collect values from a loop into a cursor

I have a PL/SQL TABLE TYPE result set that contains document ids. I can loop over the result set without a problem, but the issue is that I have to return a sys_refcursor from the function, but I am unable to collect the values from the loop into…
ChK
  • 85
  • 1
  • 8
4
votes
1 answer

PL / SQL Function to return varchar2 / numbers

I have this PL / SQL function that accepts the name of a student (f_name). The function then displays all of the information for the given student from a premade table called students. The table contains 5 columns, 2 number type, and 3 varchar2…
rob
  • 43
  • 1
  • 2
  • 7
4
votes
2 answers

ORA-22275: invalid LOB locator specified

I have huge Oracle function which is used to calculate data from 6 tables. create or replace FUNCTION STATISTICS_FUNCTION(NAMEIN IN VARCHAR2 ) RETURN CLOB AS LAST_60_CPU NUMBER; ............. LINE CLOB; CURSOR LAST_60_CPU_CURSOR…
Peter Penzov
  • 1,126
  • 134
  • 430
  • 808
4
votes
3 answers

Why EXECUTE IMMEDIATE is needed here?

I am a SQL Server user and I have a small project to do using Oracle, so I’m trying to understand some of the particularities of Oracle and I reckon that I need some help to better understand the following situation: I want to test if a temporary…
Rafael Merlin
  • 2,517
  • 1
  • 25
  • 31
4
votes
2 answers

Generate sample data in oracle 11g

I have a problem with following procedure, it simply does not choose random street and town. Problem is with streets(SYS.DBMS_RANDOM.VALUE(1,50)) and towns(SYS.DBMS_RANDOM.VALUE(1,50)) looks it random.value is evaluated only once, how can I…
Joe
  • 1,270
  • 2
  • 17
  • 24
4
votes
7 answers

How can I determine the actual database row insertion order?

I have a multithreaded process which inserts several records into a single table. The inserts are performed in a stored procedure, with the sequence being generated INTO a variable, and that variable is later used inside of an INSERT. Given that…
aw crud
  • 8,791
  • 19
  • 71
  • 115
4
votes
1 answer

What exactly are IN, OUT, IN OUT parameters in PL/SQL

I've looked up questions here as well as looking online and watching videos but I'm still confused exactly what IN, OUT is. The reason I'm asking is because I'm writing a procedure that will log an error based on the IN parameters in other…
Jules
  • 215
  • 1
  • 8
  • 13
4
votes
2 answers

How to make dynamic pivot in oracle PL SQL

I have a query below: And I want to make this pivot dynamic in procedures SELECT * FROM ( SELECT tcsd AS Aggregator, country, SUM (COUNT) AS total, COUNT (dest_addr) AS bnum, …
Lelan Calusay
  • 43
  • 4
4
votes
2 answers

Split the string into variables

After executing below select , How to assign it into variables. If input string is "x/y/z" , I have to store "x" into variable say A ,"y" into variable B and z into variable "C". Suppose if string is "x/z" then I have to store "x" into variable say…
user3376818
  • 61
  • 2
  • 8
4
votes
1 answer

Can you dynamically get the page number you are on in oracle apex?

I need to write to a page dynamically based on what page I am on using pl/sql. I don't know if it is possible, but can you get the page number you are on within pl/sql. For example with items you can use :P12_ITEM_NAME in order to get a certain item…
Chris Conaty
  • 93
  • 1
  • 1
  • 10
4
votes
4 answers

how to write loop in pl/sql that goes over numbers

I want to write a loop that iterates over numbers 105 102 19 17 101 16 106 107 for each iteration I want to plug the number in a query and insert it into a table. pseudo: LOOP (105 102 19 17 101 16 106 107) FETCH select * from some_table where…
learn_plsql
  • 1,681
  • 10
  • 28
  • 34
4
votes
3 answers

How do I remove all characters that aren't alphabetic from a string in PL/SQL?

I have a PL/SQL procedure and I need to take a string and remove all characters that aren't alphabetic. I've seen some examples and read documentation about the REGEXP_REPLACE function but can't understand how it functions. This is not a duplicate…
Daniel Paczuski Bak
  • 3,720
  • 8
  • 32
  • 78
4
votes
1 answer

Unable to capture the transfer_timeout error in PL/SQL exception handler

I am setting the timeout for 2 seconds but the code is running for a minute and is then going to when others instead of when UTL_HTTP.transer_timeout. DECLARE request UTL_HTTP.REQ; response UTL_HTTP.RESP; n NUMBER; buff …
StandForTheRight
  • 135
  • 3
  • 14
4
votes
5 answers

'group by' works on MySQL, but not Oracle

I have a query that works on MySQL but doesn't work on Oracle, and I'm trying to convert. This is my table: unique_row_id http_session_id page_name page_hit_timestamp ---------------------------------------------------------------- 0 …
Kevin
  • 11,521
  • 22
  • 81
  • 103
1 2 3
…
99
100