pl sql assignment

• Getting started with plsql
• Assignments model and language
• Assignments model in PL/SQL
• Bulk collect
• Collections and Records
• Exception Handling
• IF-THEN-ELSE Statement
• Object Types
• PLSQL procedure

plsql Assignments model and language Assignments model in PL/SQL

Fastest entity framework extensions.

All programming languages allow us to assign values to variables. Usually, a value is assigned to variable, standing on left side. The prototype of the overall assignment operations in any contemporary programming language looks like this:

This will assign right operand to the left operand. In PL/SQL this operation looks like this:

Left operand must be always a variable . Right operand can be value, variable or function:

When the code block is executed in SQL*Plus the following output is printed in console:

There is a feature in PL/SQL that allow us to assign "from right to the left". It's possible to do in SELECT INTO statement. Prototype of this instrunction you will find below:

This code will assign character literal to a local variable:

Asignment "from right to the left" is not a standard , but it's valuable feature for programmers and users. Generally it's used when programmer is using cursors in PL/SQL - this technique is used, when we want to return a single scalar value or set of columns in the one line of cursor from SQL cursor.

Further Reading:

• Assigning Values to Variables

Got any plsql Question?

• Advertise with us
• Cookie Policy
• Privacy Policy

Get monthly updates about new articles, cheatsheets, and tricks.

Home » PL/SQL Tutorial » PL/SQL Function

PL/SQL Function

Summary : in this tutorial, you will learn how to develop a PL/SQL function and how to call it in various places such as an assignment statement, a Boolean expression, and an SQL statement.

Creating a PL/SQL function

Similar to a procedure , a PL/SQL function is a reusable program unit stored as a schema object in the Oracle Database. The following illustrates the syntax for creating a function:

A function consists of a header and body.

The function header has the function name and a RETURN clause that specifies the datatype of the returned value. Each parameter of the function can be either in the IN , OUT , or INOUT mode. For more information on the parameter mode, check it out the PL/SQL procedure tutorial

The function body is the same as the procedure body which has three sections: declarative section, executable section, and exception-handling section.

• The declarative section is between the IS and BEGIN keywords. It is where you declare variables , constants , cursors , and user-defined types.
• The executable section is between the BEGIN and END keywords. It is where you place the executable statements. Unlike a procedure, you must have at least one RETURN statement in the executable statement.
• The exception-handling section is where you put the exception handler code.

In these three sections, only the executable section is required, the others are optional.

PL/SQL function example

The following example creates a function that calculates total sales by year.

To compile the function in Oracle SQL Developer, you click the Run Statement button as shown in the picture below:

Once the function is compiled successfully, you can find it under the Functions node:

Calling a PL/SQL function

You use a function anywhere that you use an expression of the same type. You can call a function in various places such as:

1) in an assignment statement:

2) in a Boolean expression

3) in an SQL statement

Editing a function

To edit and recompile an existing function, you follow these steps:

• First, click the function name that you want to edit
• Second, edit the code.
• Third, click the Compile menu option to recompile the code.

Removing a function

The DROP FUNCTION deletes a function from the Oracle Database. The syntax for removing a function is straightforward:

Followed by the DROP FUNCTION keywords is the function name that you want to drop.

For example, the following statement drops the GET_TOTAL_SALES function:

Oracle issued a message indicating that the function GET_TOTAL_SALES has been dropped:

If you want to drop a function using the SQL Developer, you can use these steps:

• First, right click on the name of function that you want to delete.
• Second, choose the Drop… menu option.
• Third, click the Apply button to confirm the deletion.

Now, you should know how to develop a PL/SQL function and call it in your program.

8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Introduction to PL/SQL

PL/SQL is a procedural extension of SQL, making it extremely simple to write procedural code that includes SQL as if it were a single language. This article gives a brief overview of some of the important points you should consider when first trying to learn PL/SQL.

What is so great about PL/SQL anyway?

Pl/sql architecture, variables and constants, using sql in pl/sql, branching and conditional control, looping statements, procedures, functions and packages, object types, collections, error handling, my utopian development environment.

Related articles.

• PL/SQL: Stop Making the Same Performance Mistakes
• PL/SQL Articles

PL/SQL is a procedural extension of SQL, making it extremely simple to write procedural code that includes SQL as if it were a single language. In comparison, most other programming languages require mapping data types, preparing statements and processing result sets, all of which require knowledge of specific APIs.

The data types in PL/SQL are a super-set of those in the database, so you rarely need to perform data type conversions when using PL/SQL. Ask your average Java or .NET programmer how they find handling date values coming from a database. They can only wish for the simplicity of PL/SQL.

When coding business logic in middle tier applications, a single business transaction may be made up of multiple interactions between the application server and the database. This adds a significant overhead associated with network traffic. In comparison, building all the business logic as PL/SQL in the database means client code needs only a single database call per transaction, reducing the network overhead significantly.

Oracle is a multi-platform database, making PL/SQL and incredibly portable language. If your business logic is located in the database, you are protecting yourself from operating system lock-in.

Programming languages go in and out of fashion continually. Over the last 35+ years Oracle databases have remained part of the enterprise landscape. Suggesting that any language is a safer bet than PL/SQL is rather naive. Placing your business logic in the database makes changing your client layer much simpler if you like to follow fashion.

Centralizing application logic enables a higher degree of security and productivity. The use of Application Program Interfaces (APIs) can abstract complex data structures and security implementations from client application developers, leaving them free to do what they do best.

The PL/SQL language is actually made up of two distinct languages. Procedural code is executed by the PL/SQL engine, while SQL is sent to the SQL statement executor.

For the most part, the tight binding between these two languages make PL/SQL look like a single language to most developers.

Overview of PL/SQL Elements

Blocks are the organizational unit for all PL/SQL code, whether it is in the form of an anonymous block, procedure, function, trigger or type. A PL/SQL block is made up of three sections (declaration, executable and exception), of which only the executable section is mandatory.

Based on this definition, the simplest valid block is shown below, but it doesn't do anything.

The optional declaration section allows variables, types, procedures and functions do be defined for use within the block. The scope of these declarations is limited to the code within the block itself, or any nested blocks or procedure calls. The limited scope of variable declarations is shown by the following two examples. In the first, a variable is declared in the outer block and is referenced successfully in a nested block. In the second, a variable is declared in a nested block and referenced from the outer block, resulting in an error as the variable is out of scope.

The main work is done in the mandatory executable section of the block, while the optional exception section is where all error processing is placed. The following two examples demonstrate the usage of exception handlers for trapping error messages. In the first, there is no exception handler so a query returning no rows results in an error. In the second the same error is trapped by the exception handler, allowing the code to complete successfully.

Variables and constants must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL. All variables and constants must be declared before they are referenced. The declarations of variables and constants are similar, but constant definitions must contain the CONSTANT keyword and must be assigned a value as part of the definition. Subsequent attempts to assign a value to a constant will result in an error. The following example shows some basic variable and constant definitions, along with a subsequent assignment of a value to a constant resulting in an error.

In addition to standard variable declarations used within SQL, PL/SQL allows variable datatypes to match the datatypes of existing columns, rows or cursors using the %TYPE and %ROWTYPE qualifiers, making code maintenance much easier. The following code shows each of these definitions in practice.

The %TYPE qualifier signifies that the variable datatype should match that of the specified table column, while the %ROWTYPE qualifier signifies that the variable datatype should be a record structure that matches the specified table or cursor structure. Notice that the record structures use the dot notation (variable.column) to reference the individual column data within the record structure.

Values can be assigned to variables directly using the ":=" assignment operator, via a SELECT ... INTO statement or when used as OUT or IN OUT parameter from a procedure. All three assignment methods are shown in the example below.

The SQL language is fully integrated into PL/SQL, so much so that they are often mistaken as being a single language by newcomers. It is possible to manuallly code the retrieval of data using explicit cursors, or let Oracle do the hard work and use implicit cursors. Examples of both explicit implicit cursors are presented below, all of which rely on the following table definition table.

The SELECT ... INTO statement allows data from one or more columns of a specific row to be retrieved into variables or record structures using an implicit cursor.

The previous example can be recoded to use an explicit cursor a shown below. Notice that the cursor is now defined in the declaration section and is explicitly opened and closed, making the code larger and a little ugly.

When a query returns multiple rows is can be processed within a loop. The following example uses a cursor FOR-LOOP to cycle through multiple rows of an implicit cursor. Notice there is no need for a variable definition as "cur_rec" acts as a pointer to the current record of the cursor.

The explicit cursor version of the previous example is displayed below. Once again the cursor management is all done manually, but this time the exit from the loop must be managed manually also.

In most situations the implicit cursors provide a faster and cleaner solution to data retrieval than their explicit equivalents.

The IF-THEN-ELSE and CASE statements allow code to decide on the correct course of action for the current circumstances. In the following example the IF-THEN-ELSE statement is used to decide if today is a weekend day.

First, the expression between the IF and the THEN is evaluated. If that expression equates to TRUE the code between the THEN and the ELSE is performed. If the expression equates to FALSE the code between the ELSE and the END IF is performed. The IF-THEN-ELSE statement can be extended to cope with multiple decisions by using the ELSIF keyword. The example below uses this extended form to produce a different message for Saturday and Sunday.

SQL CASE expressions were introduced in the later releases of Oracle 8i, but Oracle 9i included support for CASE statements in PL/SQL for the first time. The CASE statement is the natural replacement for large IF-THEN-ELSIF-ELSE statements. The following code gives an example of a matched CASE statement.

The WHEN clauses of a matched CASE statement simply state the value to be compared. If the value of the variable specified after the CASE keyword matches this comparison value the code after the THEN keyword is performed.

A searched CASE statement has a slightly different format, with each WHEN clause containing a full expression, as shown below.

Loops allow sections of code to be processed multiple times. In its most basic form a loop consists of the LOOP and END LOOP statement, but this form is of little use as the loop will run forever.

Typically you would expect to define an end condition for the loop using the EXIT WHEN statement along with a Boolean expression. When the expression equates to true the loop stops. The example below uses this syntax to pint out numbers from 1 to 5.

The placement of the EXIT WHEN statement can affect the processing inside the loop. For example, placing it at the start of the loop means the code within the loop may be executed "0 to many" times, like a while-do loop in other language. Placing the EXIT WHEN at the end of the loop means the code within the loop may be executed "1 to many" times, like a do-while loop in other languages.

The FOR-LOOP statement allows code within the loop to be repeated a specified number of times based on the lower and upper bounds specified in the statement. The example below shows how the previous example could be recorded to use a FOR-LOOP statement.

The WHILE-LOOP statement allows code within the loop to be repeated until a specified expression equates to TRUE. The following example shows how the previous examples can be re-coded to use a WHILE-LOOP statement.

In addition to these loops a special cursor FOR-LOOP is available as seen previously.

The GOTO statement allows a program to branch unconditionally to a predefined label. The following example uses the GOTO statement to repeat the functionality of the examples in the previous section.

In this example the GOTO has been made conditional by surrounding it with an IF statement. When the GOTO is called the program execution immediately jumps to the appropriate label, defined using double-angled brackets.

