• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Using Variables in Oracle

Hi gang,  I need a little help with my oracle script. I want to create a few variables, load some SQL into the variables and display them.  I  have attempted to use the following script:

VAR TotCust  VARCHAR2(20);

EXEC :TotCust := 

    DBMS_OUTPUT.put_line('Total Customers:    ' + :TotCust);

Open in new window

However, I am getting the following error:
Error: ORA-00900: invalid SQL statement

SQLState:  42000
ErrorCode: 900
Position: 2
Error occured in:
VAR TotCust  VARCHAR2(20)

Open in new window

According to what I've read, this should work. But, I am probably missing something.

Can anyone lend a hand?
2 Solutions
tot_cust pls_integer;
select count(*) into tot_cust from customer;
dbms_output.put_line('Total Customers: '||tot_cust);
slightwv (䄆 Netminder) Commented:
To add the description to what awking00 posted:

dbms_ouptut is a PL/SQL package.  It looks like you were running it straight from sqlplus.

PL/SQL is Oracle's procedural language.  What awking00 posted is called an anonymous PL/SQL block.

It runs in sqlplus but is a self-contained block of PL/SQL code.

Now it you want a complete sqlplus solution try this:

var TotCust varchar2(20);

exec select count(*) into :TotCust from customer;

print TotCust

Open in new window

MikeDelaneyAuthor Commented:
While I'm testing this in Squirrel, ultimately this will be run from a batch file (I'm guessing calling SQLplus.)  So, the first SQL does not return anything in Squirrel.  the second fails with the following error.

Error: ORA-00900: invalid SQL statement

SQLState:  42000
ErrorCode: 900
Position: 0
Error occured in:
var TotCust varchar2(20)

Open in new window

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

slightwv (䄆 Netminder) Commented:
Likely an issue with Squirrel.  I tested what I posted with sqlplus and I'm confident awking00's will work with in sqlplus as well.

>> (I'm guessing calling SQLplus.)  

Test with what the final product will be using.

You can download the Instant Client and an add-on that includes sqlplus:

Or SQL Developer (it recognizes sqlplus syntax):
Gerwin Jansen, EE MVETopic Advisor Commented:
@slightwv - your URL has: ?ssSourceSiteId=ocomen at the end?

I'd use this URL for SQL Developer:
MikeDelaneyAuthor Commented:
Hi - the last piece of the puzzle was to  set SERVEROUTPUT on.  Once I did that, I could get the output.  Both answers helped greatly.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now