Re: Query result in a Variable ?

hello,

  There are various ways of getting information from SQL*Plus out to the operating system. None that i know of are very nice, basically because Oracle runs on so many platform that they have used the lowest common denominator, mainly files.
Therefore:

1. spool the output to a text file and pick the value up from the shell script.
2. use the 'host' command to pass the value to a unix shell. However, the shell process will be a child process of SQL*plus. I have no idea how to get the information back to the parent shell.

Variables in SQL*Plus.

There are two kinds.

1. substitution variables. SQL*Plus as always had these. They are characters string variables. Create them with 'define' command. Set them using the 'new_value' clause of the 'Column' command. The value of them is used by using the '&' (ampersand) character in front of the name and a '.' (full stop) after the name - the full stop is optional. They can be used almost anywhere. The SQL*Plus parser substitutes them before executing the command.

Example:  

define RowCountDV=''
column SetRowCountDV new_value RowCountDV;

select to_char( Count(*) ) SetRowCountDV from Table /

ways to see the value is:
prompt &RowCountDV.
or
select '&RowCountDV.' from dual;

1. bind variables. These are used to allow PL/SQL to communicate with SQL*Plus. They are created with the 'variable' command and need a type assigned to them. i.e.

     variable BVRowCount number;

They are set from PL/SQL and are identified as bind variables by using a ':' (colon) at the start of the name.

They can be set using assignment statements ( e.g. :BVRowCount := 3) or using
' select ... into :bind_variable' statements.

Example:

begin

   select count(*) into :BVRowCount from Table; end;
/                                          

the value can be used by SQL*Plus in select statements (and where clauses etc). e.g.

select :BVRowCount from dual;

or printed using the 'PRINT' command.

if this is all very obvious or not what you wanted to know then oops.

graham (aka grumpy)

tero.rantaniemi_at_tele.telebox.fi (Tero Rantaniemi) wrote...

| Hi !
|
| I have a problem. I don't know how to use variables in sql.
| Example:
|
| SELECT count(*) FROM table;
|
| I want to put the result in a variable and take it to UNIX.
| Do I have to define a variable first and then and how ....??
|
| THANK YOU !
|
| email: tero.rantaniemi_at_tele.fi

Graham Miller ...
Opinions expressed are mine, they are free, and worth exactly what they cost. Received on Sun Jun 15 1997 - 00:00:00 CDT