Procedures and functions allow code to be named and stored in the database, making code reuse simpler and more efficient. Procedures and functions still retain the block format, but the DECLARE keyword is replaced by PROCEDURE or FUNCTION definitions, which are similar except for the additional return type definition for a function. The following procedure displays numbers between upper and lower bounds defined by two parameters, then shows the output when it's run.

The following function returns the difference between upper and lower bounds defined by two parameters.

Packages allow related code, along with supporting types, variables and cursors, to be grouped together. The package is made up of a specification that defines the external interface of the package, and a body that contains all the implementation code. The following code shows how the previous procedure and function could be grouped into a package.

Once the package specification and body are compiled they can be executed as before, provided the procedure and function names are prefixed with the package name.

Since the package specification defines the interface to the package, the implementation within the package body can be modified without invalidating any dependent code, thus breaking complex dependency chains. A call to any element in the package causes the whole package to be loaded into memory, improving performance compared to loading several individual procedures and functions.

Record types are composite data structures, or groups of data elements, each with its own definition. Records can be used to mimic the row structures of tables and cursors, or as a convenient was to pass data between subprograms without listing large number of parameters.

When a record type must match a particular table or cursor structure it can be defined using the %ROWTYPE attribute, removing the need to define each column within the record manually. Alternatively, the record can be specified manually. The following code provides an example of how records can be declared and used in PL/SQL.

Notice how the records can be assigned to each other directly, and how all elements within a record can be initialized with a single assignment of a NULL value.

Oracle implements Objects through the use of TYPE declarations, defined in a similar way to packages. Unlike packages where the instance of the package is limited to the current session, an instance of an object type can be stored in the database for later use. The definition of the type contains a comma separated list of attributes/properties, defined in the same way as package variables, and member functions/procedures. If a type contains member functions/procedures, the procedural work for these elements is defined in the TYPE BODY .

To see how objects can be used let's assume we want to create one to represent a person. In this case, a person is defined by three attributes (first_name, last_name, date_of_birth). We would also like to be able to return the age of the person, so this is included as a member function (get_age).

Next the type body is created to implement the get_age member function.

Once the object is defined it can be used to define a column in a database table.

To insert data into the PEOPLE table we must use the t_person() constructor. This can be done as part of a regular DML statement, or using PL/SQL.

Once the data is loaded it can be queried using the dot notation.

The query below shows this in action.

Oracle uses collections in PL/SQL the same way other languages use arrays. You can read more about the types of collections available here .

Database triggers are stored programs associated with a specific table, view or system events, such that when the specific event occurs the associated code is executed. Triggers can be used to validate data entry, log specific events, perform maintenance tasks or perform additional application logic. The following example shows how a table trigger could be used to keep an audit of update actions.

From this you can see that the trigger fired when the price of the record was updated, allowing us to audit the action.

The following trigger sets the current_schema parameter for each session logging on as the APP_LOGON user, making the default schema that of the SCHEMA_OWNER user.

You can read more about database triggers here .

When PL/SQL detects an error normal execution stops and an exception is raised, which can be captured and processed within the block by the exception handler if it is present. If the block does not contain an exception handler section the exception propagates outward to each successive block until a suitable exception handler is found, or the exception is presented to the client application.

Oracle provides many predefined exceptions for common error conditions, like NO_DATA_FOUND when a SELECT ... INTO statement returns no rows. The following example shows how exceptions are trapped using the appropriate exception handler. Assume we want to return the username associated with a specific user_id value, we might do the following.

That works fine for user_id values that exist, but look what happens if we use one that doesn't.

This is not a very user friendly message, so we can trap this error and produce something more meaningful to the users.

It is possible to declare your own named exceptions for application specific errors, or associate them with Oracle "ORA-" messages, which are executed using the RAISE statement. The example below builds on the previous example using a user-defined named exception to signal an application specific error.

The code still handles users that don't exist, but now it also raises an exception if the user returned is either SYS or SYSTEM.

We can raise errors and give them a friendly message using the RAISE_APPLICATION_ERROR procedure. The first parameter is a user-defined error number that has to be between -20000 and -20999. Notice the example below uses RAISE_APPLICATION_ERROR , rather than a user-defined named exception. The output includes the error number we specified, but notice we have to trap it with the OTHERS exception handler.

If we want to associate a named exception with the error number, we can use PRAGMA EXCEPTION_INIT as show below. This allows us to trap the error in a named exception handler, rather than having to trap it in the OTHERS handler.

I wrote a blog post about this many years ago. You can read it here . I think it's worth just spending a little time reiterating some of the main points here. It may not be to everyone's liking, but I've always found it to be the most secure and flexible approach I've come across.

I believe the use of PL/SQL Application Program Interfaces (APIs) should be compulsory. Ideally, client application developers should have no access to tables, but instead access data via PL/SQL APIs, or possibly views if absolutely necessary. The diagram below shows this relationship.

In reality the organisation is likely to be a little more complicated. Maybe something like the example below.

This has a number of beneficial effects, including:

• Security and auditing mechanisms can be implemented and maintained at the database level, with little or no impact on the client application layer.
• It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API.
• It prevents people who don't understand SQL writing inefficient queries. All SQL should be written by PL/SQL developers or DBAs, reducing the likelihood of bad queries.
• The underlying structure of the database is hidden from the client application developers, so it hides complexity and structural changes can be made without client applications being changed.
• The API implementation can be altered and tuned without affecting the client application layer. Reducing the need for redeployments of applications.
• The same APIs are available to all applications that access the database. Resulting in reduced duplication of effort.
• These API can be presented as web services using XML or JSON. See ORDS .

This sounds a little extreme, but this approach has paid dividends for me again and again. Let's elaborate on these points to explain why this approach is so successful.

It's a sad fact that auditing and security are often only brought into focus after something bad has happened. Having the ability to revise and refine these features is a massive bonus. If this means you have to re-factor your whole application you are going to have problems. If on the other hand it can be revised in your API layer you are on to a winner.

Over-reliance on database triggers is a bad thing in my opinion. It seems every company I've worked for has at one time or another used triggers to patch a “hole” or implement some business functionality in their application. Every time I see this my heart sinks. Invariably these triggers get disabled by accident and bits of functionality go AWOL, or people forget they exist and recode some of their functionality elsewhere in the application. It's far easier to wrap the transactional processing in an API that includes all necessary functionality, thereby removing the need for table triggers entirely.

Many client application developers have to be able to work with several database engines, and as a result are not always highly proficient at coding against Oracle databases. Added to that, some development architectures such as J2EE positively discourage developers from working directly with the database. You wouldn't ask an inexperienced person to fix your car, so why would you ask one to write SQL for you? Abstracting the SQL in an API leaves the client application developers to do what they do best, while your PL/SQL programmers can write the most efficient SQL and PL/SQL possible.

During the lifetime of an application many changes can occur in the physical implementation of the database. It's nice to think that the design will be perfected before application development starts, but in reality this seldom seems to be the case. The use of APIs abstracts the developers from the physical implementation of the database, allowing change without impacting on the application.

In the same way, it is not possible to foresee all possible performance problems during the coding phase of an application. Many times developers will write and test code with unrealistic data, only to find the code that was working perfectly in a development environment works badly in a production environment. If the data manipulation layer is coded as an API it can be tuned without re-coding sections of the application, after all the implementation has changed, not the interface.

A problem I see time and time again is that companies invest heavily in coding their business logic into a middle tier layer on an application server, then want to perform data loads either directly into the database, or via a tool that will not link to their middle tier application. As a result they have to re-code sections of their business logic into PL/SQL or some other client language. Remember, it's not just the duplication of effort during the coding, but also the subsequent maintenance. Since every language worth using can speak to Oracle via OCI, JDBC, ODBC or web services, it makes sense to keep your logic in the database and let every application or data load use the same programming investment.

Of course, you may not always have full control of your development environment, but it's worth bearing these points in mind.

Additional functionality that may be useful to develop your API layer is listed below.

• Pipelined Table Functions
• Using Ref Cursors To Return Recordsets
• Implicit Statement Results
• Proxy User Authentication and Connect Through in Oracle Databases

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

Created: 2013-03-10  Updated: 2024-04-02

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall Copyright & Disclaimer

PL/SQL Tutorial

If you are looking for a complete PL/SQL tutorial , you are at the right place. This plsqltutorial.com website provides you with a comprehensive PL/SQL tutorial that helps you learn PL/SQL quickly from scratch.

What is PL/SQL?

PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL). SQL is a powerful language for both querying and updating data in relational databases.

Oracle created PL/SQL that extends some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on the Oracle database. Getting to know more information about PL/SQL language .

Before getting started, we highly recommend setting up an Oracle database in your system to help you practice and learn PL/SQL effectively.

Basic PL/SQL Tutorial

We assume that you have the fundamental knowledge of databases and SQL to start our PL/SQL tutorial. If this is not the case, you need to follow the basic SQL tutorial to have a good start.

This section is targeted as a good starting point for those who are new to PL/SQL. However, if you are very familiar with the language and also want to glance through these tutorials as a refresher, you may even find something useful that you haven’t seen before.

• PL/SQL Block Structure – introduces you to PL/SQL block structure and shows you how to develop the first running PL/SQL program.
• PL/SQL Variables – shows you how to work with PL/SQL variables including declaring, naming, and assigning variables.
• PL/SQL Function – explains what PL/SQL functions are and shows you how to create PL/SQL functions.
• PL/SQL Procedure – discusses PL/SQL procedures and shows you how to create PL/SQL procedures.
• PL/SQL Nested Block – explains what a PL/SQL nested block is and how to apply it in PL/SQL programming.
• PL/SQL IF Statement – introduces you to various forms of the PL/SQL IF statement including IF-THEN , IF-THEN-ELSE  and IF-THEN-ELSIF  statement.
• PL/SQL CASE Statement – shows you how to use PL/SQL CASE statement and PL/SQL searched CASE statement.
• PL/SQL LOOP Statement – guides you on how to use PL/SQL LOOP statement to execute a block of code repeatedly.
• PL/SQL WHILE Loop Statement – executes a sequence of statements with a condition that is checked at the beginning of each iteration with the  WHILE loop statement.
• PL/SQL FOR Loop Statement – shows you how to execute a sequence of statements in a fixed number of times with FOR loop statement.
• PL/SQL Exception Handling – teaches you how to handle exceptions properly in PL/SQL as well as shows you how to define your own exception and raise it in your code.
• PL/SQL Record – explains the PL/SQL record and shows you how to use records to manage your data more effectively.
• PL/SQL Cursor – covers PL/SQL cursor concept and walks you through how to use a cursor to loop through a set of rows and process each row individually.
• PL/SQL Packages – shows you how to create a PL/SQL package that is a group of related functions, procedures, types, etc.

Oracle PL/SQL Assignment Operator

Oracle pl/sql concatenation operator, oracle pl/sql like operator, oracle pl/sql regular expressions, oracle pl/sql metacharacters, oracle pl/sql contains, oracle pl/sql operator precedence, oracle pl/sql operator precedence demo, oracle pl/sql logical operators, oracle pl/sql null in unequal compare, oracle pl/sql null in equal compare, oracle pl/sql logical operator precedence, oracle pl/sql short-circuit evaluation, oracle pl/sql arithmetic comparisons, oracle pl/sql between operator, oracle pl/sql in, oracle pl/sql boolean expressions.

