1 2 3 4 5 6 7 8 9 10 |
set serveroutput on declare dept_name varchar2(30); begin select department_name into dept_name from departments where department_id=120 ; dbms_output.put_line('Department Name:'|| department_name); end ; |
The output:
1 |
Department Name: Treasury |
declare: Variables are declare between this and the [b]begin[/b] keywords.
into: The value fetched using SQL is assigned to the variable.
dbms_output.put_line: Displays the variable’s value on screen and moves on to the next line.
Entering dbms_output.put, without a _line suffix may not produce an output.
This isn’t a procedure or a function but an anonymous block, if script had been unsaved it would have lost.
If it was a function it would have been saved in the database.
The dbms_output.put_line can be considered a form of debugging to check if the code is working properly. By continuously printing out the variable’s values to the screen, the code can be verified for correctness.
Example: To retrieve data for 5 units of a product, a Java program would have to perform 5 round trips to the database. With PL/SQL this can be achieved in only one go.
With some projects, where data isn’t being retrieved from a database quickly enough, code may need to be transferred from a Java program to PL/SQL.
In terms of performance, stored procedures are the fastest blocks of code in databases.
SGA – library cache (System Global Area) stores compiled SQL & PL/SQL code that has already been run, if the DBMS needs to run this code again it is fetched from the library cache without needing to be parsed again.
Parsing is a demanding operation on the database and takes time (as it checks whether there is authorization to use a database, if the needed columns are present within the table, etc.).
Procedures can be called using any programming language or by SQL. They can be run from any database that can be queried.
PL/SQL Program Block
The structure of a PL/SQL Program Block
declare
begin (required)
program logic (required)
exception
end (required)
1 2 3 4 5 6 7 8 9 |
{code} set serveroutput on -- no need to use toad (however toad is necessary if wanting to: execute as script) begin dbms_output.put_line('Hello World'); end ; -- a single line of comment /* This is how paragraphs of comments should be written */ |
All commands should end with a semicolon ;.
Example: The following code outputs “Hello” on to the screen
1 2 3 4 5 6 |
begin dbms_output.put_line('Hello'); end; / Hello PL/SQL procedure successfully completed |
Example: Outputs Hello(1 to 5).
1 2 3 4 5 6 |
beginfor i in 1..5 loop dbms_output.put_line('Merhaba'||i); end loop; end; / -- There may be no need for the forward slash character here in toad, but it's necessary in SQLPlus |
Output of the previous code block
1 2 3 4 5 6 |
Hello1 Hello2 Hello3 Hello4 Hello5 PL/SQL procedure successfully completed |
There is no need to use a declare here. The loop carries out an insert from 1 to 1000.
(NOTE: ‘loop’ and ‘end loop’ could have been written here instead of begin and end.)
As the insert and commit statements are enclosed in the loop, there is a commit operation following every insert operation. There isn’t much need for a rollback as if there hasn’t been an insert the statement is drawn back anyway. If the commit operation is done outside of a loop then it would be more useful to have a rollback.
This example isn’t one that really shows exception handling.
The PL/SQL assignment operator is :=
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
declare section_no number; section_name varchar2(50); begin section_no :=45; select department_name into section_name from departments where department_id=section_name; dbms_output.put_line('Section Name:'||section_name); end; The following error is due to there not being a department_id 45 declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 8 |
The code has been changed to catch errors messages as shown in the following lines.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
declare section_no number; section_name varchar2(50); begin section_no :=45; select department_name into section_name from departments where department_id=section_no; dbms_output.put_line('Section Name: '||section_name); exception when no_data_found then dbms_output.put_line('Unable to find Section Number: '||section_no); end; |
Output
1 2 |
Unable to find Section Number: 45 PL/SQL procedure successfully completed. |
DDL commands can’t be written in SQL directly but DML commands such as insert, update and delete can.
Dynamic SQL commands are required to run DDL commands such as alter, delete, drop and truncate.
Example: “execute immediate ‘drop table members”
1 2 3 |
begin drop table members; -- This command shouldn't be used here. end; |
Dynamic SQL is required to run a command as shown on line 2. The same is necessary to dynamically retrieve a table name. However dynamic SQL isn’t required to retrieve values from a table.
This is a general requirement for outputting the record counts of tables, e.g. for tables containing more than a million records.
A forward slash ‘/’ re-runs the previous statement. run does the same and in addition it outputs the statement on to the screen.
The dot operator ‘.’ keeps the syntax in the buffer and places it in the command line, as previously mentioned, it requires a forward slash to run it.
1 |
employee.salary%TYPE ---> as it is structured in the employee table's salary column |
As an exception handler for catching errors, “WHEN others THEN” should be written last. This is because general errors that haven’t been assigned, or specified, get processed at this point.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
set serveroutput on declare registryno employees.employee_id%type; name employees.first_name%type; surname employees.last_name%type; begin registryno := 199; select first_name, last_name into name, surname from employees where employee_id = registryno; dbms_output.put_line(name||' '||surname); end; Douglas Grant PL/SQL procedure successfully completed. |
If select has 3 values ‘into’ should also have 3 values, ‘into’ statements should only be a single line. For example if there are 2 people to be entered, then an ‘into’ shouldn’t used.
1 2 3 4 |
Declare EmpLname VARCHAR2(20) EmpSalary NUMBER(6) EmpBDate DATE |
Unlike SQL, PL/SQL also allows the assignment of Boolean variables, where variable naming conventions apply.
1 2 3 4 |
*max 30 characters *must begin with a letter *can contain letters, numbers and symbols. *the symbols that are allowed: $, #, _ |
These naming conventions are applicable to tables, columns and variables in PL/SQL.
Data Types
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
binary_integer: a datatype used to store signed (both positive and negative numbers) the same as number datatype PLS_integer: a newly entered PLS_integer is processed faster that binary_integer. Also faster than number in PL/SQL binary_float, binary_double: in terms of accuracy they come after decimal natural: natural numbers 0-2G (+) positive: 1-2G data type synonyms: synonyms used to run code originating from other databases synonyms used for numbers: dec, decimal, double, precision, float, integer, int, numeric, real, smalllint char character, string varchar2 varchar varchar written in mysql scripts are changed to varchar2 Declare NewEmpSalary Number(6) :=25000; NewEmpSal Number(6) DEFAULT 25000; "default" is the same as ":=". Generally ":=" gets used. Declare EndTheLoop := FALSE; |
BEGIN
A boolean can be assigned a value of true or false
In toad: view —> toad options
Select PL/SQL from languages
Click on code templates
Enter the preferred abbreviations in the opened window (i.e. declare begin end > dbe)
These can be entered while coding using ctrl+space
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
declare count number; begin count :=67; if (count < 100) then dbms_output.put_line('small count'); else dbms_output.put_line('large count'); end if; end; declare count number; begin count :=67; if (count < 100) then dbms_output.put_line('small count'); dbms_output.put_line('still too small'); else dbms_output.put_line('large count'); end if; end; declare is_male boolean; begin is_male :=false; if (is_male) then dbms_output.put_line('male'); else dbms_output.put_line('female'); end if ; end; |
— This outputs female — if the false is changed to true then the output is male Note: Booleans can’t be used in columns.
1 |
select dbms_random.value(1,5) from dual; |
Remove accidental numbers between 0 and 49. iAs with programming languages, if a constant is declared in the declare section, it’s assigned value cannot later be changed
1 |
declare date_ date ; name varchar2(20); begin date_ := sysdate; name := upper('mehmet'); dbms_output.put_line('date:'||date_); dbms_output.put_line('name:'|| name);end; declare date_ date ; name varchar2(20); begin date_ := sysdate; name := user; dbms_output.put_line('date:'||date_); dbms_output.put_line('name:'|| name);end; |
This example shows that functions can be used without statements such as ‘select user from dual:’. Comparison Operators
1 2 3 4 |
declare count1 number; count2 number; begin count1 := 14; count2 := 30; IF (count1>10 and count2>10) THEN dbms_output.put_line('Both are greater than 10'); END IF; end; |
— counts less than 10 will not get output onto the screen.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
declare count1 number; count2 number; begin count1 := 4; count2 := 30; IF (count1>10 OR count2>10) THEN dbms_output.put_line('One of the counts are greater than 10'); END IF; end; declare count number :=7; begin if(count=1) then dbms_output.put_line('count 1'); elsif (count=2) then dbms_output.put_line('count 2'); elsif (count=3) then dbms_output.put_line('count 3'); else dbms_output.put_line('The count is greater than 3'); end if; end; declare max_salary number; begin select max(salary) into max_salary from hr.employees; dbms_output.put_line('The highest salary is '||max_salary); end; declare max_salary number; min_salary number; avg_salary number; begin select max(salary) into max_salary from employees;select min(salary) into min_salary from employees; select round(avg(salary),2) into avg_salary from employees; dbms_output.put_line('The highest salary: '||max_salary); dbms_output.put_line('The lowest salary: '||min_salary); dbms_output.put_line('The average mean salary: '||avg_salary); end; |
This can be achieved with a single query:
1 2 3 |
create table copy_dept as select * from departmenst where 1=2 ; |
— The copy of departments remains empty if the rows aren’t retrieved this way.
— A condition of 1=1 being enforced results in rows being created together.
1 2 3 4 5 6 7 8 9 10 |
begin insert into copy_dept (department_id, department_name) values (10, 'test') ; commit; end; begin insert into copy_dept (department_id, department_name) values (10, 'test') ; commit; end; |
— commit can also be placed after end.
Should the commit be placed in or outside of the procedure ? If placed within the procedure, the user that runs the procedure would also have performed a commit.
Example:
A money transfer transaction scenario of where 50TL is sent from account no. 24 to account no. 30.
1 2 3 4 5 6 7 8 9 10 11 12 |
begin update accounts set amount = amount-50 where accountNo=24; update accounts set amount = amount+50 where accountNo=30; commit; exception when others then rollback; end; |
Example:
There was an error while the money was being withdrawn from account no. 20 and deposited into account no. 30.
If there was an upper bound constraint of 100TL and the amount to be transferred is 110TL, in such a scenario the amount won’t be transferred to account no. 30 and there would be a rollback.
When the procedure works, both transactions (of withdraw and deposit) should work together, if it doesn’t then both transactions should be rolled back.
In the example scenario of transferring 50TL from account no. 24 to account no. 30; if there is a commit after the first update, i.e. withdrawal, and due to an arbitrary constraint the money can’t be transferred to the receiving account the money would have been lost.
Running an update frequently is demanding on the dbms’ resources. Using the procedure enter an account number as well as the balance increase amount; if entering -50 for example, then 50TL should be deducted. An entry of +50 would deposit 50TL into the account. A commit done immediately following the amount deposit would result in an inconsistency, for this scenario.
Changes would still appear even if there isn’t a commit in the session. They wouldn’t appear in the other sessions.
“novalidate” in the commit insertions results in only validating the new data and not the old data. There is no alteration of data that doesn’t meet the conditions of the constraints.
The default setting is “enablevalidate”.
1 2 |
1 alter table employee 2 add EmpID NUMBER(4) CONTSTRAINT employeePK PRIMARY KEY NOVALIDATE ; |
create sequence EmpIDsequence:
As this is the Primary Key in the table it can’t insert the same numbers, instead it inserts the values from the sequence.
1 2 3 4 5 6 7 8 |
DECLARE BEGIN FOR i IN 1..1000 LOOP INSERT INTO employee (EmpID, lname, fname, ano, salary) VALUES (EmpIDsequence.NEXTVAL, 'Doe', 'John', 1, 30000 + i) ; END LOOP ; END ; / |
GO TO
This isn’t used often in corporate projects and has been removed from most programming languages.
Label
Can be considered a type of bookmark for executable statements within the code which the GOTO statement transfers control to.
LOOP
Using a ‘for’ statement isnt compulsory to run loops, ‘loop’ keywords are independent of ‘for’ statements.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE BEGIN FOR i IN 1..1000 LOOP INSERT INTO employee (EmpID, lname, fname, ano, salary) VALUES (EmpIDsequence.NEXTVAL, 'Doe', 'John', 1, 30000 + i) ; END LOOP ; END ; / declare count number; begin count := 1; loop dbms_output.put_line(count);end loop; end ; |
Printing from 1 to 10 using Loop
1 2 3 4 5 6 7 |
declare count number; begin count := 1; loop dbms_output.put_line(count);end loop; end ; |
If the count was omitted, the loop would have entered an infinite loop. Placing “exit when count> 100;” at the beginning is good practice.
It’s easier to achieve results, as shown in the following example using a ‘for’.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
declare count number; begin for i in 1..10 loop dbmps_output.put_line(count); end loop end ; begin for i in 1..10 loop dbms_output.put_line(i); end loop ; end ; |
Use a ‘for’ loop if the final count is known.
1 2 |
BEGIN FOR i in reverse 1..10 LOOP -- runs from 10 to 1 |
WHILE
Used whenever the final count is unknown. Checks the condition at the beginning of the loop.
1 2 3 4 5 |
declare count number; begin count := 1; while (count<=10) loop dbms_output.put_line(count); count := count+1; end loop ; end ; |
Note: This example didn’t work as it was supposed to.
1 2 3 4 5 6 7 8 9 10 11 12 |
declare total_students number ; student_grade number; total_grade number :=0 ; total_averages number :=0 ; begin total_students :=0; student_grade := &ogr_not ; while (student_grade>0) loop total_grade := total_grade + student_grade ; dbms_output.put_line('Grade Total: ' || total_grade) ; total_students := total_students + 1 ; dbms_output.put_line('Student no: ' || total_students) ; student_grade := &ogr_ not dbms_output.put_line('Not: ' || student_grade) ; end loop; ort_not := total_grade / total_students ; dbms_output.put_line('Ortalama: ' || total_averages) ; end; |
CASE
The difference between decode and case: Case can be used by itself, however Decode can’t be.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
select decode (........ into by using select decode a gender can be entered with case := decode (gender. 'M', 'Male', 'F', 'Female', 'No selection has been entered'); declare birth_month CHAR(3) message VARCHAR2(40); BEGIN x_ssn := &prompt_for_ssn; select bdate into x bdate from employee where ssn = x_ssn; birth_month := to_char (x_bdate, 'mon'); case birth_month when 'JAN' THEN message := 'start of the year' when 'FEB' THEN message := 'short month' ELSE message := 'No comment' end case; DBMS_OUTPUT.PUT_LINE (message); END; / |
Exception
As good practice, errors starting with ora- shouldn’t be displayed to users, who may be unfamiliar with the error codes.
These errors should be caught with an exception to display them as messages such as “Unable to find record”, etc.
1 2 3 4 5 6 7 8 9 |
declare dept_name varchar2 (50); begin select department_name into dept_name from departments where department_id=&team; dbms_output.put_line('Department: '|| dept_name ); end; |
If team doesn’t contain any data, by default the error message would be ORA-0143 no data found. By having exception handling code in every procedure, a less technical and easier to understand error message can be displayed to users.
‘others’ is an Oracle operator that catches all error messages and can be used to output a generic error message.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
declare dept_name varchar2 (50); begin select department_name into dept_name from departments where department_id=&takim; dbms_output.put_line('Departman: '|| dept_name ); exception when others then dbms_output.put_line('There has been an error.'); end; declare dept_name varchar2 (50); begin select department_name into dept_name from departments where department_id=&team; dbms_output.put_line('Department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find the entered record number.');end; declare dept_name varchar2 (50); begin select department_name into dept_name from departments where department_id=&department; dbms_output.put_line('Department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department);end; Error message: "Unable to find record no. 400" |
An example of a Divide by Zero error in Oracle:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
declare dept_name varchar2 (50); count number; begin count :=7/0; select department_name into dept_name from departments where department_id=&department; dbms_output.put_line('Department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department); when others then dbms_output.put_line('There has been an error.'); end; |
The previous code would output: “There has been an error.”
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
declare dept_name varchar2 (50); count number; begin count :=7/0; select department_name into dept_name from departments where department_id=&department; dbms_output.put_line('Department: '|| dept_name );exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department); when zero_divide then dbms_output.put_line('Divide by zero error.'); --when others then --dbms_output.put_line('There has been an error.'); end; |
The error output: “Divide by zero error.”
1 2 |
alter table emp drop primary key cascade; -- Can be done without entering a table name alter table emp drop constraint pk_teams_tid cascade; -- If it is another table's foreign key then it would drop the table without outputting an error |
A test scenario where the same Dept (department_id) is entered consecutively.
As a result the dbms is unable to find 2 different records with the same department_id:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
declare dept_name varchar2 (50); sayi number; begin -- count :=7/0; select department_name into dept_name from dept where department_id=&department; dbms_output.put_line('Department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department); when zero_divide then dbms_output.put_line('Divide by zero error.'); when others then dbms_output.put_line('There has been an error.'); end; "There has been an error." exception when others then null; |
The previous line can be understood as: don’t display messages, its better to use an exception. When there is an actual error it overrides others.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
"There has been an error." declare dept_name varchar2 (50); count number; begin --count :=7/0; select department_name into dept_name from dept where department_id=&department; dbms_output.put_line('Department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department); when zero_divide then dbms_output.put_line('Divide by zero error.'); --when others then -- dbms_output.put_line('There has been an error.'); end; declare dept_name varchar2 (50); count number; begin --count :=7/0; select department_name into dept_name from dept where department_id=&department; dbms_output.put_line('Department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department); when zero_divide then dbms_output.put_line('Divide by zero error.'); --when too_many_rows then -- dbms_output.put_line('There have been more results than expected'); when others then dbms_output.put_line('There has been an error.'); end; Error output "There have been more results than expected" declare dept_name varchar2 (50); count number; begin --count :=7/0; select department_name into dept_name from dept where department_id=&department; dbms_output.put_line('Department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department); when zero_divide then dbms_output.put_line('Divide by zero error.'); --when too_many_rows then --dbms_output.put_line('There have been more results than expected');when others then dbms_output.put_line('There has been an error.'||SQLERRM); end; Error message: "ORA-01422: exact fetch returns more than requested number of rows" |
SQLERRM is an important DBMS feature as it can at least output the type of error that has been encountered.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
declare dept_name varchar2 (50); count number; begin --count :=7/0;select department_name into dept_name from dept where department_id=&departman; dbms_output.put_line('Department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department); when zero_divide then dbms_output.put_line('Divide by zero error.'); --when too_many_rows then --dbms_output.put_line('There have been more results than expected');when others then dbms_output.put_line('There has been an error. ' ||SQLCODE|| ' ' ||SQLERRM); end; Error message: "ORA-01422: exact fetch returns more than requested number of rows" |
An example of SQLERRM being more useful is that it can output an error message in a local language.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
declare dept_name varchar2 (50); count number; begin --count :=7/0;select department_name into dept_name from dept where department_id=&department; dbms_output.put_line('department: '|| dept_name ); exception when no_data_found then dbms_output.put_line('Unable to find record no. '||&department); when zero_divide then dbms_output.put_line('Divide by zero error.'); --when too_many_rows then --dbms_output.put_line('There have been more results than expected');when others then dbms_output.put_line('There has been an error. ' ||SQLCODE|| ' ' ||SQLERRM); end; The resulting error message: "ORA-01422: exact fetch returns more than requested number of rows" |
SQLERRM is a better procedure as it can output the ORA-01422 error dialog in a local language.
1 2 3 |
exception when others then dbms_output.put_line(SQLERRM); |
At least it can do something like:
Pragma Exception_INIT
1 2 3 4 |
alter table hr.dept modify DEPARTMENT_name not null novalidate; insert into dept (department_id) values (700); Outputs an error such as: " ORA-01400 " : cannot insert null into.... |
As this is an undefined error, it can be declared using ‘pragma’, which is a useful way of catching undeclared errors.
1 2 3 4 5 6 7 8 9 |
declare not_null_not_allowed exception ; pragma exception_init(not_null_not_allowed , -1400);begin insert into dept (department_id) values (700); exception when not_null_not_allowed then dbms_output.put_line('Error: Can't leave a not null field !'); end; |
Error: Can’t leave a not null field !
Implicit Cursors
1 2 3 4 5 6 7 8 9 10 |
begin delete from copy_dept where department_id < 30 ; dbms_output.put_line(SQL%ROWCOUNT||'records have been deleted.'); end; With an output like "3 records have been deleted", it is possible to see the number of records that have been affected. begin delete from copy_dept where department_id > 1000 ; if (SQL%FOUND) then dbms_output.put_line('Deletion completed.'); else dbms_output.put_line('Unable to delete.'); end if ; end; Returns "unable to delete" message. |
SQL%FOUND: If the SQL changes any records, SQL%FOUND becomes FALSE as all of the values in the example become less than 1000. SQL%NOTFOUND becomes TRUE, as SQL%NOTFOUND is the reverse of SQL%FOUND.
To delete values larger than 30, SQL%FOUND becomes TRUE. This can be considered a confirmation of running the SQL query.
User-Defined Events
Example:
A condition where a salary shouldn’t be less than the average salary has been defined.
This is to be applied to new users so that their entered salary amount is checked against the average and a higher salary should be entered.
Oracle doesn’t have error handling if a lower salary has been entered. Error handling due to the business logic should be defined by the software developer.
In an example condition of not allowing more than 10 people to be on leave during a working day. If the human resources department wants to grant leave to 11 people, the dbms should catch and output a defined business logic error.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
declare salary number; avg_salary number; higher_than_avg_salary exception; begin salary :=3000; select round(avg(salary)) into avg_salary from employees; if (salary < avg_salary) then raise higher_than_avg_salary; end if;dbms_output.put_line('Average Salary: ' ||avg_salary); exception when higher_than_avg_salary then dbms_output.put_line(' The entered amount of (' ||salary||') should not be less that ('||avg_salary||')); end; The output "The entered amount of 3000 should not be less than 6462" |
Note: A range of 20000 – 20999 has been reserved for users.
1 2 3 4 5 6 7 8 9 10 |
begin salary :=3000; select round(avg(salary)) into avg_salary from employees; if (salary < avg_salary) then raise_application_error(-20100,'The entered salary should not be less than the average salary.'); end if;dbms_output.put_line('Average Salary: ' ||avg_salary); end; Error Output "ORA-20100: The entered salary should not be less than the average salary." |
The examples shown here should not be placed in Procedures or Functions as any errors would be sent to the application server instead of being displayed to the user.
Flashback Recovery
13 March 2019