Re: Code Help

From: Alex Filonov <afilonov_at_pro-ns.net>
Date: Tue, 14 Nov 2000 17:25:34 GMT
Message-ID: <8ursi2$a4d$1@nnrp1.deja.com>

In article <3A115EC3.22D7B28B_at_bridgewatersystems.com>,   Tony Walby <tony.walby_at_bridgewatersystems.com> wrote:
> Could someone tell me what is wrong with this code.
> spool size_test1.lst
>
> prompt 'Enter a choice: 1.2.3' &&choice:
> declare var size number;
> If &&choice = 1
> size := 15000000;
> elseif &&choice = 2
> size := 7500000;
> elseif &&choice = 3
> size := 3500000;
> else
> size := 500000;
>
> select ds.owner "OWNER",
> dt.table_name "Table Name",
> (ds.bytes/dt.num_rows) * size "Optimal Initial"
> from
> dba_segments ds, dba_tables dt
> where
> ds.owner = 'R22' and
> dt.owner = 'R22' and
> ds.segment_name = dt.table_name and
> ds.segment_type = 'TABLE' and
> dt.num_rows > 0
> order by
> 1,2,3
> /
> spool off
>

You apparently want to have output of your sql statement printed. But declare statement means that this is a PL/SQL code. You can convert it into pure sql:

 spool size_test1.lst

 prompt 'Enter a choice: 1.2.3' &&choice:

 select ds.owner "OWNER",

        dt.table_name   "Table Name",
        (ds.bytes/dt.num_rows) *
        decode(&&choice,1, 15000000,
                                     2, 7500000,
                                     3, 3500000,  500000)   "Optimal Initial"

from

   dba_segments ds, dba_tables dt
where

   ds.owner = 'R22' and
   dt.owner = 'R22' and
   ds.segment_name = dt.table_name and
   ds.segment_type = 'TABLE' and
   dt.num_rows > 0

order by
1,2,3
/
spool off

>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 14 2000 - 11:25:34 CST