The assignment operator is specific to PL/SQL.

Its primary function is to set a variable equal to the value or expression on the other side of the operator.

If we wanted to set the variable v_price equal to 5.5 * .90, we would write it as follows:

An assignment can take place in the declaration section when a variable is first declared, or in the execution or exception sections at any time.

For example, to declare the variable v_price, we would write.

The variable is not yet initialized, and its value is NULL.

To initialize the variable to zero, we can use the assignment operator in the declaration section as follows:

In the execution section, the assignment operator is used for assigning constants, literals, other variables, or expressions to a variable.

Another use is to assign a function call to a variable where the return value of the function is ultimately stored.

Assignment statement (PL/SQL)

The assignment statement sets a previously-declared variable or formal OUT or IN OUT parameter to the value of an expression.

Description

• Skip to content
• Accessibility Policy
• QUICK LINKS
• Oracle Cloud Infrastructure
• Oracle Fusion Cloud Applications
• Oracle Database
• Download Java
• Careers at Oracle

• Create an Account

PL/SQL Inherits Database Robustness, Security, and Portability

PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.

• Why Use PL/SQL?

An application that uses Oracle Database is worthless unless only correct and complete data is persisted. The time-honored way to ensure this is to expose the database only via an interface that hides the implementation details—the tables and the SQL statements that operate on these. This approach is generally called the smart database or SmartDB paradigm, because PL/SQL subprograms inside the database issue the SQL statements from code that implements the surrounding business logic; and because the data can be changed and viewed only through a PL/SQL interface.

Build Your PL/SQL Knowledge

Get Started with Table Functions

The Oracle PL/SQL Blog

YouTube Channel: Practically Perfect PL/SQL

Getting started with pl/sql.

PL/SQL is a powerful, yet straightforward database programming language. It is easy to both write and read, and comes packed with lots of out-of-the-box optimizations and security features. 

Building and Managing PL/SQL Program Units

• Building with Blocks : PL/SQL is a block-structured language; familiarity with blocks is critical to writing good code.
• Controlling the Flow of Execution : Conditional branching and iterative processing in PL/SQL
• Wrap Your Code in a Neat Package : Packages are the fundamental building blocks of any high quality PL/SQL-based application
• Picking Your Packages : Concepts and benefits of PL/SQL packages
• Error Management : An exploration of error management features in PL/SQL
• The Data Dictionary: Make Views Work for You : Use several key data dictionary views to analyze and manage your code

PL/SQL Datatypes

• Working with Strings in PL/SQL : PL/SQL offers several different string datatypes for use in your applications
• Working with Numbers in PL/SQL : Learn about and how to use the different numeric types in PL/SQL.
• Working with Dates in PL/SQL Dates are a relatively complex scalar datatype, in both SQL and PL/SQL
• Working with Records : A very common and useful composite type, PL/SQL’s analogue to a table’s row
• Error Management : PL/SQL’s implementation of arrays plays a role in almost every key performance feature of PL/SQL
• Working with Collections : Use several key data dictionary views to analyze and manage your code

SQL in PL/SQL

• Working with Cursors : PL/SQL cursors make it easy for you to manipulate the contents of database tables
• Bulk Processing with BULK COLLECT and FORALL : The most important performance feature in PL/SQL related to executing SQL

Practically Perfect PL/SQL with Steven Feuerstein

Practically Perfect PL/SQL (P3) offers videos on PL/SQL by Steven Feuerstein, Oracle Developer Advocate for PL/SQL and author of Oracle PL/SQL Programming .

Getting Rid of Hard-Coding in PL/SQL This first playlist of P3 explores the various forms of hard-coding that can appear in PL/SQL, and how best to get rid of them.

• What is Hard-Coding?
• The Psychology Behind Hard-Coding
• Get rid of those hard-coded literals!
• Every VARCHAR2(N) Declaration a Bug!?
• Hide Those Business Rules and Formulas!

Use Oracle PL/SQL with

• Oracle and PHP
• Oracle and Java
• Oracle and Python
• Oracle and .NET
• Oracle and Hadoop: Big Data Connectors
• Oracle and Node.js

Documentation

• Oracle Database 18c: Database PL/SQL Language Reference , PL/SQL Packages and Types Reference
• Oracle Database 12c Release 2: PL/SQL Language Reference , PL/SQL Packages and Types Reference
• Oracle Database 12c Release 1: PL/SQL Language Reference , PL/SQL Packages and Types Reference
• Oracle Database 11g Release 2: PL/SQL Language Reference , PL/SQL Packages and Types Reference
• Presentation: Doing PL/SQL from SQL: Correctness and Performance (PDF)
• Presentation: New PL/SQL Capabilities in Oracle Database 12c (PDF)
• SmartDB Resource Center
• White paper: Doing SQL from PL/SQL: Best and Worst Practices (PDF)
• White paper: Freedom, Order, and PL/SQL Optimization
• White paper: How to write SQL injection-proof PL/SQL (PDF)
• White paper: With Oracle Database 12c, There is All the More Reason to Use Database PL/SQL (PDF)
• Analytical SQL
• Edition-based Redefinition
• Query Optimization
• AskTom - Q&A and Monthly Training
• Oracle LiveSQL: Try SQL and PL/SQL in your browser
• Oracle Dev Gym - Quizzes, Workouts and Classes
• PL/SQL Office Hours
• SmartDB Office Hours
• SQL-PL/SQL Oracle Developers Community Forum

Socialize with Us

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications

Merey1508/PLSQL-assignments

Folders and files, repository files navigation, pl-sql-assignments, assignment 1: introduction to pl/sql programming language.

• Download SQL Developer
• Create a database connection
• Test the new connection

Assignment 2: Declaring PL/SQL Variables

• PL/SQL variables:

– Reference

– Large object (LOB)

– Composite

• Non-PL/SQL variables: Bind variables

Assignment 3: Writing Executable Statements

• Lexical Units in a PL/SQL Block
• Data Type Conversion
• Nested Blocks

Assignment 4: Interacting with the Oracle Server

• SELECT Statements in PL/SQL
• Inserting Data
• Updating Data
• Deleting Data
• Merging Rows
• Implicit/Explicit cursors

Assignment 5: Writing Control Structures

• IF Statement, NULL Value in IF Statement
• IF THEN ELSE Statement
• IF ELSIF ELSE Clause
• CASE Expressions
• LOOP Statements: Basic Loop, FOR loop, WHILE loop
• Nested loops
• CONTINUE Statement

Assignment 6: Working with Composite Data Types

• PL/SQL Records
• PL/SQL Collections
• PL/SQL Records Attributes
• Associative Arrays (INDEX BY Tables)

Assignment 7: Using Explicit Cursors

• Declaring the Cursor
• Opening the Cursor
• Fetching Data from the Cursor
• Closing the Cursor
• Cursor FOR Loops
• Explicit Cursor Attributes
• Cursors with Parameters

Assignment 8: Handling Exceptions

• Predefined Exceptions
• Standard Oracle Server Exceptions

• Database PL/SQL Language Reference
• PL/SQL Language Fundamentals

3 PL/SQL Language Fundamentals

The PL/SQL language fundamental components are explained.

Character Sets

Lexical Units

Declarations

References to Identifiers

Scope and Visibility of Identifiers

Assigning Values to Variables

Expressions

Error-Reporting Functions

Conditional Compilation

3.1 Character Sets

Any character data to be processed by PL/SQL or stored in a database must be represented as a sequence of bytes. The byte representation of a single character is called a character code . A set of character codes is called a character set .

Every Oracle database supports a database character set and a national character set. PL/SQL also supports these character sets. This document explains how PL/SQL uses the database character set and national character set.

Database Character Set

National Character Set

About Data-Bound Collation

Oracle Database Globalization Support Guide for general information about character sets

3.1.1 Database Character Set

PL/SQL uses the database character set to represent:

Stored source text of PL/SQL units

For information about PL/SQL units, see " PL/SQL Units and Compilation Parameters " .

Character values of data types CHAR , VARCHAR2 , CLOB , and LONG

For information about these data types, see " SQL Data Types " .

The database character set can be either single-byte, mapping each supported character to one particular byte, or multibyte-varying-width, mapping each supported character to a sequence of one, two, three, or four bytes. The maximum number of bytes in a character code depends on the particular character set.

Every database character set includes these basic characters:

Latin letters: A through Z and a through z

Decimal digits: 0 through 9

Punctuation characters in Table 3-1

Whitespace characters: space , tab , new line , and carriage return

PL/SQL source text that uses only the basic characters can be stored and compiled in any database. PL/SQL source text that uses nonbasic characters can be stored and compiled only in databases whose database character sets support those nonbasic characters.

Table 3-1 Punctuation Characters in Every Database Character Set

Oracle Database Globalization Support Guide for more information about the database character set

3.1.2 National Character Set

PL/SQL uses the national character set to represent character values of data types NCHAR , NVARCHAR2 and NCLOB .

" SQL Data Types " for information about these data types

Oracle Database Globalization Support Guide for more information about the national character set

3.1.3 About Data-Bound Collation

Collation (also called sort ordering) is a set of rules that determines if a character string equals, precedes, or follows another string when the two strings are compared and sorted.

Different collations correspond to rules of different spoken languages. Collation-sensitive operations are operations that compare text and need a collation to control the comparison rules. The equality operator and the built-in function INSTR are examples of collation-sensitive operations.

Starting with Oracle Database 12c release 2 (12.2) , a new architecture provides control of the collation to be applied to operations on character data. In the new architecture, collation becomes an attribute of character data, analogous to a data type. You can now declare collation for a column and this collation is automatically applied by all collation-sensitive SQL operations referencing the column. The data-bound collation feature uses syntax and semantics compatible with the ISO/IEC SQL standard.

The PL/SQL language has limited support for the data-bound collation architecture. All data processed in PL/SQL expressions is assumed to have the compatibility collation USING_NLS_COMP . This pseudo-collation instructs collation-sensitive operators to behave in the same way as in previous Oracle Database releases. That is, the values of the session parameters NLS_COMP and NLS_SORT determine the collation to use. However, all SQL statements embedded or constructed dynamically in PL/SQL fully support the new architecture.

A new property called default collation has been added to tables, views, materialized views, packages, stored procedures, stored functions, triggers, and types. The default collation of a unit determines the collation for data containers, such as columns, variables, parameters, literals, and return values, that do not have their own explicit collation declaration in that unit. The default collation for packages, stored procedures, stored functions, triggers, and types must be USING_NLS_COMP .

For syntax and semantics, see the DEFAULT COLLATION Clause .

CREATE FUNCTION Statement

CREATE PACKAGE Statement

CREATE PROCEDURE Statement

CREATE TRIGGER Statement

CREATE TYPE Statement

Oracle Database Globalization Support Guide for more information about specifying data-bound collation for PL/SQL units

Oracle Database Globalization Support Guide for more information about effective schema default collation

3.2 Lexical Units

The lexical units of PL/SQL are its smallest individual components—delimiters, identifiers, literals, pragmas, and comments.

Identifiers

Whitespace Characters Between Lexical Units

3.2.1 Delimiters

A delimiter is a character, or character combination, that has a special meaning in PL/SQL.

Do not embed any others characters (including whitespace characters) inside a delimiter.

