Oracle PL/SQL Programming Concepts

PL/SQL is Oracle's Procedural Language extension to SQL. It is commonly used to write data-centric programs to manipulate data in an Oracle database. Here's a basic primer to help beginners get started.
Chapter Objectives

In this Chapter, you will learn about:

    *

      The Nature of a Computer Program and Programming Languages
    *

      Good Programming Practices

Computers play a large role in the modern world. No doubt you realize how crucial they have become to running any business today; they have also become one of the sources of entertainment in our lives. You probably use computers for your everyday tasks as well, such as sending e-mail, paying bills, shopping, reading the latest news on the Internet, or even playing games.

A computer is a sophisticated device. However, it is important to remember that it is still only a device and cannot think on its own. In order to be useful, a computer needs instructions to follow. Facilities such as programming languages allow programmers to provide computers with a list of instructions called programs. These programs tell a computer what actions to perform. As a result, programming languages and computer programs play an important role in today's technology.
Lab 1.1 The Nature of a Computer Program and Programming Languages
Lab Objectives

After this Lab, you will be able to:

    *

      Understand the Nature of Computer Programs and Programming Languages
    *

      Understand the Differences between Interpreted and Compiled Languages

A computer needs instructions to follow because it cannot think on its own. For instance, when playing a game of solitaire you must choose which card to move. Each time a card is moved, a set of instructions has been executed to carry out the move. These instructions compose only a small part of the solitaire program. This program comprises many more instructions that allow a user to perform actions, such as beginning or ending a game, selecting a card's color, and so forth. Therefore, a computer program comprises instructions that direct the actions of the computer. In essence, a program plays the role of guide for a computer. It tells the computer what steps in what order should be taken to complete a certain task successfully.

Computer programs are created with the help of programming languages. A programming language is a set of instructions consisting of rules, syntax, numerical and logical operators, and utility functions. Programmers can use programming languages to create a computer program. There are many different programming languages available today. However, all programming languages can be divided into three major groups: machine languages, assembly languages, and high-level languages.

TIP

Words such as statement or command are often used when talking about instructions issued by a program to a computer. These terms are interchangeable.
Machine Languages

Machine language is the native language of a particular computer because it is defined by the hardware of the computer. Each instruction or command is a collection of zeros and ones. As a result, machine language is the hardest language for a person to understand, but it is the only language understood by the computer. All other programming languages must be translated into machine language. Consider the following example of the commands issued in the machine language.
For Example

Consider the mathematical notation X = X + 1. In programming, this notation reads the value of the variable is incremented by one. In the following example, you are incrementing the value of the variable by 1 using machine language specific to an Intel processor.

1010 0001 1110 0110 0000 0001
0000 0011 0000 0110 0000 0001 0000 0000
1010 0011 1110 0110 0000 0001

Assembly Languages

Assembly language uses English-like abbreviations to represent operations performed on the data. A computer cannot understand assembly language directly. A program written in assembly language must be translated into machine language with the help of the special program called an assembler. Consider the following example of the commands issued in assembly language.
For Example

In this example, you are increasing the value of the variable by 1 as well. This example is also specific to an Intel processor.

MOV AX, [01E6]
ADD AX, 0001
MOV [01E6], AX

High-Level Languages

A high-level language uses English-like instructions and common mathematical notations. High-level languages allow programmers to perform complicated calculations with a single instruction. However, it is easier to read and understand than machine and assembly languages, and it is not as time-consuming to create a program in high-level language as it is in machine or assembly language.
For Example

variable := variable + 1;

This example shows the simple mathematical operation of addition. This instruction can be easily understood by anyone without programming experience and with basic mathematical knowledge.
Differences between Interpreted and Compiled Languages

High-level languages can be divided into two groups: interpreted and compiled. Interpreted languages are translated into machine language with the help of another program called an interpreter. The interpreter translates each statement in the program into machine language and executes it immediately before the next statement is examined.

A compiled language is translated into machine language with the help of the program called a compiler. Compilers translate English-like statements into machine language. However, all of the statements must be translated before a program can be executed. The compiled version of the program is sometimes referred to as an executable.

An interpreted program must be translated into machine language every time it is run. A compiled program is translated into machine language only once when it is compiled. The compiled version of the program can then be executed as many times as needed.

SOURCE:http://www.informit.com/articles/article.aspx?p=30663

What is PL/SQL?

What is PL/SQL?

PL/SQL stands for Procedural Language extension of SQL.

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.


The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

     
About This PL SQL Programming tutorial

