PLSQL Questions

1. What is Trigger and its Type ?
Trigger hepls to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table).
Type :- DML, DDL and Database  Read More…  
 

2. What is Row Level trigger ?

A row-level trigger fires once for each row that is affected by a triggering event. For example, if deletion is defined as a triggering event for a particular table, and a single DELETE statement deletes five rows from that table, the trigger fires five times, once for each row.

 
3. What is Statement Level trigger ?

A statement-level trigger is fired whenever a trigger event occurs on a table regardless of how many rows are affected. In other words, a statement-level trigger executes once for each transaction. For example, if you update 1000 rows in a table, then a statement-level trigger on that table would only be executed once. Read More…

 

4. What is Nested Trigger ?
Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger, and so on. Triggers can be nested up to 32 levels: @@NESTLEVEL used to skip triggers fired 2 time.  Read more…

 
5. What is Instead of Trigger ?
An INSTEAD OF trigger is a trigger that allows you to update data in tables via their view which cannot be modified directly through DML statements. When you issue a DML statement such as INSERT , UPDATE , or DELETE to a non-updatable view, Oracle will issue an error. Read More…
 

 
6. Can we commit in trigger?
Yes, you can commit inside the trigger. But for this you have to make this trigger transaction to be an Independent transaction from its parent transaction, you can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allow you to build the Independent (child) Transaction, started by another.  Read More…
 

7. Difference between row level and statement level trigger ?

 

8. Difference between View and Materialized View ?

Views are a logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables.

Materialized views are also the logical view of our data-driven by the select query but the result of the query will get stored in the table or disk, also the definition of the query will also store in the database.

Type of View :- Simple, Complex,inline, read only,Force view
Read more…
 
 

9. Cursor and its Attribute ?
Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows. 

Attributes - %FOUND, %NotFOUND, %ISOPEN, %ROWCOUNT    

Type:- Implicit Cursor and Explicit Cursor

Read More…

10. What is Implicit Cursor ?

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.

11. What is Explicit Cursor ?

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement; 

 
12. What is REF Cursor ?
A ref Cursor is a PL/SQL datatype whose value is the memory address of a query work area on the database. It is very powerful way to return query result form database to client application.  regular cursor can be defined outside of a procedure or a function. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code. 

Type- Strong and weak - A strong ref cursor type definition specifies a return type but a weak does not.

declare   
    c1 SYS_REFCURSOR;
    ename varchar2(10);
    sal number;
begin
    open c1 for select ename, sal from scott.emp;
    LOOP
        FETCH c1 into ename, sal;
            EXIT WHEN c1%NOTFOUND;
        dbms_output.put_line('Ename: ' || ename || ', Salary: ' || sal);
    END LOOP;
    close c1;   
end;
/
 Read More…
 
 
 

13. Difference between Cursor and Ref Cursor ?
A ref cursor is a pointer to a result set. This is normally used to open a query on the database server, then leave it up to the client to fetch the result it needs. Read more…
 
 

14. What are the Exceptions ?
An exception is a runtime error or warning condition.PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. 

There are two types of exceptions − System-defined exceptions  and  User-defined exceptions

Read More…
 

15. What is Procedure ?
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. Read More…

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name; 

 

 
16. What is Recursive Procedure ?
Recursive stored procedure refers to a stored procedure which calls by itself until it reaches some boundary condition.  Read More…
 
 

17. What is Funtions ?
A function is same as a procedure except that it returns a value. Read More…

Syntax:

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

 

18. Parameter Modes in PL/SQL Subprograms ?

S.No	Parameter Mode & Description
1	IN An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is the default mode of parameter passing. Parameters are passed by reference.
2	OUT An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it. The actual parameter must be variable and it is passed by value.
3	IN OUT An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value.

19. What is Packages ?
Package is a schema object that contains definitions for a group of related functionalities. A package includes variables, constants, cursors, exceptions, procedures, functions, and subprograms. It is compiled and stored in the Oracle Database. Typically, a package has a specification and a body. Read more…

20. Advantages of Packages ?

The package is a powerful feature of PL/SQL that you should use it in any project. The following are the advantages of the package:

Make code more modular
Packages allow you to encapsulate logically related types, variables, constants, subprograms, cursors, and exceptions in named PL/SQL modules. By doing this, you make each package more reusable, manageable, readable and reliable.

