CoolInterview.com - World's Largest Collection of Interview Questions & Answers, FAQs, queries, sample papers, exam papers, dumps, what, why, how, where, when questions
Our Services
Get 9,000 Interview Questions & Answers in an eBook.




Get it now !!
Send your Resume to 6000 Companies
PL-SQL Interview Questions & Answers - Learning Mode
COOLINTERVIEW.COM ORACLE INTERVIEW QUESTIONS PL-SQL INTERVIEW QUESTIONS QUESTIONS & ANSWERS - LEARNING MODE

PL-SQL Interview Questions & Answers - Learning Mode

PL/SQL is Oracle Corporation's proprietary procedural extension to the SQL database language. Some other SQL database management systems offer similar extensions to the SQL language.

Try PL-SQL Interview Questions & Answers - Exam Mode

1 2 3 4 5 Next

Sort By : Latest First | Oldest First | By Rating

PL-SQL Interview Questions & Answers - Learning Mode
Try PL-SQL Interview Questions & Answers - Exam Mode
Question: What is difference between a PROCEDURE & FUNCTION ?

Answer: 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. Source: CoolInterview.com
Question: State the difference between implicit and explicit cursor's.



Answer: Implicit Cursor are declared and used by the oracle internally. whereas the explicit cursors are declared and used by the user. more over implicitly cursors are no need to declare oracle creates and process and closes autometically. the explicit cursor should be declared and closed by the user. Source: CoolInterview.com
Question: What is difference between stored procedures and application procedures,stored function and application function?



Answer: Stored procedures are sub programs stored in the database and can be called & execute multiple times where in an application procedure is the one being used for a particular application same is the way for function
Source: CoolInterview.com
Question: How we can create a table in PL/SQL block. insert records into it??? is it possible by some procedure or function?? please give example...



Answer: CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)

AS

l_stmt VARCHAR2(200);

BEGIN

DBMS_OUTPUT.put_line('STARTING ');

l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';

execute IMMEDIATE l_stmt;

DBMS_OUTPUT.put_line('end ');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);

END;
Source: CoolInterview.com
Question: Explian rowid,rownum?What are the pseduocolumns we have?



Answer: ROWID - Hexa decimal number each and every row having unique.Used in searching

ROWNUM - It is a integer number also unique for sorting Normally TOP N Analysys.

Other Psudo Column are

NEXTVAL,CURRVAL Of sequence are some exampls

Source: CoolInterview.com
Question: State the advantage and disadvantage of Cursor?



Answer: Advantage :

In pl/sql if you want perform some actions more than one records you should user these cursors only. bye using these cursors you process the query records. you can easily move the records and you can exit from procedure when you required by using cursor attributes.

disadvantage:

using implicit/explicit cursors are depended by sutiation. if the result set is les than 50 or 100 records it is better to go for implicit cursors. if the result set is large then you sho Source: CoolInterview.com
Question: What is Raise_application_error ?

Answer: Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database
trigger. Source: CoolInterview.com
Question: Explain the usage of WHERE CURRENT OF clause in cursors ?

Answer: WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers Source: CoolInterview.com
Question: Name the tables where characteristics of Package, procedure and functions are stored ?

Answer: User_objects, User_Source and User_error. Source: CoolInterview.com
Question: What will the Output for this Coding?
Declare
Cursor c1 is select * from emp FORUPDATE;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
Commit;
Fetch c1 in to Z;
end;




Answer: By declaring this cursor we can update the table emp through z,means wo not need to write table name for updation,it may be only by "z". Source: CoolInterview.com
Question: 1)What is the starting "oracle error number"?
2)What is meant by forward declaration in functions?



Answer: One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also Source: CoolInterview.com
Question: What is trigger,cursor,functions in pl-sql and we need sample programs about it?

Answer: Trigger is an event driven PL/SQL block. Event may be any DML transaction.

Cursor is a stored select statement for that current session. It will not be stored in the database, it is a logical component.

Function is a set of PL/SQL statements or a PL/SQL block, which performs an operation and must return a value.

Source: CoolInterview.com
Question: How to debug the procedure ?

Answer: You can use DBMS_OUTPUT oracle supplied package or DBMS_DEBUG pasckage. Source: CoolInterview.com
Question: What is ref cursor?

