Oracle Exception The Oracle database contains the following types of exceptions: Internally defined are automatically raises by the runtime system. An internally defined exception always has an error code, but does not have a name. Predefined are internally defined exceptions that PL/SQL has given a name. For example, ORA-01403 (PL/SQL: NO_DATA_FOUND). The most used predefined exceptions are: NO_DATA_FOUND… Read More »
Oracle Cursors The Oracle database contains two types of cursors: Implicit cursors are automatically constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement. The implicit cursor attributes are: SQL%ISOPEN SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT SQL%BULK_ROWCOUNT SQL%BULK_EXCEPTIONS Explicit cursors are created by the developer user. The explicit cursor consists of the… Read More »
Oracle Tutorial – Learn how to create and drop objects on Oracle database. Learn how to use DML Statements, DDL Statements, Conditional Statements, SQL queries, joins, data types. The main objects in Oracle database are tables, procedures, functions, triggers, cursors, views, packages, constraints, collections, records, indexes, exceptions. Oracle tutorial Oracle Create Table Oracle Insert Oracle Update Oracle Delete… Read More »
Oracle Create Trigger To create a trigger uses the CREATE statement. A Trigger is a stored PL/SQL block associated with a table, a schema, or the database. The events that fire a trigger are: DML statements DDL statements System events such as startup, shutdown, and error messages User events such as logon and logoff Create Trigger syntax CREATE… Read More »
Oracle Create View To create a view uses the CREATE VIEW statement. Oracle view is a logical table based on one or more tables or views. A view contains no data itself. Create View syntax CREATE OR REPLACE VIEW view_name AS select * from table_name; CREATE OR REPLACE VIEW view_name AS select * from sql_query; Create View example… Read More »
Oracle Create Package To create a package uses the CREATE PACKAGE statement. A package is an encapsulated collection of database objects like procedures, functions, variables, types, exceptions. The objects are declared in the package specification. The package body defines the queries for the cursors and the code for the subprograms. Create Package syntax CREATE OR REPLACE PACKAGE package_name… Read More »
Oracle Create Function To create a standalone stored function uses the CREATE FUNCTION statement. Create Function syntax CREATE OR REPLACE FUNCTION function_name (arg1 data_type, …) RETURN data_type IS BEGIN …. END; / Create Function example CREATE FUNCTION get_name (p_id NUMBER) RETURN VARCHAR2 IS v_name varchar2(255); BEGIN SELECT name INTO v_name FROM CUSTOMERS WHERE id=p_id; RETURN v_name; END; /… Read More »
Oracle Create Procedure To create a standalone stored procedure uses the CREATE PROCEDURE statement. Create Procedure syntax CREATE OR REPLACE PROCEDURE proc_name (arg1 data_type, …) AS BEGIN …. END; / Create Procedure example CREATE PROCEDURE remove_customer (p_id NUMBER) AS v_name varchar2(255); BEGIN SELECT name INTO v_name FROM CUSTOMERS WHERE id=p_id; DELETE FROM customers WHERE id=p_id; DBMS_OUTPUT.put_line(v_name||’ with id:… Read More »
Oracle Loop Statements A LOOP statement executes a sequence of statements multiple times. The Oracle PL/SQL LOOP statements are: Basic loop – A loop that executes an unlimited number of times. An EXIT, GOTO, RAISE statement, or a raised exception ends the loop. WHILE loop – The While Loop is executed if the expression is TRUE. FOR loop… Read More »
Oracle Transaction Statements A transaction is a series of one or more SQL statements that Oracle Database treats as a unit. A transaction is an atomic unit. A transaction ends when it is committed or rolled back. The transaction control statements are: SAVEPOINT – is a point in a transaction to which you can later roll back. A… Read More »