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

A useful example of when to use vsize function instead of length function in Oracle?

It seems vsize() and length() return the same results. Does anyone know of a practical example of when to use vsize instead of length? select vsize(object_name), length(object_name) from user_objects Result: /468ba408_LDAPHelper 20 …
Brian
  • 13,412
  • 10
  • 56
  • 82
4
votes
2 answers

PL/SQL - How to create a conditional cursor?

I need to have a conditional cursor, like: If a row exists (using WHEN EXISTS or something like this), then my cursor is: CURSOR varCursor IS SELECT 1 a FROM DUAL; Else CURSOR varCursor IS SELECT 2 a FROM DUAL; But look, I don't want to change…
Topera
  • 12,223
  • 15
  • 67
  • 104
4
votes
2 answers

How do I alter a trigger in Oracle?

I am trying to change the trigger script for my database. My trigger name is ARCH_USER_UPD_TRG and this puts any updates or deletes on the USER table into a Z_USER table I am dropping a column from the USER table and now need to modify the trigger…
user906153
  • 1,218
  • 8
  • 30
  • 43
4
votes
1 answer

Oracle SQL Union/Merge With Duplicates Issue

Hopefully someone can help me out. So let's say I have temporary table named "TEMP_TABLE" EMP_ID number EMP_FNAME varchar2() EMP_LNAME varchar2() Records in "TEMP_TABLE" 1, Some, Guy 2, Some, Girl 3, Some, Animal Through some SQL magic,…
Marquis Chamberlain
  • 41
  • 5
4
votes
4 answers

PLSql return values

Here I go again with some PLSql.. I want to know, if there's any way I can use the following function like a select without having to turn it into a function or procedure (so I can see the code from the script where it is contained). The code would…
filippo
  • 5,583
  • 13
  • 50
  • 72
4
votes
2 answers

Oracle 10g Connect By Prior - Performance Issues

I have the following SQL statement: SELECT CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" , ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID, LEVEL -1 "LEVEL" FROM ANIMALS START WITH ANIMAL_ID IN( '2360000002558' ) CONNECT BY ((PRIOR…
Paul Brower
  • 41
  • 1
  • 2
4
votes
2 answers

How to update a parameter in a job in Oracle SQL

I have just created a job using SQL Developer build-in wizard and i would like to change the parameters of the created job from my application. The job is launching a stored procedure every day at 7 o'clock. In the application I have two…
Aleksander Lipka
  • 354
  • 1
  • 9
  • 20
4
votes
6 answers

Last word in a sentence: In SQL (regular expressions possible?)

I need this to be done in Oracle SQL (10gR2). But I guess, I would rather put it plainly, any good, efficient algorithm is fine. Given a line (or sentence, containing one or many words, English), how will you find the last word of the sentence? Here…
Guru
  • 2,331
  • 6
  • 31
  • 48
4
votes
2 answers

Are there any Oracle Stored Procedure Accessor Generators for C++?

I've been spending more and more time writing DB Wrappers for Oracle access. This seems to be quite generic procedure, and I was wondering is there already are code generators that generate access routes to Oracle PL/SQL Stored Procedures in…
Leonid
  • 22,360
  • 25
  • 67
  • 91
4
votes
2 answers

Oracle APEX | How to change select list value and Submit it dynamically

I have two select lists 1. The first one P4_country_id_B contains countries names select country_name as d, country_id as r from countries 2. The second one P4_CITY_ID_B contains cities of a country based on selected value in…
Saddam Meshaal
  • 532
  • 3
  • 13
  • 30
4
votes
3 answers

Where can I find an official grammar for the PL/SQL programming language?

Where can I find an official grammar for the PL/SQL programming language? I see that the Antlr project has a user-contributed grammar, but I was hoping to find a more authoritative source.
Adam Paynter
  • 46,244
  • 33
  • 149
  • 164
4
votes
3 answers

How to execute sql query on a table whose name taken from another table

I have a table that store the name of other tables. Like COL_TAB -------------- TABLE_NAME -------------- TAB1 TAB2 TAB3 What i want to do is that, i want to run a sql query on table like this, SELECT * FROM (SELECT TABLE_NAME from COL_TAB WHERE…
Pirate
  • 2,886
  • 4
  • 24
  • 42
4
votes
1 answer

EXECUTE IMMEDIATE PL/SQL Block return type

As part of a test I want to run a PL/SQL block using EXECUTE IMMEDIATE but when I try to fetch the result with INTO it always returns the same error regardless the content of the PL/SQL block I want to run. DECLARE l_output …
Jordan Cortes
  • 271
  • 1
  • 2
  • 16
4
votes
1 answer

How to find unknown repeated substring in string or table column

I have a big string of numbers that is the input of the program (I supposed that the sequence name is 'S') it can be either a string or in a table column. the input string (S) produced by the table like table 1. I want to find unknown sub strings…
user3588552
  • 311
  • 1
  • 3
  • 12
4
votes
1 answer

Network access denied by access control list (ACL) in Oracle Database 11g

Recently we have switched from Oracle 10g to 11g, and only now I noticed that my mailing function does not work, I now get an error: ORA-24247: network access denied by access control list (ACL) So I did a bit of googling and was able to figure out…
K.I
  • 568
  • 1
  • 6
  • 19
1 2 3
…
99 100