setting a DEFINE value at runtime 2

[jaxtell]

I'd like to change a create table script to decide at run time which compression to use. Here is what I have so far

set define on
column compression new_value compress_for noprint
select 'oltp' as compression from dual;
create table ....
compress for &compress_for
...;
set define off

I'll replace the hard coded 'oltp' with some logic to determine appropriate compression. Is this the right way to accomplish defining a value at runtime? Is there a better way?


-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[SantaMufasa]

Is this the right way to accomplish defining a value at runtime?

Yes, Jaxtell, that is one way to define a value at runtime for a substitution parameter. The method you presented is particularly appropriate if you are prompting for a value, then some additional SQL processing needs to occur against the value you input prior to its use via a "&<name>" invocation.

The following script is an example of "additional SQL processing". It is a script that:[ul][li]Prompts for a username and password,[/li][li]Connects to the default Oracle instance,[/li][li]Then, the "additional SQL processing" algorithmically formulates an entirely new SQL*Plus prompt (placing the results into the substitution parameter, "my_prompt"). The new prompt is composed of:[ul][li]the host name[/li][li]the Oracle instance name[/li][li]the current Oracle user name, and[/li][li]The literal "SQL> "[/li][/ul]

accept usrname prompt "Enter an Oracle username: "
accept pw prompt "Enter &usrname.'s password: "
conn &usrname/&pw
col my_prompt new_value my_prompt
set feedback off
set time on
set termout off
select '"'||host_name||'/'||instance_name||
           ':'||user||' SQL> "' my_prompt
from v$instance;
set termout on
set sqlprompt &my_prompt

I saved the above code into a script named "log-me-in.sql". (Since there are SQL*Plus "ACCEPT" statements, you must run this code from a script...you cannot just copy and paste the code to a SQL> prompt.)

Here is an invocation of "log-me-in.sql":

SQL> @log-me-in
Enter an Oracle username: summit
Enter summit's password: ********
Connected.
16:43:42 MY-PC/my_oracle:SUMMIT SQL>

[/li][/ul]If you don't need to do any manipulation of a substitution parameter (i.e., SQL*Plus "&"-name), then you don't need all of the "new_value" definitions. Here is an example of very simple substitution-parameter definition and usage, based upon a variant of your original code:

SQL> create table jaxtell (x number) tablespace &my_tablespace;
Enter value for my_tablespace: data1

Table created.

SQL>

So you can see from the above, highly simplified code, that run-time value specification can be much simpler than your original code. If, however, you need additional SQL manipulation to a manually entered substitution-parameter value, your original technique is excellent.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.