Defines Using Select

Instead of creating a one-line SQL script containing a define statement to set variables at runtime, you can use the new_value option of the column command to set the variable's value generated by a select statement. The snippet below is taken from my Y2K Date Input tip. It checks the length of a date string to see if it has a 2-digit year or a 4-digit year, converting 2-digit years using the RR format and returning the date string with the resulting 4-digit year. To do this, it creates a define statement in a file, and runs the resulting sql file.

spool fieldina.sql
select 'define fieldval = ' || to_char(decode(sign(9-length('&fieldval')),-1,
     to_date('&fieldval','DD-MON-YYYY'),to_date('&fieldval','DD-MON-RR')),
'DD-MON-YYYY') from dual;
spool off
@fieldina.sql

This can also be done without creating the sql file and running it. The select statement below does the conversion, producing a column named fieldvalset. The column command then takes the value in that fieldvalset column and stores that into the fieldval variable as its new value, eliminating the intermediate file.  (The fieldvalset column could also just be called fieldval.) (Thanks to Bruce Knox here for this tip.)

column fieldvalset new_value fieldval
select to_char(decode(sign(9-length('&fieldval')),-1,
     to_date('&fieldval','DD-MON-YYYY'),to_date('&fieldval','DD-MON-RR')),
     'DD-MON-YYYY') fieldvalset from dual;

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.