Hide implementation details
Packages allow you to expose the functionality via their specifications and hide the detailed implementation in the package body.
It means that you can enhance the code in the body of the package without affecting other dependent packages or applications.

Improve application performance
Oracle loads the package into memory at the first time you invoke a package subprogram. The subsequent calls of other subprograms in the same package do not require disk I/O. This mechanism helps improve performance.

Minimize unnecessary recompiling code
Packages help avoid the unnecessary recompiling process. For instance, if you change the body of a package function, Oracle does not recompile the subprograms that use the function, because the subprograms are only dependent on the package specification, not the package body.

Manage authorization easily
By encapsulate objects in a package, you grant role on the package, instead of granting roles on each object in the package.

PL/SQL package concept is simple but powerful. They allow you to encapsulate the code and make your application easier to develop and maintain.
 

21. Collections and Methods ?

A Collection is an ordered group of elements of same data types.
Collections are most useful things when a large data of the same type need to be processed or manipulated. Collections can be populated and manipulated as whole using 'BULK' option in Oracle.

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. Read More…
 

 
22. What is BulK Collect ?
A bulk collect is a method of fetching many rows at once and place them in a collection. Read more…
 

Exception in Bulk.

Read More….
 

 
23. Pragma Autonoumous Tranansaction
Autonomous Transaction provides a functionality to the developer in which it allows to do changes in a separate transaction and to save/discard that particular transaction without affecting the main session transaction. Basically Pragma transactions are the child transactions for the main transaction.
This child transactions executes independently with the main transaction. Read More…
 
 

24. What is Mutating Error ?
A mutating table error comes when a row-level trigger tries to  change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).
Use autonomous transactions - We can avoid the mutating table error by marking  trigger as an autonomous transaction, making it independent from the table that calls the procedure.
 

 
25. What is Partition of table and type ?
Partitioning of table helps to divide large amount of data into specific parts,

Types :- By Range, By List, By Hash, By Composite. Read more…
 

 
26. What is Indexing ?
An index is used to performace tunnig in queries. which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table. 

Type of Index -  B-Tree,   Bitmap,     Reverse key, function based. Read more…   Read More2…
 
 

27. Difference between Custured and non Clustered Index ?
Clustered Index - Table is created with primary key constraints then database engine automatically create clustered index . In this data sort or store in the table or view based on their key and values.

Non-Clustered Index - Table is created with UNIQUE constraints then database engine automatically create non-clustered index . A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

28. What is Hint ?
Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the specific criteria.For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. index, noindex /*+INDEX(employe)*/
 

 
29. What is Overloading ?
When two or more procedure and functions share a common name but have different set of parameter either in number or in datatype then that is called overloading of procedure and function
 
 

30. What is Denormalization Technique ?
Denormalization is a strategy used on a previously-normalized database to increase performance. In computing, denormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data.

 
 
31. What is Normalization ?
Normalization is used to organize the data in such manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies. Read More…
 

32. What is ACID Property ?
ACID Properties in SQL Server ensures Data Integrity during a transaction.
 

33. What is Polymorphism ?
Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package
 

 
34. What is diffrence between 11g and 12 c ?
1. Advanced Indexing Techniques

2. Newly added Functions

3. Automatic Big table caching

4. Full Database caching

5. JSON support 

6. Invisible Columns
 
 
35. 12 c features ?

1. Robust Code using Constants for Data Type Lengths
create or replace package constants as
  tab_length constant pls_integer := 128;
end constants;
declare
  tab varchar2( constants.tab_length );  

 2. Single Statement Table Partitioning
 
 
36. 11g feature ?
1.REGEXP_COUNT function- new function which can count a stirng in a string
2.PL/SQL Sequence- Earlier to get a number from a sequence in PL/SQL we had to use SELECT command with DUAL table now you can direct refer like rollno:=rl.nextval;
3.Continue in PL/SQL -continue statement is introduced in PL/SQL loops to skip rest of the statements in an iteration and restart the next iteration.
4.Triggers Firing Order-When two or more triggers are defined for the same event, in the past it was not possible to define the order in which those triggers are fired when the event occurs.
5.Mixed Notation-  p1(10,20,30);   -- positional, p1( n2=> 20, n3 =>30 );  -- named, p1( 10, n3 =>10 );  -- MIXED is new
6.Virtual Columns - ALTER TABLE JOBS ADD (DIFFERENCE AS ( MAX_SALARY  - MIN_SALARY))
7.Fine Grained Dependency Tracking -Prior to Oracle11g, modifying the structure of a table would make dependent views invalid even thought the change to table has nothing to do with view. As we have a table called T1 and view V1
8.Compound Trigger- A compound trigger allows different blocks within a trigger to be executed at different timing points. It has a declaration section and a section for each of its timing points.
 

 
37. What is DBMS Profiler ?
The DBMS_PROFILER is a package which provides an interface to identify performance. 

