Re: Parsing the spool file.....

amerar_at_iwc.net wrote:
> Hi all,
>
> I have a question. We have a dumb ass configuration management team.
> Basically they want us to prepare Unix script with the necessary code
> to apply the changes to the Oracle database, and the CM team will apply
> the changes out to the field.

Not an unheard of approach.

> Here is an example script:
>
> sqlplus /@$1 <<EOF
> GRANT SELECT ON dba_tab_columns TO mirs_dynamic_data;
> GRANT SELECT ON dba_synonyms TO mirs_dynamic_data;
> GRANT SELECT ON dba_objects TO mirs_dynamic_data;
> @crdb_mirs_arads_feedback_tables
> ALTER SESSION SET CURRENT_SCHEMA = MIRS_DYNAMIC_DATA;
> @replicate_tables
> @get_columns
> @tt_utl
> @process_status_package_body
> @name_change
> @spf_change
> @ssan_change
> @forms_library_body
> exit
> EOF
>
> Now, this will create a nice long log file. Does anyone know of a good
> way to parse this file? Sometimes the file grows to 5000 lines long,
> and to have a non-unix or non-oracle person trying to understand the
> file is crazy.
>
> I'd like to get something that will go through the file and when it
> finds an ORA- error, it prints out the previous say 10 lines so one can
> see what led up to the error.
>
> Additionally, a typical log file would look like this:
>
> Synonym created.
>
> Grant succeeded.
>
> Table altered.
>
> Table dropped.
>
> Table created.
>
> Warning: Trigger created with compilation errors.
>
> SELECT * FROM OLD_A_FDBK_DD2246
> *
> ERROR at line 9:
> ORA-00942: table or view does not exist
>
> Where it says the Trigger had warnings, how do I know the trigger
> name??? Is there any way to echo the name of the procedure or object
> it is working with???
>
> Thanks!!
>

In your script, add "set echo on" as one of the first things done in SQL*Plus. This way, you will see the SQL statement being echoed in the SQL*Plus session.

If you want to parse the ORA- errors and display ten lines before that, then you'll probably have to write your own parser. It shouldnt' be too hard in Perl. Otherwise, I typically load the log file into vi and do ":/ORA-" to search for the first occurrence of ORA- in the file. That will show me the error line and the first few lines above it. To see the next occurence, type "/" followed by return. Repeat as necessary. You can do a similar op in other editors too.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown