Re: set defaults for $1?

From: Jan Haase <jh_at_informationsdesign.de>
Date: Fri, 08 Dec 2000 17:21:13 +0100
Message-ID: <3A310A79.44FCB27D@informationsdesign.de>

Hi Martin.

> >I want an index to be created with a size given on command line to the .sql-script. It looks like
 this:
> >
> >foo.sql:
> > create index index1 on table1 (column1)
> > tablespace ts1
> > pctfree 4
> > storage (initial $1 next $2)
> > unrecoverable;
> >
> >But what can I do if a user forgets to provide this size on command line? Is there any possibility to
 set a default if $1 is empty?
>
> Rewrite the script like this:
>
> column i_value new_value i_value noprint;
> column n_value new_value n_value noprint;
>
> select decode(&1,null,<default_value>) i_value from dual;
> select decode(&2,null,<default_value>) n_value from dual;
>
> create index index1 on table1 (column1)
> tablespace ts1
> pctfree 4
> storage (initial &i_value next &n_value)
> unrecoverable;

Thanks for your help - but your script doesn't work. I only get "-" instead of the default or even the given command line parameter.

Output:

SQL> @create-idx 1 2 old 1: select decode(&1,null,20) i_value from dual new 1: select decode(1,null,20) i_value from dual

1 row selected.

old 1: select decode(&2,null,20) n_value from dual new 1: select decode(2,null,20) n_value from dual

1 row selected.

Input truncated to 14 characters
old 4: storage (initial &i_value next &n_value) new 4: storage (initial - next -)
storage (initial - next -)

                 *

ERROR at line 4:
ORA-02218: invalid INITIAL storage option value

Any ideas?

Thanks in advance,
Jan Received on Fri Dec 08 2000 - 10:21:13 CST