Batch File to Append Date to file name
BOOLEAN Composite Types
RECORD TABLE VARRAY LOB Types BFILE BLOB CLOB
NCLOB
Reference Types
REF CURSOR
REF object_type
6. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are :
I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
Advantage is, if one change the type or size of the column in the table, it will be reflected in our
program unit without making any change.
%type is used to refer the column's datatype where as %rowtype is used to refer the whole record in
a table.
7. What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables. E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type); e_rec emp% ROWTYPE cursor c1 is select empno,deptno from emp; e_rec c1 %ROWTYPE.
8. What is PL/SQL table ?
A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers
One-dimensional
A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.
Unbounded or Unconstrained
There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.
Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.
Sparse
In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.
Homogeneous elements
Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.
With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.
Indexed by integers
PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -231-1 to 231-1, so you have an awful lot of rows with which to work
9. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
10. Explain the two type of Cursors ?
implicit cursor: implicit cursor is a type of cursor which is automatically maintained by the Oracle
Explicit Cursor: Explicit Cursor is defined by the Proframmer,and it has for phases:declare,open,fetch and close.explicit Cursor returns more than one row.
11. What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
12. What are the cursor attributes used in PL/SQL ? %ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors
13. What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes
when all the records have been processed. eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal; END LOOP;
cursor for loop is use for automatically open ,fetch,close 15. Explain the usage of WHERE CURRENT OF clause in cursors ?
PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor in order to allow you to easily make changes to the most recently fetched row of data.
The general format for the WHERE CURRENT OF clause is as follows:
UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name;DELETE FROM
table_name WHERE CURRENT OF cursor_name;
Notice that the WHERE CURRENT OF clause references the cursor and not the record into which the next fetched row is deposited.
The most important advantage to using WHERE CURRENT OF where you need to change the row fetched last is that you do not have to code in two (or more) places the criteria used to uniquely identify a row in a table. Without WHERE CURRENT OF, you would need to repeat the WHERE clause of your cursor in the WHERE clause of the associated UPDATEs and DELETEs. As a result, if the table structure changes in a way that affects the construction of the primary key, you have to make sure that each SQL statement is upgraded to support this change. If you use WHERE CURRENT OF, on the other hand, you only have to modify the WHERE clause of the SELECT statement.
This might seem like a relatively minor issue, but it is one of many areas in your code where you can leverage subtle features in PL/SQL to minimize code redundancies. Utilization of WHERE CURRENT OF, %TYPE, and %ROWTYPE declaration attributes, cursor FOR loops, local modularization, and other PL/SQL language constructs can have a big impact on reducing the pain you may experience when you maintain your Oracle-based applications.
Let's see how this clause would improve the previous example. In the jobs cursor FOR loop above, I want to UPDATE the record that was currently FETCHed by the cursor. I do this in the UPDATE statement by repeating the same WHERE used in the cursor because (task, year) makes up the primary key of this table:
WHERE task = job_rec.task AND year = TO_CHAR (SYSDATE, 'YYYY');
This is a less than ideal situation, as explained above: I have coded the same logic in two places, and this code must be kept synchronized. It would be so much more convenient and natural to be able to code the equivalent of the following statements:
Delete the record I just fetched. or:
A perfect fit for WHERE CURRENT OF! The next version of my winterization program below uses this clause. I have also switched to a simple loop from FOR loop because I want to exit conditionally from the loop:
DECLARE CURSOR fall_jobs_cur IS SELECT ... same as before ... ; job_rec
fall_jobs_cur%ROWTYPE;BEGIN OPEN fall_jobs_cur; LOOP FETCH
fall_jobs_cur INTO job_rec; IF
fall_jobs_cur%NOTFOUND THEN EXIT; ELSIF
job_rec.do_it_yourself_flag = 'YOUCANDOIT' THEN UPDATE winterize
SET responsible = 'STEVEN' WHERE CURRENT OF
fall_jobs_cur; COMMIT; EXIT; END IF; END
LOOP; CLOSE fall_jobs_cur;END;
16. What is a database trigger ? Name some usages of database trigger ? A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. Database triggers enable DBA's (Data Base Administrators) to create additional relationships
between separate databases.
For example, the modification of a record in one database could trigger the modification of a record
in a second database.
17. How many types of database triggers can be specified on a table ? What are they ? Insert Update Delete
Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k. Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement. If WHEN clause is specified, the trigger fires according to the returned Boolean value.
the different types of triggers: * Row Triggers and Statement Triggers * BEFORE and AFTER Triggers * INSTEAD OF Triggers * Triggers on System Events and User Events
18. What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available.
The two virtual table available are old and new.
19.What happens if a procedure that updates a column of table X is called in a database trigger of the same table ? To avoid the mutation table error ,the procedure should be declared as an AUTONOMOUS TRANSACTION.
By this the procedure will be treated as an separate identity. 20. Write the order of precedence for validation of a column in a table ?
I. done using Database triggers. ii. done using Integarity Constraints.
21. What is an Exception ? What are types of Exception ? Predefined
Do not declare and allow the Oracle server to raise implicitly
NO_DATA_FOUND