This Oracle PL SQL tutorial teaches you the basics of programming in PL/SQL with appropriate examples. You can use this tutorial as your guide or reference while programming with PL SQL. I will be making this Oracle PL SQL programming tutorial as often as possible to share my knowledge in PL SQL and help you in learning PL SQL better.

Even though the programming concepts discussed in this tutorial is specific to Oracle PL SQL. The concepts like cursors, functions and stored procedures can be used in other database systems like Sybase , Microsoft SQL server etc, with some change in syntax. This tutorial will be growing regularly; let us know if any topic related to PL SQL needs to be added or you can also share your knowledge on PL SQL with us. Lets share our knowledge about PL SQL with others.
A Simple PL/SQL Block:

Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

A PL/SQL Block consists of three sections:

    * The Declaration section (optional).
    * The Execution section (mandatory).
    * The Exception (or Error) Handling section (optional).

Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

This is how a sample PL/SQL Block looks.

DECLARE
     Variable declaration
BEGIN
     Program Execution
EXCEPTION
     Exception handling
END;

SOURCE:http://www.plsql-tutorial.com/

Oracle Programming in PL/SQL

Programming in PL/SQL

Databases have been in use long before the personal computer arrived on the scene.  IBM developed the Structured Query Language standard (dubbed SQL, and pronounced “See-Quel”) over 30 years ago as a way to retrieve data from their new “relational” database.  A decade later, Oracle released the first commercial relational database that used SQL, and SQL has become the de-facto query language for the vast majority of popular database products.

For the complete story, we recommend the book “Easy Oracle PL/SQL Programming”.  Once you have mastered basic SQL you are ready for the advanced book “Oracle PL/SQL Tuning” by Dr. Timothy Hall.

Even though SQL is the standard language for interacting with any modern database, it does not mean that SQL is without limitations.  If we want to retrieve a set of records from the database and modify them according to a set of rules, (updating some and returning to the calling program others), we can’t do this with a single SQL call to the database.  Complex processing requires the ability to compare values (often called Boolean logic) and implement programmatic flow control.   In other words, some type of programming language was required to process the returned rows and implement the program rules.  To achieve this capability, Oracle introduced the Procedural Language extensions to the Structured Query Language or PL/SQL.

Oracle PL/SQL was based on the ADA programming language which was developed by the Department of Defense to be used on mission critical systems.  Although not a “sexy” language like Java or C, ADA is still being develop and used for applications such as aircraft control systems.  ADA is a highly structured, strongly typed programming language that uses natural language constructs to make it easy to understand.  The PL/SQL language inherited these attributes making PL/SQL easier to read and maintain than more cryptic languages such as C.  For example, below are two loops, one in PL/SQL and the other in a programming language called C.
for x in v_start..v_finish --PL/SQL
loop
   v_int := v_int +1;
end loop;


As opposed to:

for (x = str; x< fin; x++) {i++}  --C

As we see, the PL/SQL statement is more verbose but also easier to understand.

PL/SQL is also portable within the Oracle database family and runs on all supported Oracle platforms including Oracle10g grid database. Even more important is platform independence, where programs developed in PL/SQL on a Windows Oracle database will load and run in a UNIX Oracle database.  With each release of the Oracle database, Oracle Corporation enhances the capabilities and performance of PL/SQL.  Remember, PL/SQL is an Oracle only product, and no other database management system will run PL/SQL.

Unlike other languages that execute externally, PL/SQL executes inside the database.  This means that you can take advantage of PL/SQL’s exceptional ability to manipulate data in the database without paying the network penalty of retrieving the data out of the database and them updating it back to the database.  Because PL/SQL runs inside the database it takes advantage of the capabilities and capacity of the database server.

Traditionally, PL/SQL has been a compiled/interpreted language similar to Java.   When PL/SQL code is loaded into the database it is compiled into an intermediate form, similar to the way Java is compiled into byte-code.  This intermediate code is portable across Oracle databases.  Later versions of Oracle (Oracle9i and 10g) will compile PL/SQL into native code for over 60 hardware platforms.  This natively compiled code runs more efficiently, but it loses the ability to move to other Oracle databases without recompiling.

Lastly, placing the code that interacts with the database in PL/SQL makes better use of the database resources.  PL/SQL packages are loaded as a package so as your program calls for data, the procedures and functions are already in cached memory.  Using PL/SQL will also result in your application using bind variables.  The only way not to use bind variables in PL/SQL is to implement dynamic SQL (discussed in Chapter 5).  The Database Administrator (DBA) also benefits when developers place their SQL inside PL/SQL because they have access to the statements for tuning.  For example the DBA can tune the SQL (adding hints, reordering the WHERE clause) without impacting the existing application.  Placing SQL inside PL/SQL also allows the code to be protected by the recovery capabilities of the Oracle database.