Table 3-2 summarizes the PL/SQL delimiters.

Table 3-2 PL/SQL Delimiters

3.2.2 Identifiers

Identifiers name PL/SQL elements, which include:

Reserved words

Subprograms

Every character in an identifier, alphabetic or not, is significant. For example, the identifiers lastname and last_name are different.

You must separate adjacent identifiers by one or more whitespace characters or a punctuation character.

Except as explained in " Quoted User-Defined Identifiers " , PL/SQL is case-insensitive for identifiers. For example, the identifiers lastname , LastName , and LASTNAME are the same.

Reserved Words and Keywords

Predefined Identifiers

User-Defined Identifiers

3.2.2.1 Reserved Words and Keywords

Reserved words and keywords are identifiers that have special meaning in PL/SQL.

You cannot use reserved words as ordinary user-defined identifiers. You can use them as quoted user-defined identifiers, but it is not recommended. For more information, see " Quoted User-Defined Identifiers " .

You can use keywords as ordinary user-defined identifiers, but it is not recommended.

For lists of PL/SQL reserved words and keywords, see Table D-1 and Table D-2 , respectively.

3.2.2.2 Predefined Identifiers

Predefined identifiers are declared in the predefined package STANDARD .

An example of a predefined identifier is the exception INVALID_NUMBER .

For a list of predefined identifiers, connect to Oracle Database as a user who has the DBA role and use this query:

You can use predefined identifiers as user-defined identifiers, but it is not recommended. Your local declaration overrides the global declaration (see " Scope and Visibility of Identifiers " ).

3.2.2.3 User-Defined Identifiers

A user-defined identifier is:

Composed of characters from the database character set

Either ordinary or quoted

Make user-defined identifiers meaningful. For example, the meaning of cost_per_thousand is obvious, but the meaning of cpt is not.

Avoid using the same user-defined identifier for both a schema and a schema object. This decreases code readability and maintainability and can lead to coding mistakes. Note that local objects have name resolution precedence over schema qualification.

For more information about database object naming rules, see Oracle Database SQL Language Reference .

For more information about PL/SQL-specific name resolution rules, see " Differences Between PL/SQL and SQL Name Resolution Rules ".

3.2.2.3.1 Ordinary User-Defined Identifiers

An ordinary user-defined identifier:

Begins with a letter

Can include letters, digits, and these symbols:

Dollar sign ($)

