/*
Declare Variables
*/
declare
exceptionHandlingInd boolean := null;
exceptionRaised boolean := false;
onHandledErrorSayNothing boolean := false;
exceptionFormat constant varchar2(400):=
'Attempting to divide %d by %d resulted in an exception.';
exceptionFormatTechnicalDataTypeString constant varchar2(400):=
'%s:- %s';
exceptionFormatTechnicalDataTypeNumber constant varchar2(400):=
'%s:- %d';
formatBuffer constant varchar2(100) :=
'n: %d. m: %d. result: %d. ';
logMessage varchar2(4000);
numerator int;
denominator int;
answer int;
numeratorPLSInteger PLS_INTEGER;
denominatorPLSInteger PLS_INTEGER;
answerPLSInteger PLS_INTEGER;
v_err_code NUMBER;
v_err_msg VARCHAR2(200);
begin
exceptionRaised := true;
exceptionHandlingInd := true;
-- exceptionHandlingInd := false;
--onHandledErrorSayNothing := true;
onHandledErrorSayNothing := false;
numerator := 10;
denominator := 0;
-- denominator := 5;
/*
PLSinteger data type needed for utl_lms.format_message
*/
numeratorPLSInteger := numerator;
denominatorPLSInteger := denominator;
if ( exceptionHandlingInd = true ) then
begin
-- Attempting division
begin
answer := numerator / denominator;
exceptionRaised := false;
EXCEPTION
WHEN ZERO_DIVIDE THEN
/*
Process request to say nothing about exeption using null ( NOOP )
*/
if ( onHandledErrorSayNothing = true ) then
begin
null;
/*
onHandledErrorSayNothing = true -- begin -- end
*/
end;
else
begin
/*
Save Exception handling system codes
*/
v_err_code := SQLCODE;
v_err_msg := SQLERRM;
/*
Format erroring data
*/
logMessage := utl_lms.format_message
(
exceptionFormat
, numeratorPLSInteger
, denominatorPLSInteger
) ;
dbms_output.put_line(logMessage);
/*
Format erroring technical data - SQLERRM
*/
logMessage := utl_lms.format_message
(
exceptionFormatTechnicalDataTypeString
, 'SQLERRM'
, v_err_msg
) ;
dbms_output.put_line(logMessage);
/*
Format erroring technical data - SQLCODE
*/
logMessage := utl_lms.format_message
(
exceptionFormatTechnicalDataTypeNumber
, 'SQLCODE'
, v_err_code
) ;
dbms_output.put_line(logMessage);
end;
end if;
END;
end;
elsif ( exceptionHandlingInd = false ) then
begin
answer := numerator / denominator;
exceptionRaised := false;
end;
end if;
/*
Prepare output buffer using format function
*/
if ( exceptionRaised = false ) then
begin
/*
Capture answer as a PLS Integer
Needful for use a parameter when calling utl_lms.format_message
*/
answerPLSInteger := answer;
/*
Format result
*/
logMessage := utl_lms.format_message
(
formatBuffer
, numeratorPLSInteger
, denominatorPLSInteger
, answerPLSInteger
);
/*
Diplay variable message logMessage using
*/
dbms_output.put_line(logMessage);
end;
end if;
end;
/ Write, Run & Share Oracle queries online using OneCompiler's Oracle online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'Oracle' and start writing queries to learn and test online without worrying about tedious process of installation.
Oracle Database is world's most popular database built by Oracle Corporation. It is a multi-model database management system. It's known for its robustness, scalability, and comprehensive feature set, making it popular for enterprise-level applications and large-scale data management.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
CREATE TABLE EMPLOYEE (
empId NUMBER PRIMARY KEY,
name VARCHAR2(15) NOT NULL,
dept VARCHAR2(10) NOT NULL
);
INSERT INTO EMPLOYEE VALUES (1, 'Dave', 'Sales');
TRUNCATE table table_name;
DROP TABLE table_name;
--Line1;
/* Line1,
Line2 */
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: Column names are optional.
INSERT INTO EMPLOYEE VALUES (1, 'Ava', 'Sales');
SELECT column1, column2, ...
FROM table_name
[where condition];
SELECT * FROM EMPLOYEE where dept ='sales';
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';
DELETE FROM table_name where condition;
DELETE from EMPLOYEE where empId='0001';