oracle assign variable from select

Stack Overflow Public questions & answers
Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
Talent Build your employer brand
Advertising Reach developers & technologists worldwide
About the company

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.

Declaring a variable and setting its value from a SELECT query in Oracle

In SQL Server we can use this:

How can I do the same in Oracle? I'm currently attempting the following:

Why this is not working ?

Any idea how this can work for types as objects? – Avias Nov 22, 2013 at 15:01

5 Answers 5

SELECT INTO

Make sure that the query only returns a single row:

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of an aggregate function, such as COUNT(*) or AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition. A SELECT ... BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set. The implicit cursor SQL and its attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN provide information about the execution of a SELECT INTO statement.

Not entirely sure what you are after but in PL/SQL you would simply

As @Thilo states, there is more to consider around just this specific synatx. A little more info on what you're trying to achieve would enable us to tailor a better method for populating your variable. – Ollie Sep 26, 2011 at 10:52

One Additional point:

When you are converting from tsql to plsql you have to worry about no_data_found exception

In tsql if no data found then the variable will be null but no exception

ORA-01422: exact fetch returns more than requested number of rows

if you don't specify the exact record by using where condition, you will get the above exception

For storing a single row output into a variable from the select into query :

declare v_username varchare(20); SELECT username into v_username FROM users WHERE user_id = '7';

this will store the value of a single record into the variable v_username.

For storing multiple rows output into a variable from the select into query :

you have to use listagg function. listagg concatenate the resultant rows of a coloumn into a single coloumn and also to differentiate them you can use a special symbol. use the query as below SELECT listagg(username || ',' ) within group (order by username) into v_username FROM users;

Your Answer

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service , privacy policy and cookie policy

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

Hot Network Questions

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .

Skip Headers

SELECT INTO Statement

The SELECT INTO statement retrieves data from one or more database tables, and assigns the selected values to variables or collections. For a full description of the SELECT statement, see Oracle Database SQL Reference .

In its default usage ( SELECT ... INTO ), this statement retrieves one or more columns from a single row. In its bulk usage ( SELECT ... BULK COLLECT INTO ), this statement retrieves an entire result set at once.

Keyword and Parameter Description

Another (usually short) name for the referenced column, table, or view.

Stores result values in one or more collections, for faster queries than loops with FETCH statements. For more information, see "Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)" .

A declared collection into which select_item values are fetched. For each select_item , there must be a corresponding, type-compatible collection in the list.

A user-defined function.

An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which select_item values are fetched. For each select_item , there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.

A literal that represents a number or a value implicitly convertible to a number.

A formal parameter of a user-defined function.

A user-defined or %ROWTYPE record into which rows of values are fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible field in the record.

Anything that can follow the FROM clause in a SQL SELECT statement (except the SAMPLE clause).

The schema containing the table or view. If you omit schema_name , Oracle assumes the table or view is in your schema.

A SELECT statement that provides a set of rows for processing. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement" .

A table or view that must be accessible when you execute the SELECT statement, and for which you must have SELECT privileges. For the syntax of table_reference , see "DELETE Statement" .

The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table or a varray. Operator TABLE informs Oracle that the value is a collection, not a scalar value.

A previously declared variable into which a select_item value is fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible variable in the list.

Usage Notes

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row

If no rows are returned, PL/SQL raises NO_DATA_FOUND . You can guard against this exception by selecting the result of an aggregate function, such as COUNT(*) or AVG() , where practical. These functions are guaranteed to return a single value, even if no rows match the condition.

A SELECT ... BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.

The implicit cursor SQL and its attributes %NOTFOUND , %FOUND , %ROWCOUNT , and %ISOPEN provide information about the execution of a SELECT INTO statement.

The following example demonstrates using the SELECT INTO statement to query a single value into a PL/SQL variable, entire columns into PL/SQL collections, or entire rows into a PL/SQL collection of records:

SQL & PL/SQL

How do i assign a variable.

I want to develop a query that filters by a variable.

I have found multiple examples that say I need to use DECLARE block. So, I tried what works in a PL SQL package. It does not work in SQL Developer.

So, using SQL Developer, how do I create a variable, set the value and then use it like illustrated below...

Another way to assign values to a variable is by selecting (or fetching) database values into it. With the PL/SQL SELECT INTO statement, you can retrieve data from one row in a table. In Example: Assigning Values to Variables Using PL/SQL SELECT INTO , 10 percent of the salary of an employee is selected into the bonus variable. Now, you can use the bonus variable in another computation, or insert its value into a database table.