File :- Proftab.sql,profload.sql

Table :- PLsql_profiler_date, PLSQL_Profiler_unit, PLSQL_Profiler_runs

 

 
38. What is DBMS JOB and Oracle Scheduler ?
We use Oracle Scheduler when we need to schedule a PL/SLQ expression.
 

 
39. What is SQL Loader ?
SQL*Loader loads data from external files into tables of an Oracle database.SQL LOADER utility is used to load data from other data source into Oracle. For loading it needs  CONTROL file , which defines structure of flat file
Open command prompt then sqlldr makess/makes@orcl control=controlfile.ctr.
 

 
40. What is UTL File ?
With the UTL_FILE package, PL/SQL programs can read and write operating system text files.
declare
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen(
                'UTL_DIR'     -- File location
              , 'test_file.txt' -- File name
              , 'w' -- Open mode: w = write.
                  );
 
  utl_file.put(fhandle, 'Hello world!'
                      || CHR(10));
  utl_file.put(fhandle, 'Hello again!');
 
  utl_file.fclose(fhandle);
exception
  when others then
    dbms_output.put_line('ERROR: ' || SQLCODE
                      || ' - ' || SQLERRM);
    raise;
end;
/
 
 

 
41. What is External table ?
An external table is a table whose data come from flat files stored outside of the database.You can also create views and synonyms for external tables. However, no DML operations ( UPDATE , INSERT , or DELETE ) are possible, and no indexes can be created, on external tables.
CREATE TABLE emp_load
  2    (employee_number      CHAR(5),
  3     employee_dob         CHAR(20),
  4     employee_last_name   CHAR(20),
  5     employee_first_name  CHAR(15),
  6     employee_middle_name CHAR(15),
  7     employee_hire_date   DATE)
  8  ORGANIZATION EXTERNAL
  9    (TYPE ORACLE_LOADER
 10     DEFAULT DIRECTORY def_dir1
 11     ACCESS PARAMETERS
 12       (RECORDS DELIMITED BY NEWLINE
 13        FIELDS (employee_number      CHAR(2),
 14                employee_dob         CHAR(20),
 15                employee_last_name   CHAR(18),
 16                employee_first_name  CHAR(11),
 17                employee_middle_name CHAR(11),
 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 19               )
 20       )
 21     LOCATION ('info.dat')
 22    );
 
 
 

42. Difference Between Function and Procedure ?
Proceudre:-
1.Used mainly to execute certain business logic with DML statement.
2.Procedure can return zero or more values as output.
3.Procedure cannot be called with select statement, but can be called from a block or from a procedure
4.It is not mandatory to return the value.
5.RETURN will simply exit the control from subprogram

Functions:-
1.Used mainly to perform some computational process and returning the result of that process.
2.Function can return only single value as output
3.Function can call with select statement , if function does not contain any DML statements and DDL statements..
function with DML and DDL statements can also call with select statement with some special cases (using Pragma autonomous transaction)
4.It is mandatory to return the value.
5.RETURN will exit the control from subprogram and also returns the value.
 
Read More for point no 3…
 

 
43. Can we return multiple value in function ?
Yes,you can return multiple value with function by using out parameter but the you won't be able to use in select statement.
CREATE OR REPLACE FUNCTION MAKESS.GET_COMPANY(
A NUMBER,
B in number,
C out NUMBER)
   RETURN NUMBER AS
BEGIN
C:=A+B;
RETURN(A-B);
END;
SET SERVEROUTPUT ON;
DECLARE
A NUMBER:=10;
B NUMBER:=5;
C NUMBER;
D NUMBER;
BEGIN
--SELECT MAKESS.GET_COMPANY1(A,B,C) INTO D FROM DUAL;
D:=MAKESS.GET_COMPANY1(A,B,C) ;
DBMS_OUTPUT.PUT_LINE(A);
DBMS_OUTPUT.PUT_LINE(B);
DBMS_OUTPUT.PUT_LINE(C);
DBMS_OUTPUT.PUT_LINE(D);
END;