PL/SQL is the most common language for Oracle the world, and developers are realizing the benefits in both application performance and database performance by implementing the database interaction in PL/SQL.  There are even websites built entirely using PL/SQL.  For example, Oracle’s HTML-DB product is installed in the database and consists primarily of PL/SQL packages and Java scripts.
PL/SQL Basic Structure

Like the ADA programming language, PL/SQL is based on blocks, and   PL/SQL provides a number of different blocks for different uses.  The characteristics of a block include:

* A block begins with a declarative section where variables are defined.

* This is followed by a section containing the procedural statements surrounded by the BEGIN and END key words.  Each block must have a BEGIN and END statement, and may optionally include an exception section to handle errors.  The exception section is covered later in the book.

Here is an example of a simple block:
SQL> declare
  2    v_line varchar2(40);
  3  begin
  4    v_line := 'Hello World';
  5    dbms_output.put_line (v_line);
  6  end;
  7  /

Hello World

In the example above, the variable v_line is defined in the declarative section on line 2.  Like SQL statements, each line ends with a semicolon.  Once v_line is defined, it can be used in the procedural section.  First, v_line is assigned the literal string ‘Hello World’ on line 4.  Strings are surrounded by single quotes in SQL and PL/SQL.  The v_line variable is then placed in the output buffer using the procedure dbms_output.put_line.

In PL/SQL, the semicolon defines the end of a line of code.  To execute the PL/SQL block, use the forward slash “/” on a line by itself as shown on line 7.    If you forget the forward slash, SQL*Plus will simply wait for the next line to be entered.

Note:  If you execute a PL/SQL script and SQL*Plus returns a number, it probably is an indication that you forgot to place the”/” at the end of your script.  SQL*Plus is actually waiting for the next line.  Entering a “/” will execute the script.

A PL/SQL block with no name is called an anonymous block.  It starts with the declare key word to define the declarative section.
declare
    …  define variables here
begin
    …  code goes here
exceptions
end;

A named block is a procedure or a function.  The name portion defines the declarative section so the DECLARE key word is not used.
create procedure my_proc
as
    …  define variables here
begin
    …  code goes here
exceptions
end;

A procedure can be passed and change variables.  A function can be passed variables and must return a variable.
create function my_func (v_name varchar2)return number
as
    …  define variables here
begin
    …  code goes here
    return n_jobNum;
end;

When variables are passed to a procedure or function they can be IN, OUT or INOUT.  An IN variable is passed into the procedure or function and is used, but can not be changed.  An OUT variable is passed to the procedure but it can be changed and left in the changed state when the procedure ends.

An INOUT variable is passed to a procedure or function, and it can be used by the block, changed by the block, and left in a “changed” state when the block ends.  A function can only be passed an IN variable and must return a variable.  If this is confusing, don’t worry.  When we get to name blocks and provide some examples it will be much clearer.

SOURCE:http://www.dba-oracle.com/concepts/programming_pl_sql.htm

PL-SQL Interview Questions with Answers

1. Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low
Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.

2. What is a mutating table error and how can you get around it?
Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low
Expected answer: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.

4. What packages (if any) has Oracle provided for use by developers?
Expected answer: Oracle provides the DBMS_ series of packages. There are many
which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION,
DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

5. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

6. When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

7. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the NOTFOUND cursor variable in the exit when statement? Why?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

9. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.

10. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can
also be used.

11. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of
combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and
ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc. 

source:http://www.indiastudychannel.com/resources/8518-PL-SQL-Interview-Questions-with-Answers.aspx

PL/SQL interview qiuestions

1. Which of the following statements is true about implicit cursors?
   1. Implicit cursors are used for SQL statements that are not named.
   2. Developers should use implicit cursors with great care.
   3. Implicit cursors are used in cursor for loops to handle data processing.
   4. Implicit cursors are no longer a feature in Oracle.
   
   
2. Which of the following is not a feature of a cursor FOR loop?
   1. Record type declaration.
   2. Opening and parsing of SQL statements.
   3. Fetches records from cursor.
   4. Requires exit condition to be defined.
3. 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?
   1. Use employee.lname%type.
   2. Use employee.lname%rowtype.
   3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.
   4. Declare it to be type LONG.
