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 := 
(   SELECT COUNT(*) 
    FROM CUSTOMER c
     );

    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?
MikeDelaneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Commented:
declare
tot_cust pls_integer;
begin
select count(*) into tot_cust from customer;
dbms_output.put_line('Total Customers: '||tot_cust);
end;
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

0
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

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

Or SQL Developer (it recognizes sqlplus syntax):
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html?ssSourceSiteId=ocomen
0
Gerwin Jansen, EE MVETopic Advisor Commented:
@slightwv - your URL has: ?ssSourceSiteId=ocomen at the end?

I'd use this URL for SQL Developer:
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.