PL/SQL Interview Questions

For Basic SQL:

• How do you convert a date to a string? To_char. A bonus would be that they always include a format mask.

• What is an aggregate function? I’m looking for “grouping”, sums or counts, etc.

• What is an interval? Specifies a period of time.

• What is a nested subquery? A subquery in a where clause.

• What is the dual table? A single row table provided by oracle for selecting values and expressions.

For Basic PL/SQL:

• Describe the block structure of PLSQL. Declaration, Begin, exception, end.

• What is an anonymous block? Unnamed PL/SQL block.

• What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.

• What is the difference between an explicit cursor and a select into. You might get something about performance but that’s a myth. An explicit cursor is just more typing. A cursor for loop would be used to return more than a single row.

• Why would you choose to use a package versus straight procedures and functions? I look for maintenance, grouping logical functionality, dependency management, etc. I want to believe that they believe using packages is a “good thing”.

For Advanced SQL:

• What is the difference between an aggregate and an analytic function? I’m looking for them knowing that a sum aggregate (or any other aggregate function) will return one row for a group and a sum analytic will return one result for each row in the group. If they mention the “Window”, they get a bonus point.

• How do you create a hierarchical query? Connect by.

• How would you generate XML from a query? The answer here is “A lot of different ways”. They should know that there are SQL functions: XMLELEMENT, XMLFOREST, etc and PL/SQL functions: DBMS_XMLGEN, DBMS_XMLQUERY, etc.

• What do you need before implementing a member function? You need to create a type.

• How do you tune a query? I’m looking for a discussion of autotrace and/or explain plan. Ask them what they’re looking for in a plan. This should not be a single sentence. Look for a comfort level.

For Somewhat Advanced PL/SQL:

• What is the default value of a boolean? NULL. This is somewhat tricky but apparently there are languages that default boolean to false. A PL/SQL developer needs to know all variables default to NULL.

• Why is using implicit conversions a poor programming practice? For dates, you must ASSUME that the default date format will always be the same (and it won’t be). In some cases, implicit conversion is slower. I want to feel like they don’t believe writing to_char or to_number is more work than it’s worth. BTW, this also applies to SQL.

• How can you tell if an UPDATE updated no rows? SQL%NOTFOUND.

• How can you tell if a SELECT returned no rows. NO_DATA_FOUND exception.

• How do you run Native Dynamic SQL? Execute immediate.

• What is an autonomous transaction? Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.

