Ans: A package named STANDARD defines the PL/SQL environment. The package spec includes All the types, functions, procedures, etc which are automaticaly available to PL/SQLprograms. For eg: ABS(),SUM(),COUNT()
DBMS_OUTPUT - used to display a messgae, especially while debugging
DBMS_PIPE - This allows 2 different sessions of the database to communicate over the named pipe.
DBMS_DDL - Contains procedures to wrap single PL/SQL units like package specs, package body, function, procedure, type specs, type body, etc. Eg : DBMS_DDL.CREATE_WRAPPED();
DBMS_ALERT - lets you use database triggers to alert an application when some database values change
UTL_FILE - it allows the PL/SQL program to read from and write into text files.
UTL_SMTP - allows the PL/SQL program to send emails over Simple Mail Transfer Protocol
UTL_HTP, HTF - allows PL/SQL to generate HTML tags.
UTL_HTTP - allows PL/SQL programs to make HTTP calls. It gets the data from Internet. The inputs ar the URL and the contacts of the website. The return data is usually in HTML format.
DBMS_SQL - Helps you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. For ex: it allows us to create a procedure based on a table, whose name is given at run-time.
12. What are database trigger? Types of database trigger?
Ans: These are the stored subprograms associated with tables, views , schemas or database
Table level triggers - 3 ways to catagorize these triggers : Before/After , Insert/Update/Delete, and Row-level (for each row) vs statement-level
View level - We cannot use the Before/After triggers on views, so we use " Instead of " triggers. Eg: Create or replace trigger trg_cal_sal INSTEAD OF INSERT on v_emp....
System triggers on database or Schema level - event triggers : Eg: LOGON, LOGOFF
13. What are implicit and explicit Cursors? Which one is recommended ?
Ans: A cursor is a handle or a pointer for the memory associated with a specific SQL statement. A cursor is basically an area allocated by Oracle for executing the Sql Statement. An implicit cursor statement are managed by Oracle itself and generally used for Single row query. When u need precise control on the cursor and its processing, use Explcit cursors used for multi row query.
Implicit cursor is always faster than explicit cursor.Bcoz, expilit cursor will have more no of lines, and the rule is "Less the no of lines less is the processing time." Also, implicit cursor does exception handling for NO_DATA_FOUND and TOO_MANY_ROWS by itself . We need not to write these exceptions. In explicit cursors, we have to write these exceptions.
14. What is mutating error? How do u resolve it?
Ans : When you perform a DML query (like Insert, Update, Delete) on a table,and another query is accessing the same table at the same time,none of the queries get completed and the table is called as mutuating table.It will then throw a mutating error. You can perform only 'Select' on such a table. You can resolve this error by using a PL/SQL table.
15.What is the difference between cursor & ref cursor?
Ans: Cursor is a handle or pointer to the memory location associated with a SQL statement. With a REF cursor, you can define a cursor variable, which will point to that memory space. With Ref cursor, we can dynamically retrive data in form of recordset from a PL/SQL stored procedure.
It works very similar to explicit cursor except that we define the query for this cursor at run-time.
The syntax of explicit cursor :
Cursor emp_cur IS
SELECT * from emp;
.......
OPEN emp_cur;
Syntax of REF cursor :
TYPE emp_curtype IS REF CURSOR RETURN emp%ROWTYPE; --cursor type
emp_curvar emp_curtype; --cursor variable
.........
v_state := 'NY'
OPEN emp_curvar FOR select * from emp where state = v_state;
..........
v_state := 'NJ'
OPEN emp_curvar FOR select * from emp where state = v_state;
As you can see, REF cursors can be reused for different SQL queries at runtime.
There are 2 types of REF cursors : Strong and weak.
TYPE emp_curtype IS REF CURSOR RETURN emp%ROWTYPE; --Strong REF cursor
TYPE emp_curtype IS REF CURSOR; --weak REF cursor
Which one is better ? depends on your requirement. In strong cursor, we attach the return ROWTYPE at the declaration time itself. So, the compiler knows what the cursor variable should return.So, its less error prone. Weak cursors , on the other hand , are more flexible because they can be used with any query, any return ROWTYPE , even different ROWTYPES at different calls to it.
16. What are different options in function declaration ?
Ans:
AUTHID : Specifies how to execute the function. Ex: DEFINER/CURRENT USER
PARALLEL_ENABLE: Specifies if we can run the function parallelly
DETERMINISTIC:
AUTONOMOUS_TRANSACTION: Specifies if this function is an Autonous Transaction
17. What is PRAGMA ? What are the different types of PRAGMA ?
Ans: Pragmas are specially formatted comments. Pragma tells Oracle to use specific rules other than the default rules for a particular object. They are processed at compile time, not at run time.
1. RESTRICTED_REFERENCES : This pragma checks if the function meets the rules.
create package cal_loans as
function credit_ok return boolean;
PRAGMA RESTRICTED_REFERENCES(credit_ok, RNPS,WNDS );WNDS - Write No Database State
RNPS - Read No Package State
2. EXCEPTION_INIT : This pragma is used to bind a user-defined exception to a particular number.
Declare MyExcep exception;
PRAGMA EXCEPTION_INIT(MyExcep,10001);
begin
..…
exception
when I then
dbms_output.put_line( 'Duplicate value');
end;
3. AUTONOMOUS_TRANSACTION: Its a kind of PRAGMA which changes the way a subprogram works within a transaction. A child program marked with this pragma can be committed and rolled back without committing or rolling back the parent transaction.
Eg : When you declare a trigger as AUTONOMOUS_TRANSACTION, then you can commit inside the trigger, irrespective of the calling procedure.
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
COMMIT;
END;
4. SERIALLY_REUSABLE: This pragma tells the PL/SQL engine to reuse the package data. By default, the package level data is available throughout the session, by specifing this pragma, the data will not persist after the package is executed. You should specify the PRAGMA in both, package specification and body.
18.How to avoid errors with Autonomous transactions?
Ans:
1. If the autonomous transactions uses the resources used by main transac, a deadlock will occur.
2. The auto transac should be commited or rolled back beforeexiting, or oracle will give error.
3. The maximum no of concurrent transac is set by Oracle. That number might be exceeded as autonomous transac and main transac run concurrently.
19.What is the difference between Autonomous Transaction and Nested Transaction ?
Ans: As in case of Nested Transaction, Autonomous Transaction :
1. Do not share trasaction resources like variables with main transaction.
2.Have to be commited or rolled back separately.
3. It does not depend on main transaction. When main transaction rolls back, nested transaction also rolls back but autonomous transaction does not.
4.Exceptions raised in autonomous transaction are transaction-level and not statement-level.
20.What is the difference between ANY and ALL conditions?
Ans:ANY will check for the rows in the right side matching the condition and returns the rows for which the condition is true. It will not wait and search for all rows.
ALL will check if the condition is true for ALL rows and then only it will return the value.
21.What is the difference between UNION and UNION ALL?
Ans: Both do the same job but UNION ALL will give all rows,whereas UNION will skip the duplicate rows and give only distinct rows. Hence, UNION is faster than UNION ALL.
22.What is the difference between CHAR and VARCHAR2?
Ans:Char is used for fixed length strings, varchar2 is used for variable length string. Char is faster than varchar2,so if the length is fixed and not very long, it is recommanded to use Char.
23.What is the difference between EXISTS and IN operator?
Ans:IN works same as OR operator. The IN statement is internally converted into no of OR statements.It returns all the values for which the condition matches. EXISTS first analyzes the subquery to check if it returns any rows. If yes, then it returns True, else it returns False.
Exists - always has a subquery, IN- may have a subquery of list of values.
Example:
SELECT e.* from employees e
where exists(select deptno from dept d where e.deptno = d.deptno);
24.What are Global Temporary tables?
Ans: The temporary tables are the database tables or PL/SQL tables. The maintainance and management of these tables is handled by Global Temporary Tables. They are temporary because the data is available only to the current session. They are called as Global because the table definition is available to all sessions.
Example: Create global temporary table my_g_temp_tab(
name varchar2(20), city varchar2(10) )
on commit delete rows /*OR on commit reserve rows*/;
25.