In the example, the DBMS_OUTPUT.PUT_LINE procedure is used to display output from the PL/SQL program. For more information, see "Inputting and Outputting Data with PL/SQL" .

Assigning Values to Variables Using PL/SQL SELECT INTO

Stack Exchange Network

Stack Exchange network consists of 181 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

Q&A for work

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

How do I declare and use variables in Oracle?

My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL:

And I get this error:

3 oracle is pure pain – Fabio Marreco Feb 7, 2020 at 20:57

3 Answers 3

Inside pl/sql block:

using a bind variable:

PL/SQL procedure successfully completed.

in a query:

SQL*Plus supports an additional format:

Note the ampersands where the substitutions are to be performed within the query.

In ORACLE SQL Developer 20.2.0.175, we can Run Script (F5):

Your Answer

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service , privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged oracle oracle-10g syntax or ask your own question .

Hot Network Questions

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .

Fixed Utility Menu

Your source for technical trends, tips, and best practices from pythian experts, select vs assign – how to assign pl/sql variables, select vs assign.

It isn’t unusual to see PL/SQL that contains select columns into variables from some_table

This is normal and expected when the data you need is in a table.

However, many years ago, Oracle provided a workaround for using SELECT statements in a somewhat unorthodox way via the DUAL table.

Some examples:

While such usage can be useful at times, it’s a bit of a performance hog.

In general, do not use select .. into when you could use a direct variable assignment instead.

Here a PL/SQL block is using select into to assign a date to a variable:

The same assignment can be performed directly:

Do you think it makes any difference which method is used?

If you think they’re equivalent, you may want to keep reading.

We’ll use two SQL scripts: select.sql and assign.sql.

Each script will assign sysdate to vDate 1M times in a loop.

We’ll use two different forms of monitoring for the tests:

Oracle Trace

Additionally, we’ll run the scripts without any monitoring, just so we can see the timing data.

We’ll set timing via set timing on in SQLPlus.

If you’re unfamiliar with perf, this is a good place to start: perf . I show all scripts in their entirety at the end of this article.

Before doing any kind of tracing, I’ll first run the test scripts to get timing information.

First I’ll run select.sql , then assign.sql.

Each script will make 1M variable assignments.

Assigning sysdate into vDate 1M times took 7.99 seconds.

Directly assigning via vDate := sysdate 1M times took significantly less time at 0.33 seconds.

It should be clear that you should never use select from dual when you’re able to use a direct variable assignment.

Now, let’s dig a little deeper and get a better understanding of why there’s such a large difference between the two methods of assigning a value to a variable.

Testing with Perf

We can use perf to count the operations performed by the server.

The record.sh script is used to start the recording on the server.

Testing Method

This is a fairly simple manual testing method.

Each of the SQL scripts will pause until I press ENTER.

While the SQL script is paused, I switch to the ssh session where I’m logged into the database server as root.

Then I start the perf recording via ./record.sh PID

Switching back to the SQLPlus session, I press ENTER.

Then I switch back to the server, and press CTL-C when the SQLPlus job is done.

Though somewhat crude, this method is sufficient for these tests.

I performed each test in this way, resulting in two files:

These files were renamed from the default perf.data following each test.

In the previous tests you may have noticed that the SPID was reported. This refers to the server PID for the Oracle process started on behalf of the SQLPlus session. It’s this PID that’s used with the record.sh script.

./record.sh 13280

I ran the same SQL scripts while recording each with perf.

perf report

Now to create a report from each file.

I used the following command to create a nice execution tree of each data file, along with counts for each function called.

perf report --stdio -g count -i perf.data.select

Although the output is fairly interesting, we won’t be delving into it today. What’s most interesting at this time is the number of operations performed, expressed as counters in perf.

We aren’t looking at timing, just how much work had to be done on the server for each test script.

For that, we just need one line from each file:

Well, that’s interesting. The number of calls when running the select.sql script is 32x that of the assign.sql script.

Testing with Oracle Trace

There are a number of methods to start a trace on an Oracle Session.

Here I’ll be using the old standby, alter session set events '10046 trace name context forever, level 12' simply because I have a script for it, and the name is easy to remember.

I once again ran the same two test SQL scripts, but this time by first setting the tracefile_identifier and enabling the trace.

Running select.sql with 10046 trace

Running assign.sql with 10046 trace.

Then I copied the tracefiles from the server.

We can learn a bit just by checking the sizes of the files:

