PL SQL Interview Questions

Ref cursor :

=====================

Cursor doesnt have a return type but

A Reference Cursor have a return type and it as 2 type one is 

Strongly Typed Cursor and Weakly Typed Cursor.

Another difference is REF curson can be assigned dynamically while Normal cursor once defined you cannt change it

ref cursor is dynamic,cursor is static.

CURSOR

In cursor there are 2 types explicit and implicit cursor

Explicit cursor

Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in the execution or exception sections of your programs.

IMPLICIT CURSOR

Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block, you are working with implicit cursors. These statements include INSERT, UPDATE, DELETE, and SELECT INTO statements. Unlike explicit cursors, implicit cursors do not need to be declared, OPENed, FETCHed, or CLOSEd.

REFERENCE CURSOR

A cursor variable is a data structure that points to a cursor object, which in turn points to the cursors result set. You can use cursor variables to more easily retrieve rows in a result set from client and server programs. You can also use cursor variables to hide minor variations in queries.

The syntax for a REF_CURSOR type is:

    TYPE ref_cursor_name IS REF CURSOR    [RETURN record_type];

If you do not include a RETURN clause, then you are declaring a weak REF CURSOR. Cursor variables declared from weak REF CURSORs can be associated with any query at runtime. A REF CURSOR declaration with a RETURN clause defines a "strong" REF CURSOR. A cursor variable based on a strong REF CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN at runtime.

To use cursor variables, you must first create a REF_CURSOR type, then declare a cursor variable based on that type.

declare

type rc is ref cursor ;

ref_cur rc;

cursor c is select empno,ename from scott.emp;

v_empno scott.emp.empno%type;

v_ename scott.emp.ename%type;

begin

  if 1=2 then 

  open ref_cur for select empno,ename from scott.emp;

  else

    open ref_cur for select empno,ename from scott.emp

    where deptno=30;

    end if;

  loop

      fetch ref_cur into v_empno,v_ename;

      exit when ref_cur%notfound;

    dbms_output.put_line(v_empno||'  '||v_ename);

    end loop;

    close ref_cur;

    end;

 

What is the main purpose of RETURN in functions?

 RETURN clause in functions is used to return value after executing series of execution steps in a function.

How to get 1st date of a month ?

select trunc(sysdate,'DAY') FROM DUAL;

select trunc(sysdate,'MONTH') FROM DUAL;

select trunc(sysdate,'YEAR') FROM DUAL;

What is the difference between procedure and function and package, which is the fastest?

procedure: It may or may not return a value.

function: It always return a value.

procedure :it can't be used in select query.for example if we have a procedure abc(salary number ) then we can't use it as " select abc(salary) from employee.

whereas , we can use functions in a query . "select sum(salary) from employee"

package is like a filler you can write procedure, functions within the package

Package is the fastest, because For standalone function & procedure if you call means each and every time it will complie, but for the package once if you complied it means when ever you call that package it need not recomplie so obviously the performance will increase thats why always package is best

What is mutating trigger? How do you resolve it?

Mutating trigger has similar concept to that of deadlock. When a table is in the half way of executing a transaction and is the owner of a trigger and if the same table is accessed by the trigger somewhere else in the same time then at that instance mutating trigger occurs

Mutating trigger is trigger that is currently being modified by DML opertion. For eg.,

You created a trigger trigger1 on table1, it should fire after update for each row. And you wrote some update statement on the same table (table1) inside the trigger . When you execute the individual update stmt on table1, the trigger get fires and the trigger also is currently being updated the same rows in table1, which is called mutating error and mutating trigger.

    CREATE OR REPLACE TRIGGER TRG_UPDATE_SAL

    BEFORE UPDATE ON EMP

    FOR EACH ROW

    BEGIN

    IF :NEW.SAL<:OLD.SAL THEN

    UPDATE EMP SET COMM =2000;

    ELSIF :NEW.SAL<:OLD.SAL THEN

    UPDATE EMP SET COMM=1000;

    END IF;

    END;

Oracle PRAGMA'S:

PRAGMA

Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compile.

Types Of Pragmas :-

1 - AUTONOMOUS_TRANSACTION

2 - EXCEPTION_INIT

3 - RESTRICT_REFERENCES

 The instruction is a statement that provides some instructions to the compiler.

1 - AUTONOMOUS_TRANSACTION

This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction. For instance, if rollback or commit needs to take place within the block without effective the transaction outside the block, this type of pragma can be used.

It is used for commiting the particular block only.

