PL/SQL

The primary key is used to ensure that all values in a column are not null and unique. This key combines the unique and not null constraint properties into one key. This type of key can be applied to any data type and is used to avoid missing and duplicate data. You can only have […]

Mutating table Error Occurs when Trigger is Querying or Modifying a “Mutating Table” ORA-04091 is a very common error that occurs with triggers if triggers are not managed properly. A full understanding of triggers will help you avoid that error. A mutating table is a table that is currently being modified by an update, delete, […]

Cursor Variables Cursor variables are like C or Pascal pointers, which hold the memory location (address) of an item instead of the item itself. Thus, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has the data type REF X, where REF is short for REFERENCE and X stands for […]

When data from more than one table in the database is required, a join condition is used. Rows in one table can be joined to rows in another table according to common values that exist in the corresponding columns (usually primary and foreign key columns). To display data from two or more related tables, write […]

Transaction Control Statements A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. Transaction control statements are used in a database to manage the changes made by DML statements and to group these statements into transactions. Each transaction is assigned a unique transaction_id and it groups SQL statements so […]

Data Definition Language DDL statements enable you to alter the attributes of an object without altering the applications that access the object. You can also use DDL statements to alter the structure of objects while database users are performing work in the database. These statements are most frequently used to: Create, alter, and drop schema […]

You can use the SELECT statement to Identify the columns to be displayed and retrieve data from one or more tables, object tables, views, object views, or materialized views. A SELECT statement is also known as a query because it queries a database. Syntax: SELECT {*|[DISTINCT] column|expression [alias],…} FROM table; In its simplest form, a […]

SQL is a command language that is used for communication with the Oracle server from any tool or application. Oracle SQL contains many extensions. When you enter a SQL statement, it is stored in a part of memory called the SQL buffer and remains there until you enter a new SQL statement. SQL*Plus is an […]

SQL is a command language that is used for communication with the Oracle server from any tool or application. Oracle SQL contains many extensions. When you enter a SQL statement, it is stored in a part of memory called the SQL buffer and remains there until you enter a new SQL statement. SQL*Plus is an […]

Procedures and Functions In my earlier posts, we have seen anonymous blocks were the only examples of PL/SQL code. As the name indicates, anonymous blocks are unnamed executable PL/SQL blocks. Because they are unnamed, they can be neither reused nor stored for later use. Procedures and functions are named PL/SQL blocks that are also known […]