Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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 1000+ 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 Chris 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)

We're not taking comments currently, so please try again later if you want to add a comment.

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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