1

I'm the same guy that asked:

BEGIN/END and CREATE Table in single .sql file

Anyhow, we have a several .sql scripts we're executing via SQL*Plus.

These scripts are used for creating tables, synonyms, indexes, views etc.

A single .sql file will usually have several CREATE statements inside them.

What we're looking for is a way to report pass/fail for each CREATE statement, as well as an error message if it failed.

Create table foo: Pass
Create index bar: Fail - Reason...
Create synonym foo_1: Pass etc.

Currently, we can parse the SQL*Plus output, however, this has a lot of extraneous stuff in it, and parsing a logfile like this isn't the most robust approach.

Is there a better way to programmatically check on the return status of each SQL statement via SQLPlus, and the error message, if any? Is that something achievable with SQLPlus?

Or do I need to write a wrapper of some sort (Bash, Python etc.)? Or handle this all via another way?

Cheers, Victor

Community
  • 1
  • 1
victorhooi
  • 16,775
  • 22
  • 90
  • 113

2 Answers2

2

If you want to continue with the SQL script when an error occurs, then you should create your tables and indices from within a PL/SQL block since PL/SQL has a decent exception handling:

BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE XY ( ... )';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Creating table XY failed: ' || SQLERRM);
END;
/

If you want to end the current SQL script when an error occurs, you can use the following SQL*Plus command and do the error handling in the script that called SQL*plus:

whenever sqlerror exit sql.sqlcode
Codo
  • 75,595
  • 17
  • 168
  • 206
2

Personally I don't like embedding logic into DDL scripts, unless absolutely needed (the script can easily become overly complicated, and its action less predictable)

I prefer to scan the script output for errors e.g: grep for the string ORA-

Then check there are no invalid objects in the schema

Kevin Burton
  • 11,676
  • 2
  • 24
  • 37