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
1 answer

Oracle database not recognizing type when using procedure

I declared my types in the package header: file_2.sql CREATE OR REPLACE PACKAGE MY_UTILS IS TYPE VECTOR IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; TYPE MATRIX IS TABLE OF VECTOR INDEX BY PLS_INTEGER; PROCEDURE PRINT_MATRIX(p_matrix IN…
Alexandru Antochi
  • 1,295
  • 3
  • 18
  • 42
4
votes
2 answers

Using variables in PLSQL SELECT statement

I have a query that queries on ReportStartDate and ReportEndDate so I thought I would use variables in PLSQL. Not sure what I am missing here, but I get an error: CLEAR; DECLARE varReportStartDate Date := to_date('05/01/2010', 'mm/dd/yyyy'); …
Raj More
  • 47,048
  • 33
  • 131
  • 198
4
votes
1 answer

what else can i use instead of htp.print and dbms_output on toad for oracle PL/SQL?

At the moment i have htp.print and DBMS_output to show the me the end result of user input. however, htp.print shows the confirmed message on the web browser and my DBMS_output doesn't work for some reason. But what i'm looking for is the…
A. Rahman
  • 71
  • 5
4
votes
2 answers

Call plsql script within another plsql script

I have a directory full of PLSQL scripts that I want to run, the problem is that the content of that directory is dynamic, and I have no way to know what the names of those scripts will be. I have to write some stuff to run all of the sql files in…
im8bit
  • 577
  • 1
  • 4
  • 17
4
votes
2 answers

Bulk update with commit in oracle

I am performing bulk update operation for a record of 1 million records. I need to COMMIT in between every 5000 records how can I perform? update tab1 t1 set (col1,col2,col3,col4)= (select col1,col2,col3,col4 from tab_m where row_id=…
Thej
  • 275
  • 2
  • 7
  • 18
4
votes
4 answers

Is PL/SQL the language to learn if you have Oracle 11g?

is PL/SQL the language of Oracle 11g? Is there some other langauge that Oracle uses? I don't mean front end applications that use other languages, just if I am in the database directly. I ask because I know SQL Server will let you use various…
johnny
  • 19,272
  • 52
  • 157
  • 259
4
votes
2 answers

PL/SQL no data found exception handling

I have a table EMAILS with columns: ROUTINE, EMAILS and COPIES defining to whom the result of particular procedure should be sent (as to/cc) using UTL_MAIL. I have the following code: PROCEDURE myproc AS NO_EMAIL_FOUND EXCEPTION; e…
sbrbot
  • 6,169
  • 6
  • 43
  • 74
4
votes
3 answers

How to generate a list periods of each every 30 days from start to today

I works for a company which request me generate a list of period for each 30 days since the item start until now. Sample: Item 'A' has begin date is 01/DEC/2016, and Item 'B' has begin date is 05/Feb/2016. Today is 07/FEB/2017. The output should…
Tùng
  • 43
  • 3
4
votes
2 answers

Oracle errors handling

I have such code: DECLARE e_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT(e_not_exist, -942); car_name VARCHAR2(20); BEGIN select name_of_factory into car_name from car where car_id = 1; dbms_output.put_line(car_name); EXCEPTION when…
maks
  • 5,911
  • 17
  • 79
  • 123
4
votes
2 answers

How to use MEMBER OF in PLSQL

I have seen questions with this error, but either are calling external stores or trying with incompatible types or using a varray. So i setup a very simple example and still i can not make it work. DECLARE TYPE mytype IS TABLE OF VARCHAR2(4) INDEX…
Neto Yo
  • 450
  • 1
  • 5
  • 17
4
votes
1 answer

Advantage of Internal Procedures Forward Declarations in PL/SQL Packages

Say I have the Package below (please take note of the comments): Create or replace package test_package_fdec as procedure ext_proc1; procedure ext_proc2; end test_package_fdec; / Create or replace package body test_package_fdec as …
Migs Isip
  • 1,450
  • 3
  • 23
  • 50
4
votes
1 answer

Invalid identifier error while running plsql block

Since my school does not allow me to post the code, hence i had to come back home and put up an example to show the issue i am facing. My school asked me to do a homework on dynamic sql to create a table and later insert one dummy record to it. But…
Nicky Nick
  • 135
  • 7
4
votes
1 answer

What is wrong with the WHILE Loop on PLSQL?

I'm practicing collections on PL/SQL, and now I've code a simple WHILE Loop to print on screen the values of the sparse associative array. I received the following error: Oracle Error -06502: PL/SQL: numeric or value error: NULL index table key…
user6611026
4
votes
2 answers

too many declarations of

I am getting the following error message while running the below code. I am new to coding world of pl/sql (oracle) and I request your assistance for the same. Code: create or replace package learn is function Area(i_rad NUMBER) return…
Nicky Nick
  • 135
  • 7
4
votes
1 answer

PLS-00201: identifier 'TYPE' must be declared

I'm connecting to an Oracle database as user1. Within the database, user2 exists and has a package pack1 which contains two stored procedures, proc1 and proc2. I'm trying to call those procedures, but I'm getting the aforementioned error. The error…
Lone_Wanderer
  • 179
  • 1
  • 3
  • 16
1 2 3
…
99 100