Re: SQLLDR expert required

philipwinfield_at_hotmail.com (Phil) wrote in message news:<6e25c7f7.0303070439.1255438_at_posting.google.com>...
> I am trying to create a simple sqlldr script for an end user. All they
> should need to do is copy 3 load files every 3 months to a specific
> directory. In there will be three ctl files. This all works great, but
> the format of the files are identical. My ctl files add in the
> version.
>
> What I want to do is provide a prompt, like in sqlplus once, which
> accepts the version and then substitutes this in the 3 ctl files and
> hence it is unlikely they will accifentally load in the data against
> the last version.
> What is the best approach?
>
> Oracle 8.1.7 using a Windoz 2K client.
>
> Thanks!
>
> Phil

Create a batch script and run sqlldr from it. On Windows 2K, with command extensions enabled, you can prompt user for a value via SET /P command and then create your control file. e.g.

_at_ECHO OFF

SET CONTROLFILE=C:\Temp\file.ctl
SET /P MYVALUE=
ECHO load data > "%CONTROLFILE%"
ECHO infile >> "%CONTROLFILE%"
ECHO replace into table MY_TABLE >> "%CONTROLFILE%" ECHO fields terminated by '^|' optionally enclosed by '^"' >> "%CONTROLFILE%"
ECHO trailing nullcols >> "%CONTROLFILE%" ECHO ( >> "%CONTROLFILE%"

ECHO   ....., >> "%CONTROLFILE%"
ECHO   ....., >> "%CONTROLFILE%"
ECHO   .... %MYVALUE%, >> "%CONTROLFILE%" (%MYVALUE% has the user