There is a striking disparity in the size of those files. The overhead of using Oracle Trace caused the execution time of select.sql to balloon from eight seconds to 55 seconds.

Here’s a simple profile of each trace file:

It would seem the results are a bit skewed by the overhead of the trace, as there are 45 seconds of CPU used (recall that without tracing, the script took eight seconds).

Using standard linux tools, we can get a better idea of why the select.sql takes so much more time than assign.sql.

select.sql trace

Assign.sql trace.

The last three lines of the report for select.sql tell the story; when assigning variable via select into from dual, Oracle had to create, fetch and close a cursor 1M times.

That overhead can be avoided simply by assigning variables directly, as seen in assign.sql .

Any blog that uses perf for analysis would be incomplete without the requisite flame graphs.

Flame graph for select.sql

What’s of interest in these flame graphs is the amount of work being done once the script enters the plsql_run section.

The select.sql script has quite a bit of code being executed; not only is there a large stack of code being executed, it is very wide, which in a flame graph indicates a lot of work being performed.

Flame graph for assign.sql

Now take a look at the flame graph for assign.sql . There’s much less above the plsql_run section. Not only is the stack shorter, it’s much narrower, and therefore faster.

It’s good to periodically test your assumptions.

You probably wouldn’t notice the difference in singleton events that happen too quickly for a human to perceive the difference in timing. But when scaled up, such as I have done here, the differences are easy to see.

Will using a direct assignment make a noticeable difference in a PL/SQL program that does it only once? Probably not. But what if that PL/SQL program is called frequently?

What if there are several PL/SQL programs doing this? Maybe some of them doing so in a loop?

Not only would the difference in performance be discernable, but this activity would consume extra resources; leaving them unavailable for other processes.

Over time, small changes can add up to significant performance improvements.

The next time you’re writing some PL/SQL, be sure to look at it with a critical eye toward the impact it will have on system performance.

Below please find all the scripts I used:

get-curr-ospid.sql

10046_off.sql.

You can find all the relevant files on github: select-vs-assign .

Thank you for reading and please feel free to leave any thoughts or questions in the comments.

About the Author

Jared Still

No comments, leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

Close Search for: Search

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:

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 to 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:

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.

How to call variable in select query in oracle

2 solutions

Most Recent

Add your solution here

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Oracle Training Oracle Tips Oracle Forum Class Catalog Remote DBA Oracle Tuning Emergency 911 RAC Support Apps Support Analysis Design Implementation Oracle Support

The SELECT INTO Clause

The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database. The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables.

SQL> declare

2 v_authName author.author_last_name%type;

3 begin

4 select

5 author_last_name into v_authName

6 from

7 author

8 where

9 author_key = 'A103';

11 dbms_output.put_line('Name: '||v_authName);

12 end;

13 /

Name: weaton

Here the author_key was used to retrieve one author's last name and place it in the variable called v_authName . The query can also retrieve an entire row as a record with SELECT INTO.

In the example below a record based on the columns of the author table is declared in line two below. Because v_author is declared as an author table %rowtype , you can safely use the SELECT * clause to retrieve all the columns.

2 v_author author%rowtype;

5 * into v_author

11 dbms_output.put_line('Name:

'||v_author.author_first_name||' '||

v_author.author_last_name);

Name: erin weaton

If the DBA adds a column to the author table, the query above will still execute. The record variable v_author contains a record that includes all the columns in the author table. If the value of a column in the table is NULL, it will also be NULL in the record. The individual columns are accessed using the dot "." notation with SELECT INTO.

You can see this in line 11 of the listing. Although it is important to define variables using the database datatype definition of the retrieved data, sometime this is not possible.

This is shown in the example below.

2 v_totalName varchar2(80);

