Displaying variable values 2

[MCubitt]

Sorry for the basic question, but how can I display, within an SQL script, the current value of a variable?

 

[avjoshi]

you can use the "dbms_output" package to display value of a variable in a PL/SQL procedure.
If it is a simple sql script you can prompt the value.
For example, following statement displayes the value of variable aj.

SQL> define aj=123
SQL> prompt &aj
123
SQL>

 

[SantaMufasa]

MCutitt and Avjoshius,

To be technically correct on this topic, in AvJoshius's example above, "aj" is not a "variable" it is a SQL*Plus "defined literal"; if it was truly a "variable", then you should be able to "vary" it with some sort of statement like "define aj = aj+1", which you cannot.

You create SQL*Plus (bind) variables with the syntax:
"variable x number"

To assign values, you can do so with this syntax:
"exec :x := 18"

To display the variable's value, you have a couple of options:
"select :x from dual;"
:X
----------
18

or:
"exec dbms_output.put_linex)"
18

To "vary" the value of a variable, you may say:
"exec :x := :x+2"
"exec dbms_output.put_linex)"
20

I hope this clarifies things a bit.

Dave
Sandy, Utah, USA @ 19:02 GMT, 12:02 Mountain Time

 

[avjoshi]

Thats correct Dave. I should have been more carefull and stated that aj is a literal in my example.

--Anand.

 

[sem]

You may also print bind variable in sql*plus by simply

print <variable name>

Regards, Dima