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

PL/SQL on SquirreL SQL Client 3.7.1

I was trying to execute PL/SQL scripts on SQuirrel but it doesn't seem to work. I configured Oracle Thin Driver by adding ojdbc7.jar on "Extra Class Path", was able to connect to the database but when tried to run a simple code it gives an error: --…
Thiago Alves
  • 53
  • 1
  • 5
4
votes
2 answers

Search for sql function

I have a sql function in oracle create or replace function testfunc..... Compile it succesfully. When i verify all_procedures system table it's not there. select * from all_procedures where procedure_name like '%testfunc%'; Not sure whether i am…
Arav
  • 4,957
  • 23
  • 77
  • 123
4
votes
2 answers

Array handling in plsql script

I want to pass an array from shell script to my PL/SQL script as a single argument and then try to access the array elements in PL/SQL script using index. How can I achieve this?
sakshi garg
  • 141
  • 1
  • 7
4
votes
3 answers

use bind variable with LIKE % operator

In my PL/SQL have this big dynamic SQL using bind variables. I use the DBMS_SQL package to bind and execute the generated query. Based on certain criteria, where-clauses are added to the dynamic SQL. When I just use an "in" or a "=" to match my…
Tijs
  • 43
  • 1
  • 4
4
votes
2 answers

Oracle Pipelined function

I'm trying to create a function which returns an object that can be used in the FROM Clause. According to research on the oracle documentation I've found that a PIPELINED function is what I need. I have this code: CREATE TYPE type_struct AS…
Nuno Valente
  • 143
  • 2
  • 12
4
votes
0 answers

Query returns different results between TOAD and PHP

I'm using PHP for a web aplication, Oracle for the database and I test the queries with TOAD. When I ran the first query, I got 90 rows of results in both (PHP and toad): select cliente, nombre, cadena,sum(valor) valor, sum(docs)…
Gerson Alvarado
  • 66
  • 5
4
votes
1 answer

How to delete (drop) an Oracle AQ queue?

I wish to drop an Oracle AQ queue and the table associated with it. What are the commands needed to perform this operation?
Evandro Pomatti
  • 13,341
  • 16
  • 97
  • 165
4
votes
1 answer

How to send inline table as parameter to function that receives a table?

I need to use returning_tbl(), from the code below, inside a WITH clause and then pass the inline table created with the WITH clause, as a parameter to a function. Like in using_tbl_v2 (does not work at this time) using_tbl_v1 is just an example of…
Belun
  • 4,151
  • 7
  • 34
  • 51
4
votes
2 answers

How to find out PLSQL Compilation errors

create or replace procedure address_insert as CREATE type colorarray is varray(10) of varchar2(10); CREATE type cities is varray(6) of varchar2(20); CREATE type states is varray(6) of varchar2(15); CREATE type zipcodes is varray(6) of…
Darla
  • 75
  • 1
  • 2
  • 11
4
votes
1 answer

When does an Oracle Package Specification become INVALID

As far as I know a package body can be replaced and recompiled without affecting the specification. A package specification declares procedures and functions, not defines them, so they can not reference objects, that can make the package…
chumakoff
  • 6,807
  • 2
  • 23
  • 45
4
votes
1 answer

How to get value from nested table in pl sql table

I have created nested table as follow: CREATE OR REPLACE TYPE EMP_NO_NAME AS OBJECT ( EMPNO NUMBER(4), ENAME VARCHAR2(20), JOB VARCHAR2(20), MGR NUMBER(5), HIREDATE DATE, SAL NUMBER(7,2) ); CREATE OR REPLACE TYPE EMP_TABLE IS TABLE OF…
shashank
  • 79
  • 1
  • 8
4
votes
1 answer

Examining wrapped Oracle package contains strange hexadecimal and cannot be unwrapped

Got some wrapped packages in a database, normally doing a SELECT text FROM ALL_SOURCE WHERE NAME = 'CatPackage' and owner = 'Catology' order by line gives you a big blob of text like…
NibblyPig
  • 51,118
  • 72
  • 200
  • 356
4
votes
2 answers

TOO_MANY_ROWS raised, but variable still gets a value

I just discovered that if you have a SELECT INTO that raises a TOO_MANY_ROWS exception, the variable still gets assigned the value from the first record the query retrieved. Is that expected behavior? Here's my example: for co in my_cursor loop …
AndyDan
  • 749
  • 2
  • 13
  • 29
4
votes
4 answers

How to simple change node's attribute value of XMLTYPE in Oracle 11g r2?

I just wanna to change in this XML (contained in XMLTYPE variable) all nodes named "ChildNode" with "Name"="B" attribute values to "C": …
SomeOne
  • 365
  • 1
  • 5
  • 10
4
votes
5 answers

Concat firstname and lastname with space in between in oracle pl sql

I have one requirement to concat user first_ name, and last_name with space in between in Oracle. Ex: first_name is 'Hopkins' and last_name is 'Joe'. Full name should be printed as Hopkins Joe. I'm using Oracle 11g and it is working in SQL query,…
Ram
  • 45
  • 1
  • 3
  • 10
1 2 3
…
99 100