for e.g if u declare pragma autonomous transaction

suppose there are two insert statement in your Code and if the two insert fails it will go to the exception part for insertion and in the exception part there is one more insertion and then u commit. it will commit all the insert stmt which should not happen.

for commiting only the exception part pragma autonomous transaction is used.

Very simple meaning is...

1) Pragama means force (like it says to compiler forcible do this operation)

2) It is used for committing the particular block.

3) We can't rollback this Pragama autonomous block

EXCEPTION_INIT:

The most commonly used pragma, this is used to bind a user defined exception to a particular error number.

 Pragama is compile directive .

Example of Autonomous transactions

Suppose you are updating value from table and you don't have update trigger on that table

but still you want to maintain a log entry for this update in seprate table.

You can write a procedure and call that procedure to do this . But you can not use COMMIT in this called procedure  because it will save the entire transaction.

 To avoid this you can delclare this procedure as autonomous transaction procedure so that the execution of this procedure will be treated as totally diff. transaction and you can issue  commit in called procedure without affecting the main transaction.

What is difference in defining constraints at table level and column level:

Table level Constraint

===========================

1. Constraints are defined separately after the columns are defined.

2. While defining constraints at this level constraint name must be provided

3. Not null constraints can't be defined at this level.

4. Composite keys can be defined at this level only.

A table level constraint can see every column in the table.

Column level Constraint

===========================

1. Constraints are defined along with the columns.

2. So constraint name is not required.

3. Not null constraints can be defined at this level only(NOT NULL constraint can only apply to one column).

4. Composite keys can't be defined at this level .

5. Column level constraint is more clear syntactically and more meaningful.

A column level constraint has scope only to the column it is defined on.

The major difference between the two constraint is the scope. So far as the use of constraints (validating proper data) is concerned they are pretty much the same.

how to display 4th maximum sal in enp without using subqueries?

SELECT LEVEL, MAX(Sal)

FROM Emp

WHERE LEVEL = &LevelNo

CONNECT BY PRIOR Sal > Sal

GROUP BY LEVEL;

SELECT *

        FROM (SELECT player_nm, gross_sal,

               RANK () OVER (ORDER BY gross_sal DESC) playerRank

              FROM player)

        WHERE playerRank = 4;

Can we have a Procedure in Specification but not in Package body. If yes then whats the use of it?

Its Not possible..... all the sub programs declared in spec must be implemented in the body. In this case these sub programs will be private for this package only.

What is difference between ROWNUM and ROWID.

Oracle ROWID uniquely identifies where a row resides on disk.  The information in a ROWID gives Oracle everything it needs to find your row, the disk number, the cylinder, block and offset into the block. It actually the physical address.

ROWID can be used to fetch a row from database.

ROWID is permanent.

Whereas,

ROWNUM is a "pseudo-column",

ROWNUM is used to restrict the number of rows in a query.

ROWNUM is temporary.

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.   

If you have to call same piece of code at many places in a package, How do you achieve this without calling the function many times?

 We can use 'Go To' statement in this case.

Execute Immediately:

Execute immediate is used to prepare dynamic SQL. There might be scenario where one has to prepare the SQL statement at run time in a PL/SQL object. Execute immediate requires one SQL statement which can be stored in a varchar field and when this field is used with execute immediate, it runs that SQL statement.

For Example, if we want to fetch emp name for emp table based on the schema we passed and the emp number then we can use below sql statement.

EXECUTE IMMEDIATE 'Select empname into ' || v_empname || ' from ' || v_schema || '.emp where empno = ' || v_empno;

Another important use of it is to use DDL statement in the PL/SQL object, as there is restriction on the use of DDL statement in PL/SQL object. If we want to truncate a table in a PL/SQL object we can use it in the following way:

EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_TEMP';

Bulk Bind or For all:

Bulk Bind is the facilty through which we can INSERT, UPDATE, DELETE or SELECT from records from a table in bulk rather than multiple individual statments..

Suppose you have 10 inserts to be made so you use 10 INSERT into statments to do this.

Instead of this you can you FOR ALL PL/SQL statement to perform all 10 inserts once at a time.

Morever if you have to issue 10 select statements you should rather use BULK COLLECT to perform this operation..

how do i write a function that returns more than 1 records from a table

If we want to return multiple row from a function then we can use ref cursor

function .Example is given below:

create or replace function f1 return sys_refcursor as

r1 sys_refcursor;

begin open r1 for select * from emp;

return(r1);

end;

call the

function as :select f1 from

