Re: Sqlplus script and variables

<schonlinner_at_yahoo.com> wrote in message news:1139907199.675624.57860_at_o13g2000cwo.googlegroups.com...
: Hi,
:
: what I want to achieve:
:
: I want to call an Sql*Plus script which should use the result of one
: select statement in the next statement, to be more exact: A "select"
: statement should return the partition name, and the second select
: statement should select all data from that specific partition, i.e.:
:
: select partition_name from all_tab_partitions where table_owner='SCOTT'
: and table_name='DM_MARKET' and partition_position=10
: /
: ==> put the result into a "variable" called partName in any way
:
: select count(*) from scott.dm_market partition (*partName*)
: /
:
: I know, the most easiest solution is to just use an anonymous PL/SQL
: block, but then I have to surround all statements with quotes,
: concatenate variables, etc (see following solution):
:
: declare
: partName varchar2(32);
: begin
: select partition_name into partName from all_tab_partitions where
: table_owner='SCOTT' and table_name='DM_MARKET' and
: partition_position=10;
: execute immediate 'select count(*) from scott.dm_market partition ('
: || partName || ')';
: end;
: /
:
: I wonder whether there is a more elegant way of doing that? The simple
: select statement is in reality a rather lengthly insert-statement (and
: we have about 200 of them), so in order to maintain the readability the
: different parts are in different lines and thus we would have some work
: of surrounding each line by quotes and concatenating them...
:
: Can it be done somehow in SQL*Plus without an anonymous PL/SQL block?
: The main problem seems to be that bind variables are of no use because
: one cannot "bind" a partition name. Substitution variables would be
: perfect (i.e. define partName=xxx, then "partition (&partName)", but I
: don't know of a way to assign the result of a select statement to such
: a variable...
:
: Best regards,
: Alex
:

use NEW_VALUE:

SQL> col deptno new_value the_deptno
SQL> select ename, empno, deptno
  2 from emp
  3 where job = 'PRESIDENT'
  4 /

ENAME EMPNO DEPTNO
---------- ---------- ----------
KING 7839 10 SQL> select *
  2 from dept
  3 where deptno = &the_deptno;
old 3: where deptno = &the_deptno
new 3: where deptno = 10

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 FINANCIAL NEW YORK ++ mcs Received on Tue Feb 14 2006 - 05:03:32 CST