4. Which three of the following are implicit cursor attributes?
   1. %found
   2. %too_many_rows
   3. %notfound
   4. %rowcount
   5. %rowtype
5. If left out, which of the following would cause an infinite loop to occur in a simple loop?
   1. LOOP
   2. END LOOP
   3. IF-THEN
   4. EXIT
6. Which line in the following statement will produce an error?
   1. cursor action_cursor is
   2. select name, rate, action
   3. into action_record
   4. from action_table;
   5. There are no errors in this statement.
7. The command used to open a CURSOR FOR loop is
   1. open
   2. fetch
   3. parse
   4. None, cursor for loops handle cursor opening implicitly.
8. What happens when rows are found using a FETCH statement
   1. It causes the cursor to close
   2. It causes the cursor to open
   3. It loads the current row values into variables
   4. It creates the variables to hold the current row values
9. 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?
   
   1. IN
   2. OUT
   3. RETURN
   4. IN OUT
10. 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?
    
    1. WHEN (new.cost_per_ticket > 3.75)
    2. WHEN (:new.cost_per_ticket > 3.75
    3. WHERE (new.cost_per_ticket > 3.75)
    4. WHERE (:new.cost_per_ticket > 3.75)
11. What is the maximum number of handlers processed before the PL/SQL block is exited when an exception occurs?
    1. Only one
    2. All that apply
    3. All referenced
    4. None
12. For which trigger timing can you reference the NEW and OLD qualifiers?
    1. Statement and Row
    2. Statement only
    3. Row only
    4. Oracle Forms trigger
13. 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?
    
    1. VARIABLE g_yearly_budget NUMBER
       EXECUTE g_yearly_budget := GET_BUDGET(11);
    2. VARIABLE g_yearly_budget NUMBER
       EXECUTE :g_yearly_budget := GET_BUDGET(11);
    3. VARIABLE :g_yearly_budget NUMBER
       EXECUTE :g_yearly_budget := GET_BUDGET(11);
    4. VARIABLE g_yearly_budget NUMBER
       :g_yearly_budget := GET_BUDGET(11);

14. 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?
    
    1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.
    2. Handle the error in EXCEPTION section by referencing the error code directly.
    3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.
    4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.
15. 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?
    
    1. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.
    2. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.
    3. Only the CALCULATE_BUDGET procedure needs to be recompiled.
    4. All three procedures are marked invalid and must be recompiled.
16. Which procedure can be used to create a customized error message?
    1. RAISE_ERROR
    2. SQLERRM
    3. RAISE_APPLICATION_ERROR
    4. RAISE_SERVER_ERROR
17. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?
    1. ALTER TRIGGER check_theater ENABLE;
    2. ENABLE TRIGGER check_theater;
    3. ALTER TABLE check_theater ENABLE check_theater;
    4. ENABLE check_theater;
18. 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?
    
    1. BEFORE DELETE ON gross_receipt
    2. AFTER DELETE ON gross_receipt
    3. BEFORE (gross_receipt DELETE)
    4. FOR EACH ROW DELETED FROM gross_receipt
19. 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?
    
    1. Add RETURN right before the IS keyword.
    2. Add RETURN number right before the IS keyword.
    3. Add RETURN boolean right after the IS keyword.
    4. Add RETURN boolean right before the IS keyword.
20. Under which circumstance must you recompile the package body after recompiling the package specification?
    1. Altering the argument list of one of the package constructs
    2. Any change made to one of the package constructs
    3. Any SQL statement change made to one of the package constructs
    4. Removing a local variable from the DECLARE section of one of the package constructs
21. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
    1. When the transaction is committed
    2. During the data manipulation statement
    3. When an Oracle supplied package references the trigger
    4. During a data manipulation statement and when the transaction is committed
22. Which Oracle supplied package can you use to output values and messages from database triggers, stored procedures and functions within SQL*Plus?
    1. DBMS_DISPLAY
    2. DBMS_OUTPUT
    3. DBMS_LIST
    4. DBMS_DESCRIBE
23. What occurs if a procedure or function terminates with failure without being handled?
    1. Any DML statements issued by the construct are still pending and can be committed or rolled back.
    2. Any DML statements issued by the construct are committed
    3. Unless a GOTO statement is used to continue processing within the BEGIN section, the construct terminates.
    4. The construct rolls back any DML statements issued and returns the unhandled exception to the calling environment.
24. 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?
    
    1. 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.
    2. Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
    3. Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
    4. 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.
25. 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?
    1. DBMS_DDL
    2. DBMS_DML
    3. DBMS_SYN
    4. DBMS_SQL