Number sign (#)

Underscore (_)

Is not a reserved word (listed in Table D-1 ).

The database character set defines which characters are classified as letters and digits. If COMPATIBLE is set to a value of 12.2 or higher, the representation of the identifier in the database character set cannot exceed 128 bytes. If COMPATIBLE is set to a value of 12.1 or lower, the limit is 30 bytes.

Examples of acceptable ordinary user-defined identifiers:

Examples of unacceptable ordinary user-defined identifiers:

3.2.2.3.2 Quoted User-Defined Identifiers

A quoted user-defined identifier is enclosed in double quotation marks.

Between the double quotation marks, any characters from the database character set are allowed except double quotation marks, new line characters, and null characters. For example, these identifiers are acceptable:

If COMPATIBLE is set to a value of 12.2 or higher, the representation of the quoted identifier in the database character set cannot exceed 128 bytes (excluding the double quotation marks). If COMPATIBLE is set to a value of 12.1 or lower, the limit is 30 bytes.

A quoted user-defined identifier is case-sensitive, with one exception: If a quoted user-defined identifier, without its enclosing double quotation marks, is a valid ordinary user-defined identifier, then the double quotation marks are optional in references to the identifier, and if you omit them, then the identifier is case-insensitive.

It is not recommended, but you can use a reserved word as a quoted user-defined identifier. Because a reserved word is not a valid ordinary user-defined identifier, you must always enclose the identifier in double quotation marks, and it is always case-sensitive.

Example 3-1 Valid Case-Insensitive Reference to Quoted User-Defined Identifier

In this example, the quoted user-defined identifier "HELLO" , without its enclosing double quotation marks, is a valid ordinary user-defined identifier. Therefore, the reference Hello is valid.

Example 3-2 Invalid Case-Insensitive Reference to Quoted User-Defined Identifier

In this example, the reference "Hello" is invalid, because the double quotation marks make the identifier case-sensitive.

Example 3-3 Reserved Word as Quoted User-Defined Identifier

This example declares quoted user-defined identifiers "BEGIN" , "Begin" , and "begin" . Although BEGIN , Begin , and begin represent the same reserved word, "BEGIN" , "Begin" , and "begin" represent different identifiers.

Example 3-4 Neglecting Double Quotation Marks

This example references a quoted user-defined identifier that is a reserved word, neglecting to enclose it in double quotation marks.

Example 3-5 Neglecting Case-Sensitivity

This example references a quoted user-defined identifier that is a reserved word, neglecting its case-sensitivity.

3.2.3 Literals

A literal is a value that is neither represented by an identifier nor calculated from other values.

For example, 123 is an integer literal and 'abc' is a character literal, but 1+2 is not a literal.

PL/SQL literals include all SQL literals (described in Oracle Database SQL Language Reference ) and BOOLEAN literals (which SQL does not have). A BOOLEAN literal is the predefined logical value TRUE , FALSE , or NULL . NULL represents an unknown value.

Like Oracle Database SQL Language Reference , this document uses the terms character literal and string interchangeably.

When using character literals in PL/SQL, remember:

Character literals are case-sensitive.

For example, 'Z' and 'z' are different.

Whitespace characters are significant.

For example, these literals are different:

PL/SQL has no line-continuation character that means "this string continues on the next source line." If you continue a string on the next source line, then the string includes a line-break character.

For example, this PL/SQL code:

Prints this:

If your string does not fit on a source line and you do not want it to include a line-break character, then construct the string with the concatenation operator ( || ).

For more information about the concatenation operator, see " Concatenation Operator " .

'0' through '9' are not equivalent to the integer literals 0 through 9.

However, because PL/SQL converts them to integers, you can use them in arithmetic expressions.

A character literal with zero characters has the value NULL and is called a null string .

However, this NULL value is not the BOOLEAN value NULL .

An ordinary character literal is composed of characters in the database character set .

For information about the database character set, see Oracle Database Globalization Support Guide .

A national character literal is composed of characters in the national character set .

For information about the national character set, see Oracle Database Globalization Support Guide .

You can use Q or q as part of the character literal syntax to indicate that an alternative quoting mechanism will be used. This mechanism allows a wide range of delimiters for a string as opposed to simply single quotation marks.

For more information about the alternative quoting mechanism, see Oracle Database SQL Language Reference .

You can view and run examples of the Q mechanism at Alternative Quoting Mechanism (''Q'') for String Literals

3.2.4 Pragmas

A pragma is an instruction to the compiler that it processes at compile time.

A pragma begins with the reserved word PRAGMA followed by the name of the pragma. Some pragmas have arguments. A pragma may appear before a declaration or a statement. Additional restrictions may apply for specific pragmas. The extent of a pragma’s effect depends on the pragma. A pragma whose name or argument is not recognized by the compiler has no effect.

For information about pragmas syntax and semantics, see :

" AUTONOMOUS_TRANSACTION Pragma "

" COVERAGE Pragma "

" DEPRECATE Pragma "

" EXCEPTION_INIT Pragma "

" INLINE Pragma "

" RESTRICT_REFERENCES Pragma "

" SERIALLY_REUSABLE Pragma "

" SUPPRESSES_WARNING_6009 Pragma "

" UDF Pragma "

3.2.5 Comments

The PL/SQL compiler ignores comments. Their purpose is to help other application developers understand your source text.

Typically, you use comments to describe the purpose and use of each code segment. You can also disable obsolete or unfinished pieces of code by turning them into comments.

Single-Line Comments

Multiline Comments

" Comment "

3.2.5.1 Single-Line Comments

A single-line comment begins with -- and extends to the end of the line.

Do not put a single-line comment in a PL/SQL block to be processed dynamically by an Oracle Precompiler program. The Oracle Precompiler program ignores end-of-line characters, which means that a single-line comment ends when the block ends.

While testing or debugging a program, you can disable a line of code by making it a comment. For example:

Example 3-6 Single-Line Comments

This example has three single-line comments.

3.2.5.2 Multiline Comments

A multiline comment begins with /* , ends with */ , and can span multiple lines.

You can use multiline comment delimiters to "comment out" sections of code. When doing so, be careful not to cause nested multiline comments. One multiline comment cannot contain another multiline comment. However, a multiline comment can contain a single-line comment. For example, this causes a syntax error:

This does not cause a syntax error:

Example 3-7 Multiline Comments

This example has two multiline comments. (The SQL function TO_CHAR returns the character equivalent of its argument. For more information about TO_CHAR , see Oracle Database SQL Language Reference .)

3.2.6 Whitespace Characters Between Lexical Units

You can put whitespace characters between lexical units, which often makes your source text easier to read.

Example 3-8 Whitespace Characters Improving Source Text Readability

3.3 Declarations

A declaration allocates storage space for a value of a specified data type, and names the storage location so that you can reference it.

You must declare objects before you can reference them. Declarations can appear in the declarative part of any block, subprogram, or package.

Declaring Variables

Declaring Constants

Initial Values of Variables and Constants

NOT NULL Constraint

Declaring Items using the %TYPE Attribute

For information about declaring objects other than variables and constants, see the syntax of declare_section in " Block " .

3.3.1 NOT NULL Constraint

You can impose the NOT NULL constraint on a scalar variable or constant (or scalar component of a composite variable or constant).

The NOT NULL constraint prevents assigning a null value to the item. The item can acquire this constraint either implicitly (from its data type) or explicitly.

A scalar variable declaration that specifies NOT NULL , either implicitly or explicitly, must assign an initial value to the variable (because the default initial value for a scalar variable is NULL ).

PL/SQL treats any zero-length string as a NULL value. This includes values returned by character functions and BOOLEAN expressions.

To test for a NULL value, use the " IS [NOT] NULL Operator " .

Example 3-9 Variable Declaration with NOT NULL Constraint

In this example, the variable acct_id acquires the NOT NULL constraint explicitly, and the variables a , b , and c acquire it from their data types.

Example 3-10 Variables Initialized to NULL Values

In this example, all variables are initialized to NULL .

3.3.2 Declaring Variables

A variable declaration always specifies the name and data type of the variable.

For most data types, a variable declaration can also specify an initial value.

The variable name must be a valid user-defined identifier .

The data type can be any PL/SQL data type. The PL/SQL data types include the SQL data types. A data type is either scalar (without internal components) or composite (with internal components).

Example 3-11 Scalar Variable Declarations

This example declares several variables with scalar data types.

Related Topics

" User-Defined Identifiers "

" Scalar Variable Declaration " for scalar variable declaration syntax

PL/SQL Data Types for information about scalar data types

PL/SQL Collections and Records , for information about composite data types and variables

3.3.3 Declaring Constants

A constant holds a value that does not change.

The information in " Declaring Variables " also applies to constant declarations, but a constant declaration has two more requirements: the keyword CONSTANT and the initial value of the constant. (The initial value of a constant is its permanent value.)

Example 3-12 Constant Declarations

This example declares three constants with scalar data types.

Related Topic

" Constant Declaration " for constant declaration syntax

3.3.4 Initial Values of Variables and Constants

In a variable declaration, the initial value is optional unless you specify the NOT NULL constraint . In a constant declaration, the initial value is required.

If the declaration is in a block or subprogram, the initial value is assigned to the variable or constant every time control passes to the block or subprogram. If the declaration is in a package specification, the initial value is assigned to the variable or constant for each session (whether the variable or constant is public or private).

To specify the initial value, use either the assignment operator ( := ) or the keyword DEFAULT , followed by an expression. The expression can include previously declared constants and previously initialized variables.

If you do not specify an initial value for a variable, assign a value to it before using it in any other context.

Example 3-13 Variable and Constant Declarations with Initial Values

This example assigns initial values to the constant and variables that it declares. The initial value of area depends on the previously declared constant pi and the previously initialized variable radius .

Example 3-14 Variable Initialized to NULL by Default

In this example, the variable counter has the initial value NULL , by default. The example uses the " IS [NOT] NULL Operator " to show that NULL is different from zero.

" Declaring Associative Array Constants " for information about declaring constant associative arrays

" Declaring Record Constants " for information about declaring constant records

" NOT NULL Constraint "

3.3.5 Declaring Items using the %TYPE Attribute

The %TYPE attribute lets you declare a data item of the same data type as a previously declared variable or column (without knowing what that type is). If the declaration of the referenced item changes, then the declaration of the referencing item changes accordingly.

The syntax of the declaration is:

For the kinds of items that can be referencing and referenced items, see " %TYPE Attribute " .

The referencing item inherits the following from the referenced item:

Data type and size

Constraints (unless the referenced item is a column)

The referencing item does not inherit the initial value of the referenced item. Therefore, if the referencing item specifies or inherits the NOT NULL constraint, you must specify an initial value for it.

The %TYPE attribute is particularly useful when declaring variables to hold database values. The syntax for declaring a variable of the same type as a column is:

" Declaring Items using the %ROWTYPE Attribute " , which lets you declare a record variable that represents either a full or partial row of a database table or view

Example 3-15 Declaring Variable of Same Type as Column

In this example, the variable surname inherits the data type and size of the column employees . last_name , which has a NOT NULL constraint. Because surname does not inherit the NOT NULL constraint, its declaration does not need an initial value.

Example 3-16 Declaring Variable of Same Type as Another Variable

In this example, the variable surname inherits the data type, size, and NOT NULL constraint of the variable name . Because surname does not inherit the initial value of name , its declaration needs an initial value (which cannot exceed 25 characters).

3.4 References to Identifiers

When referencing an identifier, you use a name that is either simple, qualified, remote, or both qualified and remote.

The simple name of an identifier is the name in its declaration. For example:

If an identifier is declared in a named PL/SQL unit, you can (and sometimes must) reference it with its qualified name . The syntax (called dot notation ) is:

For example, if package p declares identifier a , you can reference the identifier with the qualified name p . a . The unit name also can (and sometimes must) be qualified. You must qualify an identifier when it is not visible (see " Scope and Visibility of Identifiers " ).

If the identifier names an object on a remote database, you must reference it with its remote name . The syntax is:

If the identifier is declared in a PL/SQL unit on a remote database, you must reference it with its qualified remote name . The syntax is:

You can create synonyms for remote schema objects, but you cannot create synonyms for objects declared in PL/SQL subprograms or packages. To create a synonym, use the SQL statement CREATE SYNONYM , explained in Oracle Database SQL Language Reference .

For information about how PL/SQL resolves ambiguous names, see PL/SQL Name Resolution .

You can reference identifiers declared in the packages STANDARD and DBMS_STANDARD without qualifying them with the package names, unless you have declared a local identifier with the same name (see " Scope and Visibility of Identifiers " ).

3.5 Scope and Visibility of Identifiers

The scope of an identifier is the region of a PL/SQL unit from which you can reference the identifier. The visibility of an identifier is the region of a PL/SQL unit from which you can reference the identifier without qualifying it. An identifier is local to the PL/SQL unit that declares it. If that unit has subunits, the identifier is global to them.

If a subunit redeclares a global identifier, then inside the subunit, both identifiers are in scope, but only the local identifier is visible. To reference the global identifier, the subunit must qualify it with the name of the unit that declared it. If that unit has no name, then the subunit cannot reference the global identifier.

A PL/SQL unit cannot reference identifiers declared in other units at the same level, because those identifiers are neither local nor global to the block.

You cannot declare the same identifier twice in the same PL/SQL unit. If you do, an error occurs when you reference the duplicate identifier.

You can declare the same identifier in two different units. The two objects represented by the identifier are distinct. Changing one does not affect the other.

In the same scope, give labels and subprograms unique names to avoid confusion and unexpected results.

Example 3-17 Scope and Visibility of Identifiers

This example shows the scope and visibility of several identifiers. The first sub-block redeclares the global identifier a . To reference the global variable a , the first sub-block would have to qualify it with the name of the outer block—but the outer block has no name. Therefore, the first sub-block cannot reference the global variable a ; it can reference only its local variable a . Because the sub-blocks are at the same level, the first sub-block cannot reference d , and the second sub-block cannot reference c .

Example 3-18 Qualifying Redeclared Global Identifier with Block Label

This example labels the outer block with the name outer . Therefore, after the sub-block redeclares the global variable birthdate , it can reference that global variable by qualifying its name with the block label. The sub-block can also reference its local variable birthdate , by its simple name.

Example 3-19 Qualifying Identifier with Subprogram Name

In this example, the procedure check_credit declares a variable, rating , and a function, check_rating . The function redeclares the variable. Then the function references the global variable by qualifying it with the procedure name.

Example 3-20 Duplicate Identifiers in Same Scope

You cannot declare the same identifier twice in the same PL/SQL unit. If you do, an error occurs when you reference the duplicate identifier, as this example shows.

Example 3-21 Declaring Same Identifier in Different Units

You can declare the same identifier in two different units. The two objects represented by the identifier are distinct. Changing one does not affect the other, as this example shows. In the same scope, give labels and subprograms unique names to avoid confusion and unexpected results.

Example 3-22 Label and Subprogram with Same Name in Same Scope

In this example, echo is the name of both a block and a subprogram. Both the block and the subprogram declare a variable named x . In the subprogram, echo . x refers to the local variable x , not to the global variable x .

Example 3-23 Block with Multiple and Duplicate Labels

This example has two labels for the outer block, compute_ratio and another_label . The second label appears again in the inner block. In the inner block, another_label . denominator refers to the local variable denominator , not to the global variable denominator , which results in the error ZERO_DIVIDE .

3.6 Assigning Values to Variables

After declaring a variable, you can assign a value to it in these ways:

Use the assignment statement to assign it the value of an expression.

Use the SELECT INTO or FETCH statement to assign it a value from a table.

Pass it to a subprogram as an OUT or IN OUT parameter, and then assign the value inside the subprogram.

The variable and the value must have compatible data types. One data type is compatible with another data type if it can be implicitly converted to that type. For information about implicit data conversion, see Oracle Database SQL Language Reference .

Assigning Values to Variables with the Assignment Statement

Assigning Values to Variables with the SELECT INTO Statement

Assigning Values to Variables as Parameters of a Subprogram

Assigning Values to BOOLEAN Variables

" Assigning Values to Collection Variables "

" Assigning Values to Record Variables "

" FETCH Statement "

3.6.1 Assigning Values to Variables with the Assignment Statement

To assign the value of an expression to a variable, use this form of the assignment statement:

For the complete syntax of the assignment statement, see " Assignment Statement " .

For the syntax of an expression, see " Expression " .

Example 3-24 Assigning Values to Variables with Assignment Statement

This example declares several variables (specifying initial values for some) and then uses assignment statements to assign the values of expressions to them.

3.6.2 Assigning Values to Variables with the SELECT INTO Statement

A simple form of the SELECT INTO statement is:

For each select_item , there must be a corresponding, type-compatible variable_name . Because SQL does not have a BOOLEAN type, variable_name cannot be a BOOLEAN variable.

For the complete syntax of the SELECT INTO statement, see " SELECT INTO Statement " .

Example 3-25 Assigning Value to Variable with SELECT INTO Statement

This example uses a SELECT INTO statement to assign to the variable bonus the value that is 10% of the salary of the employee whose employee_id is 100.

3.6.3 Assigning Values to Variables as Parameters of a Subprogram

If you pass a variable to a subprogram as an OUT or IN OUT parameter, and the subprogram assigns a value to the parameter, the variable retains that value after the subprogram finishes running. For more information, see " Subprogram Parameters " .

Example 3-26 Assigning Value to Variable as IN OUT Subprogram Parameter

This example passes the variable new_sal to the procedure adjust_salary . The procedure assigns a value to the corresponding formal parameter, sal . Because sal is an IN OUT parameter, the variable new_sal retains the assigned value after the procedure finishes running.

3.6.4 Assigning Values to BOOLEAN Variables

The only values that you can assign to a BOOLEAN variable are TRUE , FALSE , and NULL .

For more information about the BOOLEAN data type, see " BOOLEAN Data Type " .

Example 3-27 Assigning Value to BOOLEAN Variable

This example initializes the BOOLEAN variable done to NULL by default, assigns it the literal value FALSE , compares it to the literal value TRUE , and assigns it the value of a BOOLEAN expression.

3.7 Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluates to a value.

An expression always returns a single value. The simplest expressions, in order of increasing complexity, are:

A single constant or variable (for example, a )

A unary operator and its single operand (for example, -a )

A binary operator and its two operands (for example, a+b )

An operand can be a variable, constant, literal, operator, function invocation, or placeholder—or another expression. Therefore, expressions can be arbitrarily complex. For expression syntax, see Expression .

The data types of the operands determine the data type of the expression. Every time the expression is evaluated, a single value of that data type results. The data type of that result is the data type of the expression.

Concatenation Operator

Operator Precedence

Logical Operators

Short-Circuit Evaluation

Comparison Operators

BOOLEAN Expressions

CASE Expressions

SQL Functions in PL/SQL Expressions

3.7.1 Concatenation Operator

The concatenation operator ( || ) appends one string operand to another.

The concatenation operator ignores null operands.

For more information about the syntax of the concatenation operator, see " character_expression ::= " .

Example 3-28 Concatenation Operator

Example 3-29 Concatenation Operator with NULL Operands

The concatenation operator ignores null operands, as this example shows.

3.7.2 Operator Precedence

An operation is either a unary operator and its single operand or a binary operator and its two operands. The operations in an expression are evaluated in order of operator precedence.

Table 3-3 shows operator precedence from highest to lowest. Operators with equal precedence are evaluated in no particular order.

Table 3-3 Operator Precedence

To control the order of evaluation, enclose operations in parentheses, as in Example 3-30 .

When parentheses are nested, the most deeply nested operations are evaluated first.

You can also use parentheses to improve readability where the parentheses do not affect evaluation order.

Example 3-30 Controlling Evaluation Order with Parentheses

Example 3-31 Expression with Nested Parentheses

In this example, the operations (1+2) and (3+4) are evaluated first, producing the values 3 and 7, respectively. Next, the operation 3*7 is evaluated, producing the result 21. Finally, the operation 21/7 is evaluated, producing the final value 3.

Example 3-32 Improving Readability with Parentheses

In this example, the parentheses do not affect the evaluation order. They only improve readability.

Example 3-33 Operator Precedence

This example shows the effect of operator precedence and parentheses in several more complex expressions.

3.7.3 Logical Operators

The logical operators AND , OR , and NOT follow a tri-state logic.

AND and OR are binary operators; NOT is a unary operator.

Table 3-4 Logical Truth Table

AND returns TRUE if and only if both operands are TRUE .

OR returns TRUE if either operand is TRUE .

NOT returns the opposite of its operand, unless the operand is NULL . NOT NULL returns NULL , because NULL is an indeterminate value.

Example 3-34 Procedure Prints BOOLEAN Variable

This example creates a procedure, print_boolean , that prints the value of a BOOLEAN variable. The procedure uses the " IS [NOT] NULL Operator " . Several examples in this chapter invoke print_boolean .

Example 3-35 AND Operator

As Table 3-4 and this example show, AND returns TRUE if and only if both operands are TRUE .

Example 3-36 OR Operator

As Table 3-4 and this example show, OR returns TRUE if either operand is TRUE . (This example invokes the print_boolean procedure from Example 3-34 .)

Example 3-37 NOT Operator

As Table 3-4 and this example show, NOT returns the opposite of its operand, unless the operand is NULL . NOT NULL returns NULL , because NULL is an indeterminate value. (This example invokes the print_boolean procedure from Example 3-34 .)

Example 3-38 NULL Value in Unequal Comparison

In this example, you might expect the sequence of statements to run because x and y seem unequal. But, NULL values are indeterminate. Whether x equals y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

Example 3-39 NULL Value in Equal Comparison

In this example, you might expect the sequence of statements to run because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

Example 3-40 NOT NULL Equals NULL

In this example, the two IF statements appear to be equivalent. However, if either x or y is NULL , then the first IF statement assigns the value of y to high and the second IF statement assigns the value of x to high .

Example 3-41 Changing Evaluation Order of Logical Operators

This example invokes the print_boolean procedure from Example 3-34 three times. The third and first invocation are logically equivalent—the parentheses in the third invocation only improve readability. The parentheses in the second invocation change the order of operation.

3.7.4 Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation . That is, PL/SQL stops evaluating the expression as soon as it can determine the result.

Therefore, you can write expressions that might otherwise cause errors.

In Example 3-42 , short-circuit evaluation prevents the OR expression from causing a divide-by-zero error. When the value of on_hand is zero, the value of the left operand is TRUE , so PL/SQL does not evaluate the right operand. If PL/SQL evaluated both operands before applying the OR operator, the right operand would cause a division by zero error.

Example 3-42 Short-Circuit Evaluation

3.7.5 Comparison Operators

Comparison operators compare one expression to another. The result is always either TRUE , FALSE , or NULL .

If the value of one expression is NULL , then the result of the comparison is also NULL .

The comparison operators are:

IS [NOT] NULL Operator

Relational Operators

LIKE Operator

BETWEEN Operator

IN Operator

Character comparisons are affected by NLS parameter settings, which can change at runtime. Therefore, character comparisons are evaluated at runtime, and the same character comparison can have different values at different times. For information about NLS parameters that affect character comparisons, see Oracle Database Globalization Support Guide .

Using CLOB values with comparison operators can create temporary LOB values. Ensure that your temporary tablespace is large enough to handle them.

3.7.5.1 IS [NOT] NULL Operator

The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL . The IS NOT NULL operator does the opposite.

Comparisons involving NULL values always yield NULL .

To test whether a value is NULL , use IF value IS NULL , as in these examples:

Example 3-14 , "Variable Initialized to NULL by Default"

Example 3-34 , "Procedure Prints BOOLEAN Variable"

Example 3-53 , "Searched CASE Expression with WHEN ... IS NULL"

3.7.5.2 Relational Operators

This table summarizes the relational operators.

Table 3-5 Relational Operators

Arithmetic Comparisons

BOOLEAN Comparisons

Character Comparisons

Date Comparisons

3.7.5.2.1 Arithmetic Comparisons

One number is greater than another if it represents a larger quantity.

Real numbers are stored as approximate values, so Oracle recommends comparing them for equality or inequality.

Example 3-43 Relational Operators in Expressions

This example invokes the print_boolean procedure from Example 3-35 to print the values of expressions that use relational operators to compare arithmetic values.

3.7.5.2.2 BOOLEAN Comparisons

By definition, TRUE is greater than FALSE . Any comparison with NULL returns NULL .

3.7.5.2.3 Character Comparisons

By default, one character is greater than another if its binary value is larger.

For example, this expression is true:

Strings are compared character by character. For example, this expression is true:

If you set the initialization parameter NLS_COMP=ANSI , string comparisons use the collating sequence identified by the NLS_SORT initialization parameter.

A collating sequence is an internal ordering of the character set in which a range of numeric codes represents the individual characters. One character value is greater than another if its internal numeric value is larger. Each language might have different rules about where such characters occur in the collating sequence. For example, an accented letter might be sorted differently depending on the database character set, even though the binary value is the same in each case.

By changing the value of the NLS_SORT parameter, you can perform comparisons that are case-insensitive and accent-insensitive.

A case-insensitive comparison treats corresponding uppercase and lowercase letters as the same letter. For example, these expressions are true:

To make comparisons case-insensitive, append _CI to the value of the NLS_SORT parameter (for example, BINARY_CI or XGERMAN_CI ).

An accent-insensitive comparison is case-insensitive, and also treats letters that differ only in accents or punctuation characters as the same letter. For example, these expressions are true:

To make comparisons both case-insensitive and accent-insensitive, append _AI to the value of the NLS_SORT parameter (for example, BINARY_AI or FRENCH_M_AI ).

Semantic differences between the CHAR and VARCHAR2 data types affect character comparisons.

For more information, see " Value Comparisons " .

3.7.5.2.4 Date Comparisons

One date is greater than another if it is more recent.

3.7.5.3 LIKE Operator

The LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not.

Case is significant.

The pattern can include the two wildcard characters underscore ( _ ) and percent sign (%).

Underscore matches exactly one character.

Percent sign ( % ) matches zero or more characters.

To search for the percent sign or underscore, define an escape character and put it before the percent sign or underscore.

Oracle Database SQL Language Reference for more information about LIKE

Oracle Database SQL Language Reference for information about REGEXP_LIKE , which is similar to LIKE

Example 3-44 LIKE Operator in Expression

The string 'Johnson' matches the pattern 'J%s_n' but not 'J%S_N' , as this example shows.

Example 3-45 Escape Character in Pattern

This example uses the backslash as the escape character, so that the percent sign in the string does not act as a wildcard.

3.7.5.4 BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range.

The value of the expression x BETWEEN a AND b is defined to be the same as the value of the expression (x>=a) AND (x<=b)  . The expression x will only be evaluated once.

Oracle Database SQL Language Reference for more information about BETWEEN

Example 3-46 BETWEEN Operator in Expressions

This example invokes the print_boolean procedure from Example 3-34 to print the values of expressions that include the BETWEEN operator.

3.7.5.5 IN Operator

The IN operator tests set membership.

x IN ( set ) returns TRUE only if x equals a member of set .

Oracle Database SQL Language Reference for more information about IN

Example 3-47 IN Operator in Expressions

This example invokes the print_boolean procedure from Example 3-34 to print the values of expressions that include the IN operator.

Example 3-48 IN Operator with Sets with NULL Values

This example shows what happens when set includes a NULL value. This invokes the print_boolean procedure from Example 3-34 .

3.7.6 BOOLEAN Expressions

A BOOLEAN expression is an expression that returns a BOOLEAN value— TRUE , FALSE , or NULL .

The simplest BOOLEAN expression is a BOOLEAN literal, constant, or variable. The following are also BOOLEAN expressions:

For a list of relational operators, see Table 3-5 . For the complete syntax of a BOOLEAN expression, see " boolean_expression ::= " .

Typically, you use BOOLEAN expressions as conditions in control statements (explained in PL/SQL Control Statements ) and in WHERE clauses of DML statements.

You can use a BOOLEAN variable itself as a condition; you need not compare it to the value TRUE or FALSE .

Example 3-49 Equivalent BOOLEAN Expressions

In this example, the conditions in the loops are equivalent.

3.7.7 CASE Expressions

Simple CASE Expression

Searched CASE Expression

3.7.7.1 Simple CASE Expression

For this explanation, assume that a simple CASE expression has this syntax:

The selector is an expression (typically a single variable). Each selector_value and each result can be either a literal or an expression. At least one result must not be the literal NULL .

The simple CASE expression returns the first result for which selector_value matches selector . Remaining expressions are not evaluated. If no selector_value matches selector , the CASE expression returns else_result if it exists and NULL otherwise.

" simple_case_expression ::= " for the complete syntax

Example 3-50 Simple CASE Expression

This example assigns the value of a simple CASE expression to the variable appraisal . The selector is grade .

Example 3-51 Simple CASE Expression with WHEN NULL

If selector has the value NULL , it cannot be matched by WHEN NULL , as this example shows.

Instead, use a searched CASE expression with WHEN boolean_expression IS NULL , as in Example 3-53 .

3.7.7.2 Searched CASE Expression

For this explanation, assume that a searched CASE expression has this syntax:

The searched CASE expression returns the first result for which boolean_expression is TRUE . Remaining expressions are not evaluated. If no boolean_expression is TRUE , the CASE expression returns else_result if it exists and NULL otherwise.

" searched_case_expression ::= " for the complete syntax

Example 3-52 Searched CASE Expression

This example assigns the value of a searched CASE expression to the variable appraisal .

Example 3-53 Searched CASE Expression with WHEN ... IS NULL

This example uses a searched CASE expression to solve the problem in Example 3-51 .

3.7.8 SQL Functions in PL/SQL Expressions

In PL/SQL expressions, you can use all SQL functions except:

Aggregate functions (such as AVG and COUNT )

Aggregate function JSON_ARRAYAGG

Aggregate function JSON_DATAGUIDE

Aggregate function JSON_MERGEPATCH

Aggregate function JSON_OBJECTAGG

JSON_TRANSFORM

JSON condition JSON_TEXTCONTAINS

Analytic functions (such as LAG and RATIO_TO_REPORT )

Conversion function BIN_TO_NUM

Data mining functions (such as CLUSTER_ID and FEATURE_VALUE )

Encoding and decoding functions (such as DECODE and DUMP )

Model functions (such as ITERATION_NUMBER and PREVIOUS )

Object reference functions (such as REF and VALUE )

XML functions

These collation SQL operators and functions:

COLLATE operator

COLLATION function

NLS_COLLATION_ID function

NLS_COLLATION_NAME function

These miscellaneous functions:

DATAOBJ_TO_PARTITION

SYS_CONNECT_BY_PATH

WIDTH_BUCKET

PL/SQL supports an overload of BITAND for which the arguments and result are BINARY_INTEGER .

When used in a PL/SQL expression, the RAWTOHEX function accepts an argument of data type RAW and returns a VARCHAR2 value with the hexadecimal representation of bytes that comprise the value of the argument. Arguments of types other than RAW can be specified only if they can be implicitly converted to RAW . This conversion is possible for CHAR , VARCHAR2 , and LONG values that are valid arguments of the HEXTORAW function, and for LONG RAW and BLOB values of up to 16380 bytes.

3.7.9 Static Expressions

A static expression is an expression whose value can be determined at compile time—that is, it does not include character comparisons, variables, or function invocations. Static expressions are the only expressions that can appear in conditional compilation directives.

Definition of Static Expression

An expression is static if it is the NULL literal.

An expression is static if it is a character, numeric, or boolean literal.

An expression is static if it is a reference to a static constant.

An expression is static if it is a reference to a conditional compilation variable begun with $$ .

An expression is static if it is an operator is allowed in static expressions, if all of its operands are static, and if the operator does not raise an exception when it is evaluated on those operands.

Table 3-6 Operators Allowed in Static Expressions

This list shows functions allowed in static expressions.

IS [NOT] INFINITE

IS [NOT] NAN

TO_BINARY_DOUBLE

TO_BINARY_FLOAT

Static expressions can be used in the following subtype declarations:

Length of string types ( VARCHAR2, NCHAR, CHAR, NVARCHAR2, RAW , and the ANSI equivalents)

Scale and precision of NUMBER types and subtypes such as FLOAT

Interval type precision (year, month ,second)

Time and Timestamp precision

VARRAY bounds

Bounds of ranges in type declarations

In each case, the resulting type of the static expression must be the same as the declared item subtype and must be in the correct range for the context.

PLS_INTEGER Static Expressions

BOOLEAN Static Expressions

VARCHAR2 Static Expressions

Static Constants

" Expressions " for general information about expressions

3.7.9.1 PLS_INTEGER Static Expressions

PLS_INTEGER static expressions are:

PLS_INTEGER literals

For information about literals, see " Literals " .

PLS_INTEGER static constants

For information about static constants, see " Static Constants " .

" PLS_INTEGER and BINARY_INTEGER Data Types " for information about the PLS_INTEGER data type

3.7.9.2 BOOLEAN Static Expressions

BOOLEAN static expressions are:

BOOLEAN literals ( TRUE , FALSE , or NULL )

BOOLEAN static constants

Where x and y are PLS_INTEGER static expressions:

x <> y

For information about PLS_INTEGER static expressions, see " PLS_INTEGER Static Expressions " .

Where x and y are BOOLEAN expressions:

For information about BOOLEAN expressions, see " BOOLEAN Expressions " .

Where x is a static expression:

x IS NOT NULL

For information about static expressions, see " Static Expressions " .

" BOOLEAN Data Type " for information about the BOOLEAN data type

3.7.9.3 VARCHAR2 Static Expressions

VARCHAR2 static expressions are:

String literal with maximum size of 32,767 bytes

TO_CHAR(x) , where x is a PLS_INTEGER static expression

For information about the TO_CHAR function, see Oracle Database SQL Language Reference .

TO_CHAR(x , f , n) where x is a PLS_INTEGER static expression and f and n are VARCHAR2 static expressions

x || y where x and y are VARCHAR2 or PLS_INTEGER static expressions

" CHAR and VARCHAR2 Variables " for information about the VARCHAR2 data type

3.7.9.4 Static Constants

A static constant is declared in a package specification with this syntax:

The type of static_expression must be the same as data_type (either BOOLEAN or PLS_INTEGER ).

The static constant must always be referenced as package_name . constant_name , even in the body of the package_name package.

If you use constant_name in the BOOLEAN expression in a conditional compilation directive in a PL/SQL unit, then the PL/SQL unit depends on the package package_name . If you alter the package specification, the dependent PL/SQL unit might become invalid and need recompilation (for information about the invalidation of dependent objects, see Oracle Database Development Guide ).

If you use a package with static constants to control conditional compilation in multiple PL/SQL units, Oracle recommends that you create only the package specification, and dedicate it exclusively to controlling conditional compilation. This practice minimizes invalidations caused by altering the package specification.

To control conditional compilation in a single PL/SQL unit, you can set flags in the PLSQL_CCFLAGS compilation parameter. For information about this parameter, see " Assigning Values to Inquiry Directives " and Oracle Database Reference .

" Declaring Constants " for general information about declaring constants

PL/SQL Packages for more information about packages

Oracle Database Development Guide for more information about schema object dependencies

Example 3-54 Static Constants

In this example, the package my_debug defines the static constants debug and trace to control debugging and tracing in multiple PL/SQL units. The procedure my_proc1 uses only debug , and the procedure my_proc2 uses only trace , but both procedures depend on the package. However, the recompiled code might not be different. For example, if you only change the value of debug to FALSE and then recompile the two procedures, the compiled code for my_proc1 changes, but the compiled code for my_proc2 does not.

3.8 Error-Reporting Functions

PL/SQL has two error-reporting functions, SQLCODE and SQLERRM , for use in PL/SQL exception-handling code.

For their descriptions, see " SQLCODE Function " and " SQLERRM Function " .

You cannot use the SQLCODE and SQLERRM functions in SQL statements.

3.9 Conditional Compilation

Conditional compilation lets you customize the functionality of a PL/SQL application without removing source text.

For example, you can:

Use new features with the latest database release and disable them when running the application in an older database release.

Activate debugging or tracing statements in the development environment and hide them when running the application at a production site.

How Conditional Compilation Works

Conditional Compilation Examples

Retrieving and Printing Post-Processed Source Text

Conditional Compilation Directive Restrictions

3.9.1 How Conditional Compilation Works

Conditional compilation uses selection directives, which are similar to IF statements, to select source text for compilation.

The condition in a selection directive usually includes an inquiry directive. Error directives raise user-defined errors. All conditional compilation directives are built from preprocessor control tokens and PL/SQL text.

Preprocessor Control Tokens

Selection Directives

Error Directives

Inquiry Directives

DBMS_DB_VERSION Package

3.9.1.1 Preprocessor Control Tokens

A preprocessor control token identifies code that is processed before the PL/SQL unit is compiled.

There cannot be space between $ and plsql_identifier .

The character $ can also appear inside plsql_identifier , but it has no special meaning there.

These preprocessor control tokens are reserved:

For information about plsql_identifier , see " Identifiers " .

3.9.1.2 Selection Directives

A selection directive selects source text to compile.

For the syntax of boolean_static_expression , see " BOOLEAN Static Expressions " . The text can be anything, but typically, it is either a statement (see " statement ::= " ) or an error directive (explained in " Error Directives " ).

The selection directive evaluates the BOOLEAN static expressions in the order that they appear until either one expression has the value TRUE or the list of expressions is exhausted. If one expression has the value TRUE , its text is compiled, the remaining expressions are not evaluated, and their text is not analyzed. If no expression has the value TRUE , then if $ELSE is present, its text is compiled; otherwise, no text is compiled.

For examples of selection directives, see " Conditional Compilation Examples " .

" Conditional Selection Statements " for information about the IF statement, which has the same logic as the selection directive

3.9.1.3 Error Directives

An error directive produces a user-defined error message during compilation.

It produces this compile-time error message, where string is the value of varchar2_static_expression :

For the syntax of varchar2_static_expression , see " VARCHAR2 Static Expressions " .

For an example of an error directive, see Example 3-58 .

3.9.1.4 Inquiry Directives

An inquiry directive provides information about the compilation environment.

For information about name , which is an unquoted PL/SQL identifier, see " Identifiers " .

An inquiry directive typically appears in the boolean_static_expression of a selection directive, but it can appear anywhere that a variable or literal of its type can appear. Moreover, it can appear where regular PL/SQL allows only a literal (not a variable)—for example, to specify the size of a VARCHAR2 variable.

Predefined Inquiry Directives

Assigning Values to Inquiry Directives

Unresolvable Inquiry Directives

3.9.1.4.1 Predefined Inquiry Directives

The predefined inquiry directives are:

$$PLSQL_LINE

A PLS_INTEGER literal whose value is the number of the source line on which the directive appears in the current PL/SQL unit. An example of $$PLSQL_LINE in a selection directive is:

$$PLSQL_UNIT

A VARCHAR2 literal that contains the name of the current PL/SQL unit. If the current PL/SQL unit is an anonymous block, then $$PLSQL_UNIT contains a NULL value.

$$PLSQL_UNIT_OWNER

A VARCHAR2 literal that contains the name of the owner of the current PL/SQL unit. If the current PL/SQL unit is an anonymous block, then $$PLSQL_UNIT_OWNER contains a NULL value.

$$PLSQL_UNIT_TYPE

A VARCHAR2 literal that contains the type of the current PL/SQL unit— ANONYMOUS BLOCK , FUNCTION , PACKAGE , PACKAGE BODY , PROCEDURE , TRIGGER , TYPE , or TYPE BODY . Inside an anonymous block or non-DML trigger, $$PLSQL_UNIT_TYPE has the value ANONYMOUS BLOCK .

$$ plsql_compilation_parameter

The name plsql_compilation_parameter is a PL/SQL compilation parameter (for example, PLSCOPE_SETTINGS ). For descriptions of these parameters, see Table 2-2 .

Because a selection directive needs a BOOLEAN static expression, you cannot use $$PLSQL_UNIT , $$PLSQL_UNIT_OWNER , or $$PLSQL_UNIT_TYPE in a VARCHAR2 comparison such as:

However, you can compare the preceding directives to NULL . For example:

Example 3-55 Predefined Inquiry Directives

In this example, a SQL*Plus script, uses several predefined inquiry directives as PLS_INTEGER and VARCHAR2 literals to show how their values are assigned.

Example 3-56 Displaying Values of PL/SQL Compilation Parameters

This example displays the current values of PL/SQL the compilation parameters.

In the SQL*Plus environment, you can display the current values of initialization parameters, including the PL/SQL compilation parameters, with the command SHOW PARAMETERS . For more information about the SHOW command and its PARAMETERS option, see SQL*Plus User's Guide and Reference .

3.9.1.4.2 Assigning Values to Inquiry Directives

You can assign values to inquiry directives with the PLSQL_CCFLAGS compilation parameter.

For example:

Each value must be either a BOOLEAN literal ( TRUE , FALSE , or NULL ) or PLS_INTEGER literal. The data type of value determines the data type of name .

The same name can appear multiple times, with values of the same or different data types. Later assignments override earlier assignments. For example, this command sets the value of $$flag to 5 and its data type to PLS_INTEGER :

Oracle recommends against using PLSQL_CCFLAGS to assign values to predefined inquiry directives, including compilation parameters. To assign values to compilation parameters, Oracle recommends using the ALTER SESSION statement.

For more information about the ALTER SESSION statement, see Oracle Database SQL Language Reference .

The compile-time value of PLSQL_CCFLAGS is stored with the metadata of stored PL/SQL units, which means that you can reuse the value when you explicitly recompile the units. For more information, see " PL/SQL Units and Compilation Parameters " .

For more information about PLSQL_CCFLAGS , see Oracle Database Reference .

Example 3-57 PLSQL_CCFLAGS Assigns Value to Itself

This example uses PLSQL_CCFLAGS to assign a value to the user-defined inquiry directive $$Some_Flag and (though not recommended) to itself. Because later assignments override earlier assignments, the resulting value of $$Some_Flag is 2 and the resulting value of PLSQL_CCFLAGS is the value that it assigns to itself (99), not the value that the ALTER SESSION statement assigns to it ( 'Some_Flag:1, Some_Flag:2, PLSQL_CCFlags:99' ).

3.9.1.4.3 Unresolvable Inquiry Directives

If the source text is not wrapped, PL/SQL issues a warning if the value of an inquiry directive cannot be determined.

If an inquiry directive ( $$ name ) cannot be resolved, and the source text is not wrapped, then PL/SQL issues the warning PLW-6003 and substitutes NULL for the value of the unresolved inquiry directive. If the source text is wrapped, the warning message is disabled, so that the unresolved inquiry directive is not revealed.

For information about wrapping PL/SQL source text, see PL/SQL Source Text Wrapping .

3.9.1.5 DBMS_DB_VERSION Package

The DBMS_DB_VERSION package specifies the Oracle version numbers and other information useful for simple conditional compilation selections based on Oracle versions.

The DBMS_DB_VERSION package provides these static constants:

The PLS_INTEGER constant VERSION identifies the current Oracle Database version.

The PLS_INTEGER constant RELEASE identifies the current Oracle Database release number.

Each BOOLEAN constant of the form VER_LE_ v has the value TRUE if the database version is less than or equal to v ; otherwise, it has the value FALSE .

Each BOOLEAN constant of the form VER_LE_ v_r has the value TRUE if the database version is less than or equal to v and release is less than or equal to r ; otherwise, it has the value FALSE .

For more information about the DBMS_DB_VERSION package, see Oracle Database PL/SQL Packages and Types Reference .

3.9.2 Conditional Compilation Examples

Examples of conditional compilation using selection and user-defined inquiry directives.

Example 3-58 Code for Checking Database Version

This example generates an error message if the database version and release is less than Oracle Database 10g release 2; otherwise, it displays a message saying that the version and release are supported and uses a COMMIT statement that became available at Oracle Database 10g release 2.

Example 3-59 Compiling Different Code for Different Database Versions

This example sets the values of the user-defined inquiry directives $$my_debug and $$my_tracing and then uses conditional compilation:

In the specification of package my_pkg , to determine the base type of the subtype my_real ( BINARY_DOUBLE is available only for Oracle Database versions 10g and later.)

In the body of package my_pkg , to compute the values of my_pi and my_e differently for different database versions

In the procedure circle_area , to compile some code only if the inquiry directive $$my_debug has the value TRUE .

3.9.3 Retrieving and Printing Post-Processed Source Text

The DBMS_PREPROCESSOR package provides subprograms that retrieve and print the source text of a PL/SQL unit in its post-processed form.

For information about the DBMS_PREPROCESSOR package, see Oracle Database PL/SQL Packages and Types Reference .

Example 3-60 Displaying Post-Processed Source Textsource text

This example invokes the procedure DBMS_PREPROCESSOR . PRINT_POST_PROCESSED_SOURCE to print the post-processed form of my_pkg (from " Example 3-59 " ). Lines of code in " Example 3-59 " that are not included in the post-processed text appear as blank lines.

3.9.4 Conditional Compilation Directive Restrictions

Conditional compilation directives are subject to these semantic restrictions.

A conditional compilation directive cannot appear in the specification of a schema-level user-defined type (created with the " CREATE TYPE Statement " ). This type specification specifies the attribute structure of the type, which determines the attribute structure of dependent types and the column structure of dependent tables.

Using a conditional compilation directive to change the attribute structure of a type can cause dependent objects to "go out of sync" or dependent tables to become inaccessible. Oracle recommends that you change the attribute structure of a type only with the " ALTER TYPE Statement " . The ALTER TYPE statement propagates changes to dependent objects.

If a conditional compilation directive is used in a schema-level type specification, the compiler raises the error PLS-00180: preprocessor directives are not supported in this context.

As all conditional compiler constructs are processed by the PL/SQL preprocessor, the SQL Parser imposes the following restrictions on the location of the first conditional compilation directive in a stored PL/SQL unit or anonymous block:

In a package specification, a package body, a type body, a schema-level function and in a schema-level procedure, at least one nonwhitespace PL/SQL token must appear after the identifier of the unit name before a conditional compilation directive is valid.

The PL/SQL comments, "--" or "/*", are counted as whitespace tokens.

If the token is invalid in PL/SQL, then a PLS-00103 error is issued. But if a conditional compilation directive is used in violation of this rule, then an ORA error is produced.

Example 3-61 and Example 3-62 , show that the first conditional compilation directive appears after the first PL/SQL token that follows the identifier of the unit being defined.

In a trigger or an anonymous block, the first conditional compilation directive cannot appear before the keyword DECLARE or BEGIN , whichever comes first.

The SQL parser also imposes this restriction: If an anonymous block uses a placeholder, the placeholder cannot appear in a conditional compilation directive. For example:

Example 3-61 Using Conditional Compilation Directive in the Definition of a Package Specification

This example shows the placement of the first conditional compilation directive after an AUTHID clause, but before the keyword IS , in the definition of the package specification.

Example 3-62 Using Conditional Compilation Directive in the Formal Parameter List of a Subprogram

This example shows the placement of the first conditional compilation directive after the left parenthesis, in the formal parameter list of a PL/SQL procedure definition.

• PL/SQL Tutorial
• PL/SQL - Home
• PL/SQL - Overview
• PL/SQL - Environment
• PL/SQL - Basic Syntax
• PL/SQL - Data Types
• PL/SQL - Variables
• PL/SQL - Constants and Literals

PL/SQL - Operators

• PL/SQL - Conditions
• PL/SQL - Loops
• PL/SQL - Strings
• PL/SQL - Arrays
• PL/SQL - Procedures
• PL/SQL - Functions
• PL/SQL - Cursors
• PL/SQL - Records
• PL/SQL - Exceptions
• PL/SQL - Triggers
• PL/SQL - Packages
• PL/SQL - Collections
• PL/SQL - Transactions
• PL/SQL - Date & Time
• PL/SQL - DBMS Output
• PL/SQL - Object Oriented
• PL/SQL Useful Resources
• PL/SQL - Questions and Answers
• PL/SQL - Quick Guide
• PL/SQL - Useful Resources
• PL/SQL - Discussion
• Selected Reading
• UPSC IAS Exams Notes
• Developer's Best Practices
• Questions and Answers
• Effective Resume Writing
• HR Interview Questions
• Computer Glossary

In this chapter, we will discuss operators in PL/SQL. An operator is a symbol that tells the compiler to perform specific mathematical or logical manipulation. PL/SQL language is rich in built-in operators and provides the following types of operators −

• Arithmetic operators
• Relational operators
• Comparison operators
• Logical operators
• String operators

Here, we will understand the arithmetic, relational, comparison and logical operators one by one. The String operators will be discussed in a later chapter − PL/SQL - Strings .

Arithmetic Operators

Following table shows all the arithmetic operators supported by PL/SQL. Let us assume variable A holds 10 and variable B holds 5, then −

Show Examples

Relational Operators

Relational operators compare two expressions or values and return a Boolean result. Following table shows all the relational operators supported by PL/SQL. Let us assume variable A holds 10 and variable B holds 20, then −

Comparison Operators

Comparison operators are used for comparing one expression to another. The result is always either TRUE, FALSE or NULL .

Logical Operators

Following table shows the Logical operators supported by PL/SQL. All these operators work on Boolean operands and produce Boolean results. Let us assume variable A holds true and variable B holds false, then −

PL/SQL Operator Precedence

Operator precedence determines the grouping of terms in an expression. This affects how an expression is evaluated. Certain operators have higher precedence than others; for example, the multiplication operator has higher precedence than the addition operator.

For example, x = 7 + 3 * 2 ; here, x is assigned 13 , not 20 because operator * has higher precedence than +, so it first gets multiplied with 3*2 and then adds into 7 .

Here, operators with the highest precedence appear at the top of the table, those with the lowest appear at the bottom. Within an expression, higher precedence operators will be evaluated first.

The precedence of operators goes as follows: =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN.

To Continue Learning Please Login

• SQL Cheat Sheet
• SQL Interview Questions
• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database
• How to open an XML file ?
• How to Restore a Dump File in SQL?
• How to Rename a Column in PL/SQL?
• How to Find Duplicate Rows in PL/SQL
• How to Restore a Dump File in SQL Server
• How to Open CSV File Online?
• How to Pagination in PL/SQL?
• How to Get PL/SQL Database Size?
• How to Get the Day of the Week for PL/SQL?
• How to Edit Text Files in Linux?
• PL/SQL For Loop
• PL/SQL Loops
• How to Check If a Table Exist in PL/SQL?
• How to Execute a SQL Script File using JDBC?
• How to open a file using the with statement
• How to Export a Table Data to a PDF File in SQL?
• Open a File in Python
• How to open and close a file in Python
• How to set up a PHP Console ?

How to Open a PL/SQL File?

In database management and application development, PL/SQL (Procedural Language/Structured Query Language) files play an important role. These files contain stored procedures , functions , triggers, and other programmatic constructs essential for Oracle database systems.

Opening a PL/SQL file is the first step towards understanding and modifying its contents. In this article, we learn about How to Open a PL/SQL File with the help of various methods in detail.

Opening a PL/SQL file involves accessing its contents for viewing, editing or executing SQL commands and programmatic logic. Depending on our workflow and the tools we have, we can choose from several methods to solve this task. Below is the method which helps us to explain How to Open a PL/SQL File as follows:

• Using SQL*Plus
• Using SQL Developer
• Using a Text Editor

1. Using SQL*Plus

• SQLPlus, Oracle’s command-line interface for executing SQL commands and PL/SQL scripts, is a Common tool among Oracle database professionals.
• To open a PL/SQL file in SQLPlus, simply launch the application, connect to the desired database, and use the @command to execute the script.

This command executes the PL/SQL script located at the specified file path.

2. Using SQL Developer

• Oracle SQL Developer provides a user-friendly Integrated Development Environment ( IDE ) for Oracle database development.
• To open a PL/SQL file in SQL Developer, navigate to the File menu, select Open and choose the desired file from your file system. SQL Developer provides syntax highlighting, code completion and debugging capabilities, enhancing the development experience.
• Click on File -> Open -> File.
• Select your PL/SQL file from the file explorer and click Open.

3. Using a Text Editor

• For developers who prefer lightweight and customizable text editors, opening PL/SQL files in tools like Sublime Text, Visual Studio Code, or Notepad++ is a common choice. Simply open the text editor, navigate to File -> Open and select the PL/SQL file from your file system.
• While text editors lack database-specific features, they offer flexibility and extensibility through plugins and customization options.
• Open your preferred text editor.
• Navigate to File -> Open and select your PL/SQL file.

Overall, Opening a PL/SQL file is a fundamental task for Oracle database administrators and developers. By using the methods discussed in this article such as SQLPlus, SQL Developer, text editors. You can easily access and manage PL/SQL files according to your preferences and requirements. Whether you prefer simplicity, functionality or integration with other development tools, there’s a method tailored to your workflow. Experiment with different approaches to discover the most efficient and comfortable way to work with PL/SQL files in your Oracle database environment.

Please Login to comment...

Similar reads, improve your coding skills with practice.

What kind of Experience do you want to share?