assignment in oracle

Home » PL/SQL Tutorial » PL/SQL Variables

PL/SQL Variables

Summary : in this tutorial, you will learn about PL/SQL variables and how to use them effectively.

In PL/SQL, a variable is named storage location that stores a value of a particular data type . The value of the variable changes through the program. Before using a variable, you must declare it in the declaration section of a block .

Declaring variables

The syntax for a variable declaration is as follows:

In this syntax:

• First, specify the name of the variable. The name of the variable should be as descriptive as possible, e.g., l_total_sales , l_credit_limit , and l_sales_revenue .
• Second, choose an appropriate data type for the variable, depending on the kind of value that you want to store, for example, number, character, Boolean, and datetime.

By convention, local variable names should start with l_ and global variable names should have a prefix of g_ .

The following example declares three variables l_total_sales , l_credit_limit , and l_contact_name :

Default values

PL/SQL allows you to set a default value for a variable at the declaration time. To assign a default value to a variable, you use the assignment operator ( := ) or the DEFAULT keyword.

The following example declares a variable named l_product_name with an initial value 'Laptop' :

It is equivalent to the following block:

In this example, instead of using the assignment operator := , we used the DEFAULT keyword to initialize a variable.

NOT NULL constraint

If you impose the NOT NULL constraint on a value, then the variable cannot accept a NULL value. Besides, a variable declared with the NOT NULL must be initialized with a non-null value. Note that PL/SQL treats a zero-length string as a NULL value.

The following example first declares a variable named l_shipping_status with the NOT NULL constraint. Then, it assigns the variable a zero-length string.

PL/SQL issued the following error:

Because the variable l_shipping_status declared with the NOT NULL constraint, it could not accept a NULL value or zero-length string in this case.

Variable assignments

To assign a value to a variable, you use the assignment operator ( := ), for example:

You can assign a value of a variable to another as shown in the following example:

Anchored declarations

Typically, you declare a variable and select a value from a table column for this variable. If the data type of the table column changes, you must adjust the program to make it work with the new type.

PL/SQL allows you to declare a variable whose data type anchor to a table column or another variable. Consider the following example:

In this example:

• First,  declare two variables l_customer_name and l_credit_limit whose data type anchors to the name and credit_limit columns respectively, in the declaration section of the block.
• Second, query the customer name and credit limit of the customer id 38 and assign these column values to the l_customer_name and l_credit_limit variables in the execution block.
• Third, display the customer name and credit limit.

PL/SQL returned the following output:

This example illustrates how to declare variables that anchor to another variable:

Here is the output:

Now, you should know how to use PL/SQL variables in your block and manipulate them efficiently.

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Simple Oracle variable SQL Assignment

Despite having spent an hour researching I can't seem to figure out how to correctly define a variable and then use it in your SQL.

This is what I have so far produced:

DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy');

of which I get the reply:

ORA-06550: line 1, column 63: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function package pragma procedure subtype type use form current cursor Details: DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy'); Error at line 1 ORA-06550: line 1, column 63: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function package pragma procedure subtype type use form current cursor

I'd love to find out how to do such a simple task!

• 3 Variables are a client construct. You need to tell us what client you are using. –  APC Commented Nov 7, 2011 at 17:39

5 Answers 5

Your variable declaration is correct.

The DECLARE keyword is used to define variables scoped in a PL/SQL block (whose body is delimited by BEGIN and END; ). How do you want to use this variable?

The following PL/SQL works fine for me:

You can also use the DEFINE statement to use simple string substitution variables. They are suitable for a client like SQL/PLUS or TOAD.

• 4 So is there no way to do it succinctly like in MS SQL where it's a simple declare @varible set @variable ? I'd like to later use the variable in a WHERE clause, how would I do that? How would I also print the variable to screen? –  m.edmondson Commented Nov 7, 2011 at 17:14
• I'm not familiar with MS SQL, but if you're referring to some kind of global variable, it's possible to define a variable in a package, thus making it visible to all the package's members, even though global variables are something you'll probably want to avoid. You can print the variable to screen using dbms_output.put_line . –  Xavi López Commented Nov 7, 2011 at 17:22
• Not a global variable - just a normal variable that I can assign and then use later on –  m.edmondson Commented Nov 7, 2011 at 18:21
• I've been about to post this before but wasn't sure this is what you wanted. You can also use the DEFINE statement to interpolate simple string substitution variables. See my updated on the answer for an example. –  Xavi López Commented Nov 7, 2011 at 18:57

To accomplish what you're attempting in Toad, you don't need to declare the variable at all. Simply include your variable prefaced with a colon and Toad will prompt you for the variable's value when you execute the query. For example:

If this doesn't work initially, right-click anywhere in the editor and make sure "Prompt for Substitution Variables" is checked.

If you really want to do it similarly to the way SQL Server handles variables (or you want to be able to do the same thing in SQL*Plus), you can write it as follows:

However, to make this work in Toad, you'll need to run it through "Execute as script", rather than the typical "Execute statement" command.