1.  How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;
Related Articles
2.  Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
3.  What command would you use to encrypt a PL/SQL application?
WRAP
4.  Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
25.  Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
26.  Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
27.  Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
28.  Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
29.  When a user process fails, what background process cleans up after it?
PMON
30.  What background process refreshes materialized views?
The Job Queue Processes.
31.  How would you determine what sessions are connected and what resources they are waiting for?
Use of V$SESSION and V$SESSION_WAIT
32.  Describe what redo logs are.
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.
33.  How would you force a log switch?
ALTER SYSTEM SWITCH LOGFILE;
34.  Give two methods you could use to determine what DDL changes have been made.
You could use Logminer or Streams
35.  What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
36.  What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
37.  Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
38.  When creating a user, what permissions must you grant to allow them to connect to the database?
Grant the CONNECT to the user.
39.  How do you add a data file to a tablespace?
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>
40.  How do you resize a data file?
ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;
41.  What view would you use to look at the size of a data file?
DBA_DATA_FILES
42.  What view would you use to determine free space in a tablespace?
DBA_FREE_SPACE
43.  How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
44.  How can you rebuild an index?
ALTER INDEX <index_name> REBUILD;
45.  Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
46.  You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS
47.  How can you gather statistics on a table?
The ANALYZE command.
48.  How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
49.  What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
50.  Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
Technical – UNIX
Every DBA should know something about the operating system that the database will be running on. The questions here are related to UNIX but you should equally be able to answer questions related to common Windows environments.
1.  How do you list the files in an UNIX directory while also showing hidden files?
ls –ltra
2.  How do you execute a UNIX command in the background?
Use the “&”
3.  What UNIX command will control the default file permissions when files are created?
Umask
4.  Explain the read, write, and execute permissions on a UNIX directory.
Read allows you to see and list the directory contents.
Write allows you to create, edit and delete files and subdirectories in the directory.
Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.
5.  the difference between a soft link and a hard link?
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.
6.  Give the command to display space usage on the UNIX file system.
df –lk
7.  Explain iostat, vmstat and netstat.
Iostat reports on terminal, disk and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
8.  How would you change all occurrences of a value using VI?
Use :%s/<old>/<new>/g
9.  Give two UNIX kernel parameters that effect an Oracle install
SHMMAX & SHMMNI
10.  Briefly, how do you install Oracle software on UNIX.
Basically, set up disks, kernel parameters, and run orainst.
Technical Interview Round
Tell me about yourself?
Introduces himself, tells about about current project, roles and responsibilities, tells about previous work experiences and tells about his technical strengths.
What is a partition in Oracle?
Decomposing a table into many parts for better performance and manageability. Types of Partition: 1. List, 2. Range, 3. Hash, 4. Composite. Read more about Oracle partition
What is a trigger in SQL?
Trigger is a SQL block executed implicitly when one or more DML event occurs.Read more about triggers.
What are the differences between a procedure and function in SQL?
Procedure may or may not return a value through parameters, where as a function always return a value. There are three types of parameters allowed in procedure: IN, OUT and IN OUT, where as there is only one type of parameter allowed in a function: IN. Read more differences between functions and procedures
What is an Explain Plan?
Explain Plan on wikipedia, Explain Plan in Oracle
Write a SQL query to find second maximum salary in each department of a Company.
select department, max(salary) from employee where salary < (select max(sal) from employee) group by department_no
Write a SQL query to delete all duplicate records in a table
delete from employee emp where rowid < (select max(rowid) from employee e1 where emp.column=e1.column)
What is mutating table?
A table is called mutating table if it is affected by insert, update or delete statement or when it is affected by a delete cascade statement. Read more about mutating tables in Oracle
Project Manager Interview Round:
Tell me about yourself?
Again, tells his name, previous job experience and current project, roles and responsibilities.
Which technologies have you worked on in your previous companies?
I have worked on Oracle PL/SQL and Cognos. Goes on telling about the technologies he worked on in previous companies.
Would you like to work on Business Objects(BO)?
Yes, I would like to work. Please train me on Cognos, if possible.
Tell me the difference between Business Objects(BO) and Cognos?
Cognos is an IBM tool, it is a MOLAP tool. Business Objects is a SAP tool, it is a ROLAP tool.
HR Discussion Round:
Why did you want to change your current job?
Firstly current job location is far from my home. It takes me two hours to go to office and two hours to come home. Secondly, I don’t get enough exposure in Cognos as I work only with List reports. I am looking for a better opportunities. (Rants in his mind, “I want a better pay!!”)
Why do you think should you be selected for this position?
Gives a bombastic textbook answer and impresses the HR.
How much salary are you expecting?
Six and a half hundred thousand rupees per annum (6,50,000 Rs or 13,380$ USD approx)
HR: Well that is too high, you will have to quote lesser.
How about Six hundred thousand Rupees per annum? (6,00,000 Rs or 12,351$ USD approx)
HR: I’m afraid I can not offer you more than five and half hundred thousand Rupees (11,321$ USD approx) for your profile.
PL/SQL interview qiuestions
By admin | May 8, 2004
Which of the following statements is true about implicit cursors?
Implicit cursors are used for SQL statements that are not named.
Developers should use implicit cursors with great care.
Implicit cursors are used in cursor for loops to handle data processing.
Implicit cursors are no longer a feature in Oracle.
Which of the following is not a feature of a cursor FOR loop?
Record type declaration.
Opening and parsing of SQL statements.
Fetches records from cursor.
Requires exit condition to be defined.
A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?
Use employee.lname%type.
Use employee.lname%rowtype.
Look up datatype for EMPLOYEE column on LASTNAME table and use that.
Declare it to be type LONG.
Which three of the following are implicit cursor attributes?
%found
%too_many_rows
%notfound
%rowcount
%rowtype
If left out, which of the following would cause an infinite loop to occur in a simple loop?
LOOP
END LOOP
IF-THEN
EXIT
Which line in the following statement will produce an error?
cursor action_cursor is
select name, rate, action
into action_record
from action_table;
There are no errors in this statement.
The command used to open a CURSOR FOR loop is
open
fetch
parse
None, cursor for loops handle cursor opening implicitly.
What happens when rows are found using a FETCH statement
It causes the cursor to close
It causes the cursor to open
It loads the current row values into variables
It creates the variables to hold the current row values
Read the following code:
CREATE OR REPLACE PROCEDURE find_cpt
(v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
IS
BEGIN
IF v_cost_per_ticket  > 8.5 THEN
SELECT  cost_per_ticket
INTO            v_cost_per_ticket
FROM            gross_receipt
WHERE   movie_id = v_movie_id;
END IF;
END;
Which mode should be used for V_COST_PER_TICKET?
IN
OUT
RETURN
IN OUT
Read the following code:
CREATE OR REPLACE TRIGGER update_show_gross
{trigger information}
BEGIN
{additional code}
END;
The trigger code should only execute when the column, COST_PER_TICKET, is greater than $3. Which trigger information will you add?
WHEN (new.cost_per_ticket > 3.75)
WHEN (:new.cost_per_ticket > 3.75
WHERE (new.cost_per_ticket > 3.75)
WHERE (:new.cost_per_ticket > 3.75)
What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
Only one
All that apply
All referenced
None
For which trigger timing can you reference the NEW and OLD qualifiers?
Statement and Row
Statement only
Row only
Oracle Forms trigger
Read the following code:
CREATE OR REPLACE FUNCTION get_budget(v_studio_id IN NUMBER)
RETURN number IS
v_yearly_budget NUMBER;
BEGIN
SELECT  yearly_budget
INTO            v_yearly_budget
FROM            studio
WHERE   id = v_studio_id;
RETURN v_yearly_budget;
END;
Which set of statements will successfully invoke this function within SQL*Plus?
VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := GET_BUDGET(11);
VARIABLE g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
VARIABLE :g_yearly_budget NUMBER
EXECUTE :g_yearly_budget := GET_BUDGET(11);
VARIABLE g_yearly_budget NUMBER
:g_yearly_budget := GET_BUDGET(11);
CREATE OR REPLACE PROCEDURE update_theater
(v_name IN VARCHAR v_theater_id IN NUMBER) IS
BEGIN
UPDATE  theater
SET             name = v_name
WHERE   id = v_theater_id;
END update_theater;
When invoking this procedure, you encounter the error:
ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.
How should you modify the function to handle this error?
An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
Handle the error in EXCEPTION section by referencing the error code directly.
Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
Read the following code:
CREATE OR REPLACE PROCEDURE calculate_budget IS
v_budget        studio.yearly_budget%TYPE;
BEGIN
v_budget := get_budget(11);
IF v_budget < 30000
THEN
set_budget(11,30000000);
END IF;
END;
You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
Only the CALCULATE_BUDGET procedure needs to be recompiled.
All three procedures are marked invalid and must be recompiled.
Which procedure can be used to create a customized error message?
RAISE_ERROR
SQLERRM
RAISE_APPLICATION_ERROR
RAISE_SERVER_ERROR
The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
ALTER TRIGGER check_theater ENABLE;
ENABLE TRIGGER check_theater;
ALTER TABLE check_theater ENABLE check_theater;
ENABLE check_theater;
Examine this database trigger
CREATE OR REPLACE TRIGGER prevent_gross_modification
{additional trigger information}
BEGIN
IF TO_CHAR(sysdate, DY) = MON
THEN
RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
END IF;
END;
This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?
BEFORE DELETE ON gross_receipt
AFTER DELETE ON gross_receipt
BEFORE (gross_receipt DELETE)
FOR EACH ROW DELETED FROM gross_receipt
Examine this function:
CREATE OR REPLACE FUNCTION set_budget
(v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS
BEGIN
UPDATE  studio
SET             yearly_budget = v_new_budget
WHERE   id = v_studio_id;
IF SQL%FOUND THEN
RETURN TRUEl;
ELSE
RETURN FALSE;
END IF;
COMMIT;
END;
Which code must be added to successfully compile this function?
Add RETURN right before the IS keyword.
Add RETURN number right before the IS keyword.
Add RETURN boolean right after the IS keyword.
Add RETURN boolean right before the IS keyword.
Under which circumstance must you recompile the package body after recompiling the package specification?
Altering the argument list of one of the package constructs
Any change made to one of the package constructs
Any SQL statement change made to one of the package constructs
Removing a local variable from the DECLARE section of one of the package constructs
Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
When the transaction is committed
During the data manipulation statement
When an Oracle supplied package references the trigger
During a data manipulation statement and when the transaction is committed
Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
DBMS_DISPLAY
DBMS_OUTPUT
DBMS_LIST
DBMS_DESCRIBE
What occurs if a procedure or function terminates with failure without being handled?
Any DML statements issued by the construct are still pending and can be committed or rolled back.
Any DML statements issued by the construct are committed
Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
Examine this code
BEGIN
theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;
END;
For this code to be successful, what must be true?
Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
DBMS_DDL
DBMS_DML
DBMS_SYN
DBMS_SQL
Can you briefly share your experience in the field of PLSQL as to how long have you been using it and the different challenges you have faced?
What according to you separates a function from a procedure and from an anonymous PLSQL block?
Why do you need to use a DECLARE statement?
What is the significance of the SQLERRM and when is this function used?
What are triggers and name the different triggers used by a PLSQL developer?
Explain the different forms of cursors that you are aware of and what are the differences between them?
If I use a CREATE TABLE statement after an INSERT statement and then follow it up by a ROLLBACK statement, will I be able to insert rows?
Why does one use a & operator in PLSQL and how is it different from using a && operator?
How is a unique key separate from a primary key while using a PLSQL environment?
1. What is PL/SQL ?
Ans. 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 ?
Ans. PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
3. What are the components of a PL/SQL block ?
Ans. A set of related declarations and procedural statements is called block.
4. What are the components of a PL/SQL Block ?
Ans. Declarative part, Executable part and Exception part.
Datatypes PL/SQL
5. What are the datatypes a available in PL/SQL ?
Ans. Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
6. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
Ans. % 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.
7. What is difference between % ROWTYPE and TYPE RECORD ?
Ans. % 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.
8. What is PL/SQL table ?
Ans. 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.
9. What is a cursor ? Why Cursor is required ?
Ans. 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.
10. Explain the two type of Cursors ?
There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
11. What are the PL/SQL Statements used in cursor processing ?
Ans. DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
12. What are the cursor attributes used in PL/SQL ?
Ans. %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 fetched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.
13. What is a cursor for loop ?
Ans. 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;
14. What will happen after commit statement ?
Ans. 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.
15. Explain the usage of WHERE CURRENT OF clause in cursors ?
Ans. WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor.
16. What is a database trigger ? Name some usages of database trigger ?
Ans. Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.
17. How many types of database triggers can be specified on a table ? What are they ?
Ans. 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.
18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
Ans.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.
19. What are two virtual tables available during database trigger execution ?
Ans. The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.
20. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Ans. Mutation of table occurs.
21. What is an Exception ? What are types of Exception ?
Ans. Exception is the error handling part of PL/SQL block. 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.
22. What is Pragma EXECPTION_INIT ? Explain the usage ?
Ans. The PRAGMA EXECPTION_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)
23. What is Raise_application_error ?
Ans. 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.
24. What are the return values of functions SQLCODE and SQLERRM ?
Ans. SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.
25. Where the Pre_defined_exceptions are stored ?
Ans. In the standard package.
26. What is a stored procedure ?
Ans. A stored procedure is a sequence of statements that perform specific function.
27. What is difference between a PROCEDURE & FUNCTION ?
Ans. A FUNCTION always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
28. What are advantages fo Stored Procedures ?
Ans. Extensibility, Modularity, Reusability, Maintainability and one time compilation.
29. What are the modes of parameters that can be passed to a procedure ?
Ans. IN, OUT, IN-OUT parameters.
30. What are the two parts of a procedure ?
Ans. Procedure Specification and Procedure Body.
31. Give the structure of the procedure ?
Ans. PROCEDURE name (parameter list…..)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
32. Give the structure of the function ?
Ans. FUNCTION name (argument list …..) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;
33. Explain how procedures and functions are called in a PL/SQL block ?
Ans. Function is called as part of an expression.
sal := calculate_sal (‘a822′);
procedure is called as a PL/SQL statement
calculate_bonus (‘A822′);
34. What is Overloading of procedures ?
Ans. The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
35. What is a package ? What are the advantages of packages ?
Ans. Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Application Design, Information. Hiding,. reusability and Better Performance.
36.What are two parts of package ?
Ans. 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.
37. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
Ans. 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.
38. Name the tables where characteristics of Package, procedure and functions are stored ?
Ans. User_objects, User_Source and User_error