Q1. What are the various types of parameter modes in a procedure / function?
Answer. IN, OUT AND INOUT.
Q2. What is a transaction?
Answer. A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
Q3. What is Sysdate? A function or A pseudo column or A system defined variable?
Answer. Sysdate is a function that returns the current date and time set for the operating system on which the database resides, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter.
Q4. Can you pass parameters in packages? How?
Answer. Yes. You can pass parameters to procedures or functions in a package.
Q5. Is following construct allowed in PL/SQL?
Create or replace Procedure abc is
--some variable declarations here
procedure xyz is
--some variable declarations here
begin
--some code here
end xyz;
begin
--some code here
xyz;
--some code here
end abc;
---------------------------------------------------------------
Create or replace Procedure abc is
--some variable declarations here
N1 Number;
Function xyz return number is
--some variable declarations here
begin
--some code here
end xyz;
begin
--some code here
N1 := xyz;
--some code here
end abc;
---------------------------------------------------------------
Create or replace Function abc return number is
--some variable declarations here
procedure xyz is
--some variable declarations here
begin
--some code here
end xyz;
begin
--some code here
xyz;
--some code here
end abc;
---------------------------------------------------------------
Create or replace Function abc return number is
--some variable declarations here
N1 Number;
Function xyz return number is
--some variable declarations here
begin
--some code here
end xyz;
begin
--some code here
N1 := xyz;
--some code here
end abc; Answer. All the above constructs are valid and allowed in Oracle.
Q6. What is PRAGMA?
Answer. PRAGMA refers to a compiler directive or “hint”. It is used to provide an instruction to the compiler. Pragma directives are processed at compile time; they pass necessary information to the compiler; they are not processed at runtime. The 5 types of Pragma directives available in Oracle are listed below:
- PRAGMA AUTONOMOUS_TRANSACTION can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.
- PRAGMA SERIALLY_REUSABLE tells Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory.
- PRAGMA RESTRICT_REFERENCES defines the purity level of a packaged program. After Oracle8i this is no longer required.
- PRAGMA EXCEPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number);
- PRAGMA INLINE (Introduced in Oracle 11g) specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.
Example Syntax:
CREATE OR REPLACE [FUNCTION | PROCEDURE] [NAME] IS
[PRAGMA];
BEGIN
…
…
END;
Q7. What are Exceptions? How many types of Exceptions are there?
Answer. Exceptions are conditions that causes premature termination of a block. There are two types of exceptions.
- Pre-Defined Exceptions by PL/SQL and are associated with specific error codes. Below is the list of some pre-defined exceptions.
| 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 |
- User-Defined Exceptions declared by the users and are rose on deliberate request. (Breaking a condition, etc.)
Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from propagating out of the block and define actions to be performed when exception is raised.
Q8. What is Raise & Raise_application_error?
Answer.
- Raise statement is used to raise a user defined exception.
- Raise_application_error is a procedure of package DBMS_STANDARD which allows issuing a user_defined error messages from stored sub-program or database trigger.
Q9. What is a package? What are the advantages of packages?
Answer. Package is a database object that groups logically related procedures & functions together. The advantages of packages are Modularity, Easier Application Design, Information Hiding, Reusability, Better Performance and polymorphism.
Q10. What are two parts of package?
Answer. Following are the two parts of package:
- 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.
Q11. Which is the correct way to execute / call a procedure?
- CALL procedure_name(‘X’,’Y’);
- EXECUTE Procedure_name(‘X’,’Y’);
- EXEC Procedure_name(‘X’,’Y’);
- begin Procedure_name(‘X’,’Y’); end;
Answer. 2, 3, 4 are the correct ways to execute procedure.
Q12. From the following statements which one is invalid?
A] Drop Package – drops package specification & package body both.
B] Drop Package body – drops package body only.
C] Drop Package – drops package specification only.
Answer. C] Drop Package – drops package specification only.
Q13. How do you make a Function and Procedure as a Private?
Answer. Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.
Q14. What is the importance of SQLCODE and SQLERRM?
Answer.
• SQLCODE returns the error number of for the last encountered error.
• SQLERRM returns the error message for the last encountered error.
Q15. What is an Anonymous block?
Answer. An anonymous block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in DB schema. It is also not compiled and saved in server storage, so it needs to be parsed and executed each time it is run. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables and can prompt the user for input using the SQL*Plus ‘&’ feature as any stored procedure.
Q16. What is Polymorphism or Procedure or Function Overloading?
Answer. Overloading / Polymorphism is a technique by which there can be multiple procedures or functions with same name but different parameters / signature. It can be implemented using packages only.
Q17. What is a package cursor?
Answer. A package cursor is a cursor which is declared in the package specification without an SQL statement. The SQL statement for the cursor is attached dynamically at run time from calling procedures.
Q18. Can Overloaded function have different return types?
Answer. Yes.
Q19. How to execute a PL/SQL procedure from command prompt?
Answer. Use following command to execute the PL/SQL procedure from command prompt.
sqlplus -s scott/tiger@dev1 @Script.sql
Where “Script.sql” is afile containing the calls the PL/SQL procedure.
Q20. How can you send Emails from PL/SQL procedure?
Answer. UTL_Mail can be used to send Emails.
CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;
BEGIN
UTL_MAILsend_attach_varchar2(
sender => 'sender@senderdomain.com',
recipients =>
'receiver1@receiverdomain.com, receiver2@receiverdomain.com',
cc => 'receiver3@receiverdomain.com',
bcc => 'bccreceiver1@bccreceiverdomain.com',
subject => 'Test mail using UTL_MAIL',
message => 'This is a test mail!',
attachment => 'text/plain; charset=us-ascii',
att_filename => 'attachment.txt');
END;
UTL_SMTP can also be used for sending Emails. In case of any discrepancies or doubts do contact me.
Best of Luck Friends.
TechOraFAQ 
Good work. Very helpful. Appreciate the efforts of the author.
LikeLike
Good to have such source of knowledge to prepare for interview
LikeLike
Wow good piece of information.
LikeLike
Good work. Keep going.
LikeLike