dual;

How to find the max value in a Sequence?

SELECT MAX_VALUE FROM USER_SEQUENCES

       WHERE SEQUENCE_NAME='NAME_OF_SEQUENCE'

how to export table data into a file in oracle:

spool c:filename.txt

select * from tab where ................;

spool off;

goto c: and check it.i think it is the easyest way to store some data from oracle to flat file...u can save it in any extension like .csv.xls.txt ect............

or

we can use utl_file pkg.

What are the advantages of packages ?

Package have more advantages

Modularity

Easily Application design

Better Performence

Information Hiding

 What are analytical functions and how are they used?

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause.

for example:

SQL> select deptno,

  2  ename,

  3  sal,

  4  sum(sal) over (partition by deptno

  5  order by sal,ename) CumDeptTot,

  6  sum(sal) over (partition by deptno) SalByDept,

  7  sum(sal) over (order by deptno, sal) CumTot,

  8  sum(sal) over () TotSal

  9  from emp

10  order by deptno, sal;

over by clause, order by clause,partition ny clause comes under anyalytical clause.....

 How to create temporary table? Give some example

 Hi guys........

Temporary tables means which tables are hold data temporarly

i mean these holds the data certain time mainly only that session.

exp:-

    create global temporary table employ(emp_id number(10),employ_name varchar2(20)) on commit delete/on commit preserve;

this is brief desc of temporary table

What is difference between DESCRIBE and DESC please define

Describe command is used to describe the data defination of a schema object.

Desc is abbreviation of Describe.

how the execution will be done in exceptions?

There is a lot to explain the execution of exceptions.

Whenever error is occured,the corresponding exception will be raised and handled in Exception block.

But in the case of nested blocks,The exception raised will be caught by handler of Inner block.If inner block does not consist of Handler,It will be propagated to Next outer block and  so on.

If There is an error in declaration section and also in Exception section,That also be propagated to next outer block.

Control never goes back to the block where the exception has occured. It propogates forward, and looks for an exception handler.

In absence of an exception handler, the control goes to the end and program gets terminated.

what is Oracle collections

It is kind of datatype.

Collection is nothing buta convenientway to manipulate many variables at once.

There are 3 types of collection.

1. INDEX BY TABLE

2. NESTED TABLE

3. VARRAYS

How to return multiple records from procedure?E. G. I fired a select query and I want to retun a result to ?

User Ref cursor as out parameter in proc

1.What is difference between procedure and function?

1.procedure may or may not return values whereas function must return value

2. we can call the function in the sql statements whereas we can't call the procedure

The main difference is in Function you can perform only DDL

but in Stored procedure you can perform DDL, DML both

What are the Blocks in PL/SQL?

The PL/SQL Blocks are 2 types

1. Ananymous Block

2. Named Block

1. Ananymous Block means with out any name.

Declare

Begin

<Exec Statmts>

Exception

End.

2. Names Block are Procedures/Functions/Triggers

Differentiate between %type and %rowtype attribute in Oracle PL/AQL programming ?

%type will use for to define at column level.

%rowtype is used to define for one or more than one columns ( like row or record or tuple) means its user define type which hold one or more than one columns along with it. and generally it usedwith Cursor.

What is the alternative of using a 'WHERE CURRENT OF 'Clause in a plsql statement ?

It can be used in cursors

If we want to update the current record (i.e cursor holding the current record) we can use WHERE CURRENT clause

Eg.

UPDATE emp

SET sal=sal*10

FROM emp WHERE CURRENT of "cursor name"

what is the difference between binary_integer and plsql_integer

You must be speaking about PLS_INTEGER. PLS_INTEGER, uses machine-arithmetic unlike BINARY_INTEGER which uses library arithmetic. As such PLS_INTEGER is somewhat faster than BINARY_INTEGER. Also BINARY_INTEGER has subtypes. I don't think PLS_INTEGER has subtypes

1.what is difference between pl/sql table and records?

2. what is the advantages of pl/sql table?

3. what is ref cursor? what is the advantages of ref cursor

1.what is difference between pl/sql table ...

Ans. PL/SQL table is not created permanently in the database and it's only for the session, once the program is complete the table is no longer available so it's used generally when you have some bulk data to store but not permanently.

2. What is the advantages of PL/SQL Tables?

Ans. The performance is faster access/retreival of data is faster and it's not permanent as well.

Difference between Pl/sql tables and record is that in pl/sql tables we can store more than one row

whereas in record we store only one row.

