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

Insert or Update using Oracle and PL/SQL

I have a PL/SQL function that performs an update/insert on an Oracle database that maintains a target total and returns the difference between the existing value and the new value. Here is the code I have so far: FUNCTION calcTargetTotal(accountId…
Shane
  • 41
  • 1
  • 2
4
votes
4 answers

PLSQL: using subquery in if-statement error PLS-00405

I'm getting error pls-00405 when I try to run this code: BEGIN IF :P10_KAART_CODE IN (SELECT KAART_CODE FROM CADEAUKAART) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; There are some similar questions about this but couldn't find…
J. Adam
  • 1,457
  • 3
  • 20
  • 38
4
votes
3 answers

SQL Injection in PL/SQL - myth or fact?

I am trying to hack my PL/SQL code. We create the PL/SQL procedure that opens and fetch the cursor. By our standard we did create a dynamic SQL statement, but we are unable to inject the OR 1=1 condition. I did prepare a…
Gico
  • 1,276
  • 2
  • 15
  • 30
4
votes
2 answers

Fix for Maximum length allowed for char in Oracle 4000 character

I am currently using Oracle 11g. As per my requirement, I need to store first 4000 (max limit) characters to my variable (used in triggers). For that I am using VarName = SUBSTR function(VarName, 1, 4000), but it seems like it is storing nothing (I…
Sumit Suman
  • 71
  • 1
  • 6
4
votes
3 answers

Can I use DECODE in a UPDATE statement?

Can I use DECODE in a UPDATE statement on the left hand side of SET? UPDATE temp SET DECODE(update_var, 1, col1, 2, col2) = update_value; This is giving me error as eual sign ignored..
Avi
  • 1,115
  • 8
  • 20
  • 30
4
votes
1 answer

Passing dynamic input parameters to 'execute Immediate'

I have a table where I am storing certain conditions along with input parameters as shown below: CONDITION | INPUT_PARAMS --------------------------------------------------------- :p_end_date < :p_start_date | …
user10060883
  • 97
  • 1
  • 10
4
votes
4 answers

generating same positive and negative number in Oracle sequence

How can I generate the below numbers through sequence in oracle 2 -2 2 -2 2 -2 And so on. I can generate 1,2,1,2,1,2 through cycle but above I am not able to use logic.
user9892866
4
votes
1 answer

oracle sql: collect aggregation

I want to group my database entries by an attribute and to know which entries are in each group at the same time. I collect the ids of the grouped entries with Oracle COLLECT function COLLECT Function DECLARE TYPE ids_type IS TABLE OF number(19,…
Ewgenij Sokolovski
  • 897
  • 1
  • 12
  • 31
4
votes
1 answer

Response message: java.sql.SQLException: Fail to convert to internal representation: jmeter

I would like to know about how to pass array value in the jMeter for oracle stored procedure. Below details setup already but which is not working.Please help me on this. Oracle PL/SQL: PROCEDURE Get_User( p_input1 IN …
sunleo
  • 10,589
  • 35
  • 116
  • 196
4
votes
9 answers

Oracle ORA-00600

I have SQL SELECT statement that returns: Error: ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], [] If I narrow my results by adding one more condition in WHERE clause everything is ok. Anyone knows what is…
Chobicus
  • 2,024
  • 2
  • 17
  • 26
4
votes
1 answer

why i'm not getting error? how does database understand relevant column in nested subquery?

Here is the scenario: I have two tables department and employee. when i'm selecting a column from a table which doesn't exist in that table, it's throws error as expected. However, when i'm using subquery and again selecting the same column from the…
Channa
  • 742
  • 17
  • 28
4
votes
1 answer

get_detailed_sqlerrm syntax on PL/SQL

I'm trying to debug an "HTTP Request Failed" error on PL/SQL for a function trying to consume a SOAP web service. The suggestion is to use get_detailed_sqlerrm to get the details of the error message, but when I try to run the suggested query, it…
Meloku
  • 45
  • 1
  • 2
  • 9
4
votes
2 answers

Oracle11g numeric overflow in for loop

I have a for loop in pl/sql function something like: FOR i IN min..max LOOP variables i, min, max are declared as NUMERIC in my case min and max are ofen very large, but range itself is small, ie: min = 3232236033 max = 3232236286 as You see range…
canni
  • 5,737
  • 9
  • 46
  • 68
4
votes
1 answer

split comma separated values into columns dynamically

I am trying to find a sql to split a comma separated values which i have in a column into separate columns . I found several questions like it but none of the answer was able to handle the scenario of separated values increase in a future row . Is…
Pawan Rawat
  • 495
  • 1
  • 7
  • 25
4
votes
3 answers

how to insert long string oracle clob or blob

How I can insert very long text 100000
Meqenaneri Vacharq
  • 99
  • 1
  • 1
  • 12
1 2 3
…
99 100