Answer: In PL/SQL ,pointer has a datatype REF X where
REF-Reference
X-class of objects
Cursor Variables has a datatype REF-CURSOR
where Cursor Varibales are like pointers which hold the memory location of some item instead of the item itself. Source: CoolInterview.com
Question: What is pl/sql?what are the advantages of pl/sql?

Answer: PL/SQL(a product of Oracle) is the 'programming language' extension of sql.
It is a full-fledged language although it is specially designed for database centric activities.
Source: CoolInterview.com
Question: Can Commit,Rollback ,Savepoint be used in Database Triggers?If yes than HOW? If no Why?With Reasons

Answer: we cannot commit inside a trigger.

As we all know that when a dml is complete one can issue a commit.

A trigger if created is fired before the dml completes.

so we cannot commit intermediately. Source: CoolInterview.com
Question: How many types of database triggers can be specified on a table ? What are they ?

Answer: Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned Boolean value. Source: CoolInterview.com
Question: Explain how procedures and functions are called in a PL/SQL block ?

Answer: Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822'); Source: CoolInterview.com
Question: What will happen after commit statement ?

Answer: Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;
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. Source: CoolInterview.com
Question: Where the Pre_defined_exceptions are stored ?

Answer: In the standard package.
Procedures, Functions & Packages ; Source: CoolInterview.com

1 2 3 4 5 Next



India News Network
Latest 20 Questions
Payment of time- barred debt is: (a) Valid (b) Void (c) Illegal (d) Voidable
Consideration is defined in the Indian Contract Act,1872 in: (a) Section 2(f) (b) Section 2(e) (c) Section 2(g) (d) Section 2(d)
Which of the following is not an exception to the rule, "No consideration, No contract": (a) Natural love and affection (b) Compensation for involuntary services (c) Completed gift (d) Agency
Consideration must move at the desire of: (a) The promisor (b) The promisee (c) The promisor or any other party (d) Both the promisor and the promisee
An offer which is open for acceptance over a period of time is: (a) Cross Offer (b) Counter Offer (c) Standing Offer (d) Implied Offer
Specific offer can be communicated to__________ (a) All the parties of contract (b) General public in universe (c) Specific person (d) None of the above
_________ amounts to rejection of the original offer. (a) Cross offer (b) Special offer (c) Standing offer (d) Counter offer
A advertises to sell his old car by advertising in a newspaper. This offer is caleed: (a) General Offer (b) Special Offer (c) Continuing Offer (d) None of the above
In case a counter offer is made, the original offer stands: (a) Rejected (b) Accepted automatically (c) Accepted subject to certain modifications and variations (d) None of the above
In case of unenforceable contract having some technical defect, parties (a) Can sue upon it (b) Cannot sue upon it (c) Should consider it to be illegal (d) None of the above
If entire specified goods is perished before entering into contract of sale, the contract is (a) Valid (b) Void (c) Voidable (d) Cancelled
______________ contracts are also caled contracts with executed consideration. (a) Unilateral (b) Completed (c) Bilateral (d) Executory
A offers B to supply books @ Rs 100 each but B accepts the same with condition of 10% discount. This is a case of (a) Counter Offer (b) Cross Offer (c) Specific Offer (d) General Offer
_____________ is a game of chance. (a) Conditional Contract (b) Contingent Contract (c) Wagering Contract (d) Quasi Contract
There is no binding contract in case of _______ as one's offer cannot be constructed as acceptance (a) Cross Offer (b) Standing Offer (c) Counter Offer (d) Special Offer
An offer is made with an intention to have negotiation from other party. This type of offer is: (a) Invitation to offer (b) Valid offer (c) Voidable (d) None of the above
When an offer is made to the world at large, it is ____________ offer. (a) Counter (b) Special (c) General (d) None of the above
Implied contract even if not in writing or express words is perfectly _______________ if all the conditions are satisfied:- (a) Void (b) Voidable (c) Valid (d) Illegal
A specific offer can be accepted by ___________. (a) Any person (b) Any friend to offeror (c) The person to whom it is made (d) Any friend of offeree
An agreement toput a fire on a person's car is a ______: (a) Legal (b) Voidable (c) Valid (d) Illegal
Cache = 0.03125 Seconds