TYPE rec_emp is RECORD( ename   varchar2(10), empno number (5));

rec_emp1 rec_emp

Whereas a pl/sql  Table is

TYPE tab_emp is TABLE OF rec_emp index by binary_integer;

T_emp tab_emp;

What is the difference between NO DATA FOUND and %NOTFOUND ?

NO_DATA_FOUND is system defined exception. This is generated when no rows selected.

%NOTFOUND is attribute which is used with cursor. If cursor returns no row then %NOTFOUND returns true and if returns row then %NOTFOUND is false.

How can I disable a trigger trough SQL prompt.

Alter trigger trigger_name enable/disable;

Left Outer & Right Outer Join

Left outer Join fetch all the records from Left side Table(Only Condition satisfied records from right side table) where as Right Outer Join fetch all the records from Right side Table(Only Condition satisfied records from Left side table).

Insert a record in two tables

    INSERT ALL

        INTO first_table VALUES(value1,value2)

         INTO second_table VALUES(value1,value2)

    

    SELECT * FROM dual;

Display Middle Record

SELECT * FROM scott.EMP WHERE ROWNUM <=(SELECT COUNT(1)/2 FROM scott.EMP)

MINUS

SELECT * FROM scott.EMP WHERE ROWNUM <(SELECT COUNT(1)/2 FROM scott.EMP)

What is the difference between DROP,DELETE and TRUNCATE.. Can anyone tell why we require EXISTS with example.

Delete Command removes all the rows from the table and the deleted record get logged into transaction log which slow down the performance. While truncate table also deletes the records from the table but does not log the deleted record. Records deleted with truncate table can be autocomitted.

Drop table is used to drop the table.

Delete table deletes rows as well as remove the table skeleton. while truncate table only removes rows from the table.

SQL and PLSQL

1.Sql is a declarative language.It tells only what to do.

PL/Sql is a procedural language.It tells what to do and how to do.

2.Sql executes DDL and DML statements .

Pl/Sql executes triggers,functions,procedures and package.

3.sql executes only one statements at a time.

pl/sql executes multiple statements at a time.

4.sql is data oriented language.

pl/sql is object oriented language and supports inheritance etc.

5.We can embed sql statements in a pl/sql.

we cannot embed pl/sql statements in sql

How do I retrieve the last row of a table?

select * from emp where rowid = (select max(rowid) from emp);

Is there any function in oracle to check whether a value is numeric or not. I have a column which contains both numeric and alphanumeric data. I want to fetch only the numeric data from that column. Can anyone tell me how to do it?

we can use the following querry to get the answer

select * from where upper(column_name)=lower(column_name);

here colum_name is name of the column which contains both numeric and

alpha- numeric values.

THE QUERRY WORKS LIKE THIS--

When ever we compare two values, oracle compares them by their ASCII VALUES.

ASCII(UPPER(char)) is not equal to ASCII(LOWER(char))

but for numbers

upper(number) is always equal to lower(number)

what are the advantages of views?

1) To protect some of the columns of a table from other users.ie for security purpose.

2)To hide the complexity of a query.

3)To hide the complexity of calculations.

is there any other option other than desc query to describe the table

SELECT

column_name "Name",

nullable "Null?",

concat(concat(concat(data_type,'('),data_length),')') "Type"

FROM user_tab_columns

WHERE table_name='TABLE_NAME_TO_DESCRIBE';

How can we increase performance in a View?

dont user select */in/write minimal cond

Tune SQL Query

1> always avoid select * from

2> do not use in operator

Matrix query

SELECT count(e.empno) Emp_Count,to_char(e.hiredate,'YYYY') hired_On  

from scott.emp e

group by rollup(to_char(e.hiredate,'YYYY'))

order by 2

Can we have a commit statement inside a trigger? if no why cant we?

Yes ,You can Commit inside the trigger.

But for this you have to make this trigger transaction to be a Independent transaction from its parent transaction, You can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allow you to build the Indepadent(child) Transaction,started by another. Shold be declare in DECLARE section of any subprogram.

CREATE OR REPLACE TRIGGER t_trigger

AFTER INSERT ON t1 FOR EACH ROW

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

 i PLS_INTEGER;

BEGIN

  SELECT COUNT(*)

  INTO i

  FROM t1;

  INSERT INTO t2

  VALUES

  (i);

  COMMIT;

END;

What is CoRelated SubQuery