5 initcap(author_last_name||',

'||author_first_name)

into v_totalName

9 author_key = 'A105';

10 dbms_output.put_line('Name: '||

v_totalName);

11 end;

12 /

Name: Withers, Lester

The query above is returning a string value created from multiple table columns. The variable v_totalname must be defined as a datatype that can hold the composite string returned by the query. If the SELECT INTO clause attempts to load a value into a variable that is the wrong datatype, or not large enough to contain the value, an exception is raised.

Although a SELECT INTO can only return one row of data, SELECT INTO can return multiple columns into multiple variables. In the example below, two columns are selected from a table and loaded into two predefined variables.

2 v_lname author.author_last_name%type;

3 v_fname author.author_first_name%type;

4 begin

5 select

6 author_first_name, author_last_name

7 into v_fname, v_lname

8 from

9 author

10 where author_key = 'A108';

12 dbms_output.put_line('Name: '||v_fname||'

A single row of column values is loaded into the list of variables when multiple columns are selected. The order of the columns and the order of the variables must match when using the SELECT INTO clause.

In each example so far, the restriction defined in the query's WHERE clause has been based on a primary key. Using a unique or primary key is important as the SELECT INTO clause can not retrieve more that one row of data. If the query returns more that one row an exception is thrown.

9 author_state = 'MO';

10 dbms_output.put_line('Name: '||v_authName);

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at line 4

The above example retrieved the authors from the author table with an author_state of 'Missouri'. There are three authors in the author table from Missouri and the SELECT INTO raised a TOO_MANY_ROWS exception.

Another issue with using SELECT INTO statement is that SELECT INTO throws an exception is it fails to return at least one rows.

9 author_state = 'FL';

ORA-01403: no data found

Here the query asks for authors from Florida. Since there are not any authors in the table from Florida, the SELECT INTO raises a NO_DATA_FOUND exception exception" . Your PL/SQL code should be written in such a way that it is able to handle these exceptions. Anytime the SELECT INTO raises an exception, the query will not load a value into the defined variable.

When you try and access the variable, you will either get another exception or worse, use an old or invalid variable value. So using the SELECT INTO query can be problematic. However since a SELECT INTO query can return one and only one row of data, it makes a perfect PL/SQL function. Within the function, your code can catch and handle the possible exceptions.

Below is an example of wrapping the SELECT INTO query in a function with exception handling.

SQL> create or replace function auth_Name

2 ( v_auth_state IN author.author_state%type)

3 return varchar2

4 as

5 v_authName author.author_last_name%type;

6 begin

7 select

8 author_last_name into v_authName

9 from

10 author

11 where

12 author_state = v_auth_state;

13 return v_authName;

14 exception

15 when TOO_MANY_ROWS

16 then return 'Too Many Authors in that

State';

17 when NO_DATA_FOUND

18 then return 'No Authors in that State';

19 when others

20 then raise_application_error(

21 -20011,'Unknown Exception in authName

Notice when the function code catches an exception it must handle it and return something appropriate or pass the exception back to the calling block. In the above example, the code catches and handles the TOO_MANY_ROWS and NO_DATA_FOUND exception , which should cover most of the exceptions the function will encounter.

For more information, see the book Easy PL/SQL Programming .

Oracle ® is the registered trademark of Oracle Corporation.

IMAGES

How to set oracle_home path?
Oracle Select
How oracle select statement work internally
Seelenpartner letzte phase: Select into variable oracle
Select in Oracle
Oracle Application's Blog: How to assign role to user in oracle apps from backend

VIDEO

Oracle Apps Default Types Valuesets Part2
MP Controller Favorites!
What is a Variable?
OIC
Oracle Reports Developer 10g
Variable & assignments

COMMENTS

Declaring a variable and setting its value from a SELECT query in
DECLARE @variable INT; SELECT @variable= mycolumn from myTable;. How can I do the same in Oracle? I'm currently attempting the following: DECLARE COMPID
SELECT INTO Statement
The SELECT INTO statement retrieves data from one or more database tables, and assigns the selected values to variables or collections.
How do I assign a variable?
There are TWO ways to declare, assign to, and use "variables" in Oracle SQL queries. But, in fact, only one of them is truly a "SQL variable"
Assigning Values to a Variable With the PL/SQL SELECT INTO
Another way to assign values to a variable is by selecting (or fetching) database values into it. With the PL/SQL SELECT INTO statement, you can retrieve
How do I declare and use variables in Oracle?
Inside pl/sql block: declare startdate number; begin select 20110501 into startdate from dual; end; /. using a bind variable:
Select vs Assign
Select vs Assign – How To Assign PL/SQL Variables · Select vs Assign · Testing · Timings · Testing with Perf · Testing with Oracle Trace · Analysis.
PL/SQL SELECT INTO Statement By Practice Examples
A) PL/SQL SELECT INTO – selecting one column example · First, declare a variable l_customer_name whose data type anchors to the name columns of the customers
The Overview of PL/SQL Variables
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 ( := )
How to call variable in select query in oracle
While a procedure in SQL Server can return data as a result set, an Oracle procedure cannot do this the same way.
Oracle PL/SQL select into clause
The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database. The SELECT INTO is actually a standard SQL query where the