What are records? Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data...

Variables In PLSQL Variables must be declared first before the usage. The PL/SQL variables can be a scalar type such as DATE, NUMBER, VARCHAR(2), DATE, BOOLEAN, LONG and CHAR, or a composite type, such array type VARRAY. Only TRUE and FALSE can be assigned...

CREATE OR REPLACE PACKAGE test AS -- package spec TYPE list IS VARRAY(25) of NUMBER(3); PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2); PROCEDURE fire_employee (emp_id INTEGER); PROCEDURE raise_salary (emp_id INTEGER, amount REAL); END test;...

Writing PL/SQL Procedures/Functions PL/SQL functions returns a scalar value and PL/SQL procedures return nothing. Both can take zero or more number of parameters as input or output. The special feature about PL/SQL is that a procedure/function argument...

Write a Query To Delete The Repeated Rows from emp table; SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename) TO DISPLAY 5 TO 7 ROWS FROM A TABLE SQL>select ename from emp where rowid in(select rowid from emp where rownum<=7...

Partitions Partition is a data base object, it allows VLDB objects (Very Large Databases such as tables, indexes etc). By splitting large database objects into smaller, more manageable units. You can divide the rows of a single table into different parts...

Explain Plan Explain Plan : It is a statement that allows you to have oracle generate execution plan for any sql statement with out actually executing it.You will be able to examine the execution plan by querying the plan table. Plan Table : A Plan table...

Frequently Used Data Dictionary Queries 1.Index Table : Select INDEX_NAME ,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,NUM_ROWS, LAST_ANALYZED,PARTITIONED From All_Indexes; 2. Index Columns : select INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME, COLUMN_POSITION,...

What is a Trigger A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically....

Dynamic Management View Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the database about the current state of the SQL Server machine. These values help the administrator to analyze problems...