It is very similar to sub-queries where the parent query is executed based on the values returned by sub-quries. but when comes to co-related subqueries for every instance of parent query subquery is executed and based on the result of sub-query the parent query will display the record as we will have refernce of parent quries in su-queries we call these as corelated subquries.

so, we can define co-related sub query as for every record retrival from the sub query is processed and based on result of process the parent record is displayed.

The main differencre between subquery and co-related subquery is that in subquery child query is executed first n then parent,but in co-related subquery main query is executed first(even though parenthesis are present) and then child query.

Example of co-related subquery

select dname from dept where exists

(select deptno from emp

where dept.deptno=emp.deptno);

select dname from dept where not exists

(select deptno from emp

where dept.deptno=emp.deptno);

Tuning Tips:

1.Don't perform operations on the database object in where clause.If u perform operations on database columns in where clause it will ignore indexed defined columns.

dont'use:

select * from emp

where trunc(hiredate)=trunc(sysdate);

use:

select * from emp

where  hiredate between trunc(sysdate) and trunc(sydate)+.9999;

2.Try not to use having clause  in the select statements.Having clause will filter records only after fecthcing all rows.

using where clause helps reducing overhead  in sorting ,summing etc.

Having clauses should only used when columns with summary operations applied to them are resticted by clause.

dont'use:

select job,avg(sal) from emp

group by job

having job !='manager';

use:

select job,avg(sal) from emp

where job='manager'

group by job;

3.Minimize the number of table lookups (subquery blocks)  in queries, particularly  if statements include subquery selects or multicolumn updates.Avoid using subqueries when a join will do the job.

dont' use:

select * from emp

where deptno =(select deptno from emp where empno=1234)

and sal=(select sal from emp where hiredate=sysdate);

use:

select * from emp

where (deptno,sal ) =(select deptno,sal from emp where empno=1234 and hiredate=sysdate);

4. Consider the alternatives EXists,in and table joins when doing multiple table joins.

if the outer query is big and the inner query  is small  in is more efficient .

if the outer query is small and the inner query  is big  exists is more efficient .

5. try to Avoid using Distinct key word in select statement. The oracle quey will fetch all the rows satisfied by the table joins

and sort,filter out duplicate values.

6. If possible use union all instead of union.Union causes all rows returned by each portion of the union to be sorted and merged and duplicates

to be filtered before the first row is returned. A Union All simply returns all rows including duplicates and does not have to

perform           any sort ,merge,filter.You don't care if duplicates are returned , Union All is much more efficient.

7. Rewrite complex subqueries with temporary tables. oracle created global temporary tables and sql with operator to help

divide and conquer complex sql subqueries . Tuning sql with temporary tables can result in amazing  performance improvements.

8. create index on null values when you are fetching records based on null value.

9. Do not use UPPER (or any functions) on the table columns while joining the database tables, until it is necessary.

Oracle evaluates Non-Indexed conditions linked by AND bottom up

Bad: select * from address where

                        areacode = 972 and

                        type_nr = (select seq_nr from code_table where type = ‘HOME’)

Good: select * from address where

                        type_nr = (select seq_nr from code_table where type = ‘HOME’) and

                        areacode = 972

Oracle evaluates Non-Indexed conditions linked by OR top down

Bad: select * from address where

                        type_nr = (select seq_nr from code_table where type = ‘HOME’) or

                        areacode = 972

Good: select * from address where

                        areacode = 972 or

                        type_nr = (select seq_nr from code_table where type = ‘HOME’)

important under rule based optimizer, and won't hurt under cost based optimizer

order FROM clauses in descending order of table sizes based upon row counts

for example

select * from larger table, smaller table

select * from larger table, smaller table, smallest table

select * from larger table, smaller table, associative table

Make sure everything that can be joined is joined (for 3 or more tables)      

Instead of:

SELECT * FROM t1, t2, t3

WHERE t1.emp_id = t2.emp_id

AND t2.emp_id = t3.emp_id

add

SELECT * FROM t1, t2, t3

WHERE t1.emp_id = t2.emp_id

AND t2.emp_id = t3.emp_id

AND t1.emp_id = t3.temp_id;

Rule #4 : Use FORALL for DML Operations

EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;

FOR i IN l_tab.first .. l_tab.last LOOP

    INSERT INTO forall_test (id, code, description)

    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);

  END LOOP;

  EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;

  FORALL i IN l_tab.first .. l_tab.last

    INSERT INTO forall_test VALUES l_tab(i);

1.         Always use the where clause in your select statement to narrow the number of rows returned.

If we don’t use a where clause, the Oracle performs a full table scan on our table and returns all of the rows.

