Ans)An inline view is just a subquery in place of a table in an INSERT, UPDATE, DELETE, or SELECT. If you could have issued a "create view as <subquery>" and then inserted/updated/deleted/selected from it, you can skip the create view and just inline it in the DML.
Consider:
SQL> select * from ( select empno, ename from emp where deptno = 10 );
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
SQL> delete from ( select empno, ename from emp where deptno = 10 );
3 rows deleted.
SQL> update ( select empno, ename from emp where deptno = 20 ) set ename = lower( ename );
5 rows updated.
2) What are the uid and user function?
UID returns an integer that uniquely identifies the session user (the user who logged on).
SELECT UID FROM DUAL;
UID
----------
19
USER returns the name of the session user (the user who logged on) with the datatype VARCHAR2. Oracle compares values of this function with blank-padded comparison semantics.
In a distributed SQL statement, the UID and USER functions identify the user on your local database. You cannot use these functions in the condition of a CHECK constraint.
SELECT USER, UID FROM DUAL;
USER UID
------------------------------ ----------
SCOTT 19
3)With set autocommit on command, a PL/SQL block having multiple insert, update or delete statements is executed. Now if the script aborts in between, will all the changes be committed or none at all ?
SET AUTOCOMMIT ON treats a PL/SQL block as a single transaction. In other words, no matter how many INSERT, UPDATE and DELETE statements that are executed in the PL/SQL block, the commit will happen only at the end of the complete block. Therefore, if the script aborts in the middle of the block, then nothing will be committed. But as long as the block completes successfully, all statements executed within the block will be committed.
3) What is %type and %rowtype?
The %TYPE and %ROWTYPE Attributes The %TYPE attribute provides the datatype of a variable, constant, or column. This attribute is particularly useful when declaring a variable or procedure argument that refers to a column in a database table. The %ROWTYPE attribute is useful if you want to declare a variable to be a record that has the same structure as a row in a table or view, or a row that is returned by a fetch from a cursor.
When you declare a construct using %TYPE and %ROWTYPE, you do not need to know the datatype of a column or structure of a table. For example, the argument list of a procedure that inserts a row into the EMP table could be declared as
CREATE PROCEDURE hire_fire(emp_record emp%ROWTYPE) AS ... END;
If you change the column or table structure, the constructs defined on their datatypes or structure automatically change accordingly.
However, while one type of dependency is eliminated using %TYPE or %ROWTYPE, another is created. If you define a construct using object%TYPE or object%ROWTYPE, the construct depends on object. If object is altered, the constructs that depend on object are invalidated.
4)What are mutating tables?
When a table is in state of transition it is said to be mutating. e.g. If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
A mutating table is a table that is in the process of being modified by an UDPATE, DELETE or INSERT statement. A table may also be considered to be mutating if it needs to be updated due to a DELETE CASCADE referential integrity constraint. The reason you may get a mutating table error (i.e. ORA-04091) is that if you fire a trigger which attempts to look at or modify the same table, the trigger is effectively prevented from seeing a consistent view of the data.
For example, let's say that you attempt to give every employee who makes $10/hour a 10% salary increase as follows: UPDATE employee SET salary = salary * 1.10 WHERE salary = 10 ;
This would mean that the changed salary for all these employees would be $11. Your UPDATE statement caused an AFTER ROW trigger to be fired that contained the following statement: SELECT salary FROM employee WHERE . . .
You would get the mutating table error because the trigger tried to query the original employee table but the original table was in the process of change. Which version of the table should Oracle retrieve when the SELECT is issued? That's the problem! Oracle simply will not allow this statement to occur, because it doesn't have any way of knowing how to provide the correct set of data (i.e. a consistent view of the data). Oracle would generate the ORA-04091 error and rollback both the trigger body and the triggering statement (i.e. the UPDATE).
Bottom-line: you cannot touch the table you are updating, deleting from or inserting into in any trigger or user-defined function code that it may execute
5)) PL/SQL table?
65) PL/SQL tables are a lot more useful than you might think. True, a PL/SQL table is LIKE a 1-dimensional array. However, unlike an array, it is a sparse structure. You only use the memory that you need. It's unlimited in it's size. Well, it IS limited by the amount of memory you have.
If you need to hold 100 items, then you must declare an array of that size with your normal programming langauges (such as C or Pascal or Basic). However, there really is no way to search the array other than a sequential search UNLESS you use hashing or a binary search.
With a PL\SQL table, the index is a binary index. The indexes do NOT have to be in sequential order. I can declare a PL/SQL table as such:
TYPE error_table_typ IS TABLE of NUMBER(7)
INDEX BY BINARY_INTEGER;
my_table error_table_typ;
I could then store numbers as follows:
my_table(1) := 100;
my_table(100) := 205;
my_table(150) := 1;
All that is in memory are these three "records". That's it. Now, if I wanted to store them this way in an array in C or Pascal, I would have to create an array of at least 150 elements. Of course, some of you may be thinking, "WHY would I want to store elements out of order?" Well, a PL/SQL table works great if you have data you have to constantly verify against. Selecting against a PL/SQL table is a LOT faster than having to constantly go against a table in the database. Loading the table into a PL/SQL table and then using the PL/SQL table to verify your data is much faster. THIS is where a PL/SQL table comes in handy.
Let's say you have a table that has an ID to indentify a business. You want the business name. The ID's are NOT neccessarily in a sequential order. But, they ARE numbers. Here's the table:
COMPANY_TABLE
company_id_nbr NUMBER(8);
company_name_txt VARCHAR2(200);
We create a PL/SQL table as follows:
DECLARE
TYPE company_name_table_typ IS TABLE of VARCHAR2(200)
INDEX BY BINARY_INTEGER;
my_table company_name_table_typ;
CURSOR my_cursor IS
SELECT company_id_nbr, company_name_txt
FROM company_table;
my_cursor_rec my_cursor%ROWTYPE;
BEGIN
OPEN my_cursor;
FETCH my_cursor
INTO my_cursor_rec;
WHILE my_cursor%FOUND
LOOP
my_table(my_cursor_rec.company_id_nbr) :=
my_cursor_rec.company_name_txt;
FETCH my_cursor
INTO my_cursor_rec;
END LOOP;
CLOSE my_cursor;
END;
Put this code into a procedure before you're main code runs (make the PL/SQL tables global, of course). Then, if we ever need to verify the company name and all we have is the id, we can then do the following:
DECLARE
name_txt VARCHAR2(200);
id_nbr NUMBER(8);
BEGIN
name_txt := my_table(id_nbr);
...
EXCEPTION
WHEN no_data_found
THEN
/* This will happen if the record does NOT exists */
/* in the PL/SQL table. */
END;
This is MUCH faster than going against the table in the database directly. THIS is the beauty of PL/SQL tables. At first, I too felt they were useless. Now,
I've gone back to many programs I've written and rewrote them to use PL/SQL tables with this kind of programming. I have noticed a 25%+ increase in the speed in the verification routines of my programs, which also decreases the amount of time my programs run (especially my overnight processes that are
huge).
With PL/SQL 2.3, you will be able to have PL/SQL tables with more than one column which means you can create a PL/SQL table that is a direct copy of a record of a table in the database. Until then, if you use PL/SQL version before 2.3, then you won't be able to have more than one column per PL/SQL table. But,
you just have to create a PL/SQL table for each column that has a different type and index each table by the same index. It's that simple.