Take in mind that Oracle's PL/SQL is not SQL.

PL/SQL is a procedural language. SQL is not procedural, but you can define "variables" the user can enter via the "&var" syntax (see http://www.orafaq.com/node/515 ).

This is an old post, but in case anyone stumbles on this (as I just did), you can handle this with a CTE:

Almost the same syntax works in SQL Server (minus the date -specific stuff and from dual ).

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged oracle variables plsql toad or ask your own question .

• The Overflow Blog
• Scaling systems to manage all the metadata ABOUT the data
• Navigating cities of code with Norris Numbers
• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Bringing clarity to status tag usage on meta sites
• Tag hover experiment wrap-up and next steps

Hot Network Questions

• Relative pronouns that don't start the dependent clause
• Why would luck magic become obsolete in the modern era?
• Why did Borland ignore the Macintosh market?
• can a CPU once removed retain information that poses a security concern?
• What is this fruit from Cambodia? (orange skin, 3cm, orange juicy flesh, very stick white substance)
• What was I thinking when I drew this diagram?
• A post-apocalyptic short story where very sick people from our future save people in our timeline that would be killed in plane crashes
• How to fix fncychap changing appendix cref name to "chapter"?
• How can one design earplugs so that they provide protection from loud noises, such as explosions or gunfire, while still allowing user to hear voices?
• I need to better understand this clause in an independent contract agreement for Waiverability:
• Will I have to disclose traffic tickets to immigration (general)
• Did Newton predict the deflection of light by gravity?
• Are the US or its European allies offering Iran anything in return for de-escalation?
• I can't hear you
• How to deal with a boss that is using non-related arguments in a dissent?
• Unstable output C++: running the same thing twice gives different output
• Is there a limit to how much power could be transmitted wirelessly?
• Are there rules of when there is linking-sound compound words?
• How can I cross an overpass in "Street View" without being dropped to the roadway below?
• Would a manned Mars landing be possible with Apollo-era technology?
• PF2e Remaster: can Trip now be used with Finesse?
• How do I loosen this nut of my toilet lid?
• Why did evolution fail to protect humans against sun?
• Why are the perfect fifth and fourth called "perfect" in 12-ET when they differ by approximately 2 cents from just intonation?

• Manage VIP Account
• Register for VIP Plan
• VIP Member-Only Content
• HCM Data Loader
• HCM Extract
• BI Publisher
• Fast Formula
• OTBI Analytics
• Personalizations
• Scheduled Processes
• Absence Management
• Performance Management
• Talent Management
• Time & Labor
• HCM Extracts Questions
• HCM Data Loader Questions
• BI Reports Questions
• Report Issues/suggestions
• Your Feedback counts
• Write for Us
• Privacy Policy
• Join Telegram Group
• Join LinkedIn Group
• Join Facebook Page

Assignment Type and its importance in HCM Cloud

• Post category: Core HR
• Post comments: 0 Comments
• Post last modified: July 1, 2021
• Reading time: 4 mins read

In this article, we will look into the Assignment Types available in HCM Cloud and how to use them in the reports.

Each worker type is denoted by its alphabet value in the ASSIGNMENT_TYPE and PERIOD_TYPE columns of the PER_ALL_ASSIGNMENTS_M and PER_PERIODS_OF_SERVICE tables respectively. Both ASSIGNMENT_TYPE and PERIOD_TYPE should have the same values for the given work terms record.

For example, pending worker is denoted by P, employee by E, contingent worker by C, nonworker by N, and offer by O.

Both Assignment and Work Terms information will be stored in PER_ALL_ASSIGNMENTS_M table with a different ASSIGNMENT_TYPE.

Now, if you are building reports based on the Assignment table, we should use the right Assignment types.

If we have to pull Employees only, we need to use ASSIGNMENT_TYPE = ‘E’. If you use ASSIGNMENT_TYPE <>’ET’ then this query might work if you ONLY have Employees in the client POD without any contingent workers and pending workers, else it will pull additional employees on the report.

I have seen many cases where the developers used ASSIGNMENT_TYPE NOT IN (‘ET’,’CT’).. In this case, it will pull Employees, Contingent Workers, Non-Workers, and Offers. Instead of writing like this, it is better to rewrite it as ASSIGNMENT_TYPE IN (‘E’,’C’) if there is a need for pulling Employees and Contingent Workers.

You Might Also Like

How to configure Global Search in Oracle Cloud Applications?

SQL query to pull the EEO Information

How to Mass Download Document of Records from 21D?

Session expired

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.

• Tables and Views for HCM

PER_ASSIGNMENT_STATUS_TYPES

This table holds pre-defined and user defined status types (for Assignments or Sets of Employment/Placement Terms). Current seeded values will have to be revisited to ensure we remove obsolete values and incorporate Enterprise statuses.

Schema: FUSION

Object owner: PER

Object type: TABLE

Tablespace: REFERENCE

Primary Key