asked on

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?

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

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

MikeDelaney

ASKER

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

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gerwin Jansen

@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

MikeDelaney

ASKER

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.