5.         Use equijoins. It is better if you use with indexed column joins.  For maximum performance when joining two or more tables, the indexes on the columns to be joined should have the same data type.

Choose the join order so you will join fewer rows to tables later in the join order.

l       use smaller table as driving table

l       have first join discard most rows

16.       Try joins rather than sub-queries which result in implicit joins

Bulk Collect In Oracle PL/SQL

 Using Oracle BULK COLLECT technique enables the PL/SQL engine to collect many rows at once and place them in a collection of array.

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

You should have heard that BULK COLLECT together with FORALL can help a PL/SQL to perform better in terms of average execution time.

The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. FORALL does not require a LOOP command.

what is the difference between whereclause and having clause

The difference between 'where' clause and 'having' clause is

1.U can use group function/aggregate functions in having clause but

  u cannot use group function/aggregate functions in where clause.

Where Clause :- Used to filter the records from the table before group by cluse.

Having Clause :- Used to filter the grouped records after group By clause.

Cursor:

A Cursor is basically an area allocated by oracle for executing the Sql statement . Oracle uses an implicit cursor statement for single row subquery and uses explicit cursor for multirow query.

System Global Area(SGA):

The SGA is a shared memory region allocated by the oracle that contatins data and control information for one oracle instance.

It consists data base buffer cache and redo log buffer.

What is "Rollup" And "cube"? How does these different from "groupby"

The ROLLUP is an extension to the GROUP BY caluse.

the cube operator is an addintional switch in the GROUP BY clause in a select statement. the cube operator can be applied to all aggregate fuctions, inluding AVG, SUM , MAX, MIN, and COUNT. it is used to produce results sets that are typically used for cross-tabulat reports.

while ROLLUP produces only a fraction of possible subtotal combinations, cube produces subtotals for all possible combinations of groupings specified in the GROUP BY clause, and a grand total.

difference between decode and case.

1) Decode is a function while Case statement.

2) Case cannot process null while decode can.

Decode will work for only equality operator but case will work for other than equality operator.

we cannot use Decode other than select statement but we can use any where in sql,plsql case statement.

What are the advantages and disadvantages of View?

A) Advantages of views:

1. View the data without storing the data into the object.

2. Restict the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4. Restict the access of a table so that nobody can insert the rows into the table.

Disadvatages:

1.      Can not use DML operations on this.

2.       

2. When table is dropped view becomes inactive.. it depends on the table objects.

3. It is an object, so it occupies space.

Q) What is difference between SUBSTR and INSTR?

substr gives the substring of the position we gives where as

instr gives the the character present in the particular position

Q) What is the Difference between Replace and Translate.

A) Both Replace and Translate are single row functions in Oracle 9i.

The Replace Function replaces single character with multiple characters.

But in Translate Function replaces single character with single character only

how can u create session variable in pakages?

A)        By declaring a vaiable in package specification.

Force view

When we are creating a view for an existing table that is called a normal view.

If there is no table but we need to create a view. Then we will go for this force view. means there is no table also we can create a view by using this force key word.

Q) 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.

why we go for cusor . why don't we go for select statement in a plsql procedure?

suppose i have a table employee having 3 columns like no,name,sal. i have added two more columns like modified_user,modified_date.

write a trigger If any body done modifications for sal i want to update extra two columns.

ans: in this case we will write after update trigger and in execution block again we will write one more update statement.

when we update any info sal that time trigger will fire and inside trigger one more update statement it is there then again trigger will fire.

like this it will execute infinite times. how can you handle this.

What is procedure overloading?

ans: suppose we have created a procedure with two parameters as input and we have used that procedure so many places in a package.

later i have added one more parameter (done modifications) as per business logic. if i have call the same procedure again then it will goto invalid

state. In this situation we can create one more procedure with same name ,different parameters. So, we can make the package is valid.

 This is called procedure overloading .Function overloading is also same like this.....

what is global variable?

what is global temporary table. how to create global temporary table?

i have a procedure with 10 lines of code. when i execute this procedure i got an error after 5 th line.If any error occur i will handle it exception block. if error came after 5th line i want to execute remaining lines also. how to handle it?

ans: if we keep the 5 lines in begin ,end; we will handle it inside begin end.if any error occur in 5 lines we will handle in exception block then it will disturb the remaining code. So, we can execute remaining 5 lines also.

what is inline view?

what is correlated sub query?

how to get last monday of month?

ans:  select last_day(sysdate,'mon') from dual;