Sqlplus script and variables

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 Received on Tue Feb 14 2006 - 02:53:19 CST