1) What is PL/SQL ?
A: PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
2)What is the basic structure of PL/SQL ?
A: PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
DECLARE
--all the variables u use in ur program should be declared here
---
BEGIN
--application logic goes here
--
EXCEPTION HANDLING
--very imp
END
3)What are the datatypes a available in PL/SQL ?
A: Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORDS & COLLECTIONS.
4)What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
A:%ROWTYPE allows you to associate a variable with an entire table row. The
%TYPE associates a variable with a single column type.
5) What is difference between % ROWTYPE and TYPE RECORD ?
A:% 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.
6)What is PL/SQL table ?
A: Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
7)What is a cursor ? Why Cursor is required ?
A: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.
The oracle server uses works areas called private sql area.Here all the DML statement is executed and to processing statement.basically
8)Explain the two type of Cursors ?
A:there are two types of cursor
1.Implicit cursor.
2.Explicit cursor.
Implicit cursor is open for all DML statement.after execute the statement cursor is atomatically closed.
Explicit cursor is created by programmer.
explicit cursor is needed when
query returns more than one rows.
In that case,programmer creates
explicit cursor.open the cursor.
then fetch the value from the active set.
after fetching all the value,
cursor is closed by programmer.
9)What are the PL/SQL Statements used in cursor processing ?
A:DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
10)What are the cursor attributes used in PL/SQL ?
A:%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.
11) How to avoid using cursors? What to use instead of cursor and in what cases to do so?
Ajust use subquery in for clause
ex:
for emprec in (select * from emp)
loop
dbms_output.put_line(emprec.empno);
end loop;
no exit sttement needed..implicit open,fetch,close occurs
12)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;
13)What will happen after commit statement ?
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
After commit statement, all changed data will be saved in permenent database.
After commit all the locks on the database tables are leased.
14)Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor.
What is a database trigger ? Name some usages of database trigger ?
Trigger is a stored plsql program, which is fired automatically when an event occur on the database.
Triggers can be created on
1.DML statements
2.DDL staments
3.Viwes(for views only insted of trigger is valid)
4.Any database event occures like database startup or shutdown.
Advantages of triggers:
1.Data auditing.
2.Enforce complex Business rules.
3.providing security.
How many types of database triggers can be specified on a table ? What are they ?
We can fire 12 type of triggers.
DML: Insert, Update, Delete
Timing: Before, After
Levels: Row, Statment
all combination of these three will be 12 can be fired.Statement Trigger which fires only once regardless of number of rows.
Row Trigger which fires once for each row.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
we can use TCL commands in trigger by using autonomous transactions feature of oracle.
What are two virtual tables available during database trigger execution ?
Two tables are: OLD and NEW.
Insert Trigger :
OLD - no value.
NEW - inserted value.
UPDATE TRIGGER -
OLD- old value.
NEW- new updated value.
DELETE TRIGGER -
OLD - old value.
NEW - no value.
What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.Trigger will be called... Based on the event what trigger as to do. if trigger is also doing the same update statment then Mutating Table occurs. if trigger is not doing any DML statment nothing happens just Trigger will be called..
Write the order of precedence for validation of a column in a table I. done using Database triggers.ii. done using Integarity Constraints. ?
1. Before Statement Trigger
2. Integrity constraint
3. After statement trigger
What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block.
1. Predefined Exceptions: These are raised by oracle itself.
2. Non-predefined Exceptions: Declare the exception in declarative section and allow oracle to raise the error implicitly. This can be done using PRAGMA_EXCEPTION_INIT.
3. User defined exception: We can explictly define the exceptions bases on business rules and raise by using RAISE keyword The types are Predefined and user defined. Some of Predefined exceptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an user defined exception with an oracle error number. To get an error message of a specific oracle error.
x exception;
Pragma exception_int(x,error number)
What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.
Where the Pre_defined_exceptions are stored ?
Standard_package it consist of two sub packages...
for Ora-Errors
1.Oracle package(ORA)
for pls -Errors
2.pl/sql package(PLS)
What is a stored procedure ?
A stored procedure is a named pl/sql block which performs an action.It is stored in the database as a schema object and can be repeatedly executed.It can be invoked, parameterised and nested.
What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION is always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
A function can call directly by sql statement like select func_name from dual while procedure cannot.
In case of function, only it takes IN parameters
IN case of procedure
only it take IN,OUT,INOUT parameters..
What are advantages fo Stored Procedures ?
Extensibility,Modularity, Reusability, Maintainability and one time compilation.
Faster in execution: As Procedure is a complied schema object and stored in database, hence it takes less time to execution.
What are the modes of parameters that can be passed to a procedure ?
IN,OUT,IN-OUT parameters.
IN parameter is the default mode which acts as a constant inside calling environment.value passed in this parameter can not be changed.OUT parameter mode is used to pass value from calling environment into main block,here we can change the value.It acts as a variable inside calling environment.
INOUT parameter mode which pass value into calling environment and will get the value back in main block.
IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter.
OUT & INOUT parameter mode uses call by value method to pass values.
What are the parts of a procedure ?
basically procedure has three
parts
1.variable declaretion(optional)
2.body(mandetory)
3.Exception(optional)
Give the structure of the procedure ?
CREATE [OR REPLACE] PROCEDURE proc_name [Parameter List]
IS
BEGIN
-- statements
[EXCEPTION]
-- Exception handling
END;
Give the structure of the function ?
create or replace function <name>(arg1,arg2,....)
return datatype
as
....
variable declaration
begin
...
program code
...
return <variable>
exception
exception statement
end;
Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');
What is Overloading of procedures ?
Same procedure name with different parameters called procedure overloading, parameters may different by their datatypes, sequence and position.
eg1:
get_employee(Emp_id number);
get_employee(Emp_id Varchar(20));
eg2:
get_employee(ID number, name char(20))
get_employee(ID Number, name char(20), dept char(20))
get_employee(name char(20), dept char(20),ID Number)
What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
One more differene is cursor declared in a package specification must have RETURN type
How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called.
Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.
What is autonomous Transaction? Where are they used?
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.
example using prgma autonomous incase of mutation problem happens in a trigger.An autonomous transaction is a transaction that is started within the context of another transaction, called the parent transaction, but is independent of it.
An autonomous transaction can be committed or rolled back regardless of the state of the parent transaction.
We can specify a transaction as autonomous using a PRAGMA.
PRAGMA AUTONOMOUS TRANSACTION
What is ref cursor in pl/sql?
Ref Cursors known as Dynamic cursors cane be associated with as many SELECT statements you want at different times.You can associate with different SELECT stataments at dynamic time.
Static cursor (general) is associated with only one SELECT statement.It can't be with many.That too we can't use at dynamic time.
Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
refcur1 empcurtyp;
Begin
Open refcur1 for select * from emp;
Open refcur1 for select * from dept;
End;
What is mutating trigger? How do you resolve it?If a trigger body is trying to update dependent tables, will it get a mutating error?
If a trigger body is trying to update dependent tables the best approach in this case to avoid mutating trigger errors would be to make the transactions independent and not associated with triggers which is technically called as autonomous transactions.
What are the advantages of bulk binding in PL/SQL?
It retreives multiple rows with a single fetch...It increases the performance by reducing no.of context switches
How many number of triggers can be created on a table?
There is no limit on number of triggers on one table.
you can write as many u want for insert,update or delte by diff names.
What is the difference between stand alone procedures and stored procedures?
How to tune package, how can we see the output, which logic taking more time to execute?
Can we use commit or rollback command in the exception part of PL/SQL block?
Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function.
Cursor Advantages and Disadvantages?
Cursor advantages:
--> Using cursor to getting multiple values.
--> One cursor have multiple select statements
--> Cursor parameters
i.e cursor cursor_name (parameter datatype)
is
select * from table_name
where(condition)
-->Where Current of Clause: this is use full when the primary key is not present.
Cursor disadvantages:
-->It is returned by only one row at a time.
--> It gives less performance
-->Each time we fetch a row from the cursor, where as a normal cursor select statement query makes only one trip.
Is it possible to define a RETURN statement in the exception part of a function?
Explain it with example.?
Yes, you can return from exception block.
example: create or replace function TestReturn return varchar2 is
Result varchar2(1);--size 1
begin
Result := 123;--size 3
return Test;
exception when others then
return sqlerrm;
end TestReturn;
---------------------------
declare
test_ varchar2(100);
BEgin
test_ := TestReturn();
dbms_output.put_line(test_);
end;
.above code returns the exception
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
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 differenttable 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.
Can we create a table by using a procedure or a function?
We can create a table in procedure by executing a string statement using EXECUTE IMMEDIATE or using DBMS_SQL.EXECUTE.
How to return more than one value from a function?
f you want to return more than 1 value & dont want to use ref curser...try this, 1. Return comma separated varchar & then use dbms_utility.comma_to_table function to parse them.2. Try returning variable of type pl/sql table or varray.I have not tried number 2, but should work.
Can we give COMMIT or ROLLBACK within a trigger?
Although you cant give commit or rollback in trigger directly, still there are ways to give them
You can use the autonomus transactions to do that
What are the types of collection in PL/SQL? What is the advantage of nested tables?
Varrays,nested tables,index by tables are oracle collection.Index by tables also known as associative array similar to hashtables in java. Varrays are like arrays in java static in nature need to define maximum size at time of declaration.Nested tables are like nested class in java holding a table as a column of a table.
No comments:
Post a Comment