Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Anna.

Asked: March 18, 2021 - 7:21 am UTC

Last updated: March 22, 2021 - 7:30 am UTC

Version: 11.1.0.6.0

Viewed 100+ times

You Asked

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/VARIABLE.html#GUID-B4A0DAA3-B6E0-42F7-89B0-EF9C41F02FA3

I read the followings at the above link

The following example illustrates assigning a value to a variable for input binding:

VARIABLE tmp_var VARCHAR2(10)=Smith


The following example illustrates an alternate method to achieve the same result as the previous example:

VARIABLE tmp_var VARCHAR2(10)    
VARIABLE tmp_var=Smith    
EXECUTE DBMS_OUTPUT.PUT_LINE(:tmp_var)


My problem: When I tried to run above commands to create these variables in sqlplus.exe, Oracle did not create them, I am a newbie I even tried to run these command in Oracle live too but couldn't succeed, I was just executing them as Oracle instructed me to do. could you please guide me what wrong I am committing.

Regards,
Anna

and we said...

Here's what I see when running those examples:
SQL> VARIABLE tmp_var VARCHAR2(10)=Smith
SQL> EXECUTE DBMS_OUTPUT.PUT_LINE(:tmp_var)
Smith

PL/SQL procedure successfully completed.

SQL>
SQL> VARIABLE tmp_var VARCHAR2(10)
SQL> VARIABLE tmp_var=Smith
SQL> EXECUTE DBMS_OUTPUT.PUT_LINE(:tmp_var)
Smith

PL/SQL procedure successfully completed.


All looks good to me!

So... what exactly happens when you try these? Show us your code and output!

Note: these commands are unsupported in Oracle Live SQL, so you'll get an error if you try and use these there.

Rating

  (3 ratings)

Comments

Great!

A reader, March 18, 2021 - 8:51 pm UTC

Sir, I executed exactly these commands as you did. I executed them in Version: 11.1.0.6.0

Could this version be problem? which was not letting to execute these commands.
Chris Saxon
March 19, 2021 - 8:34 am UTC

*checks docs for 11.1*

https://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12050.htm#BACGHCJE

Nope, no reference to being able to declare a variable and assign it in one go.

I believe this enhancement arrived in 12.2 of SQL*Plus

This problem occurs at my side

A reader, March 18, 2021 - 8:53 pm UTC


SQL> VARIABLE tmp_var VARCHAR2(10)=Smith
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

support for input binding on "variable" command

Rajeshwaran Jeyabal, March 20, 2021 - 3:32 pm UTC

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/release-changes.html#GUID-27FF7BCE-6111-4872-8FA1-0F412610290D
....
Support for input binding by using the VARIABLE command. The VARIABLE command now supports input binding which can be used in SQL and PL/SQL statements.
....


As mentioned on the docs, it is a new feature of SQL*Plus version 12.2 and above.

so you should be using the sql*plus version 12.2 and above to get it worked.

demo@XEPDB1> variable x number = 55
demo@XEPDB1> exec dbms_output.put_line('x = '||:x);
x = 55

PL/SQL procedure successfully completed.

demo@XEPDB1> print x

         X
----------
        55

demo@XEPDB1> $ sqlplus -version

SQL*Plus: Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Connor McDonald
March 22, 2021 - 7:30 am UTC

sounds like its time to upgrade :-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library