Are you having an issue migrating (>5000 lines) large sql scripts?

In Stat 5.6.2 functionality was introduced that added preprocessing commands to SQL Plus when processing scripts. That allowed Stat to capture a class of SQL Plus error messages that otherwise were not returned to Stat by SQL Plus. Unfortunately the addition of these preprocessors had the detrimental effect of decreasing the buffer size allowed for pasting in the script itself.

The most frequent error noticed by users was “directory/file not found”. Users also noticed that the script did not look complete in the log file.

There are 2 resolutions to this issue. The first is the preferred resolution.

Both resolutions involve a modification to the stat.conf file followed by recycling of the Oracle agent(s).

1) Run SQL Plus in silent mode. Add this to the stat.conf file:

<env>.sqlplus_options:-s

OR

2) Don’t use PREPROCESSORS. Disable the PREPROCESSORS in stat.conf by modifying the SQLPLUS_PREPROCESSORS statement to this:

<env>.env.SQLPLUS_PREPROCESSORS:

If SQL Plus is run in silent mode, the log files on the OA console will not display every single line of the script. Only the SQLPlus errors will be displayed.

If you drop the preprocessors there may be some SQL errors that SQLPlus does not return to Stat. Stat may think a migration was successful even though the compile did not complete. If the schema object is thoroughly tested before migration, this should not be an issue.

This has been posted as SOL101883 on the Support Portal.

Also be aware that the Stat development team is working on a solution to capture Sho Err output from SQL Plus.

About the Author