exit code from run-i2p-transform.sh is not reliable

[dckc]

It has often reported success to Jenkins even though it failed; for example, build 1​02 Dec 13, 2016 4:19 PM resulted in ORA-00933: ​SQL ​command ​not ​properly ​ended (fixed in ecca422).

[dckc]

@njgraham does this mean you figured out how to get sqlplus to give reliable exit codes?

whenever oserror exit 9;
whenever sqlerror exit sql.sqlcode;

https://github.com/kumc-bmi/grouse/pull/6/files#diff-d6c1b161a9fc0ec54d3cd63ca166981aR50

cc @archaean @mprittie

[njgraham]

@dckc, unfortunately that doesn't result in reliable exit codes (at least in my experience/memory). Even with those statements sqlplus exits with 0 for some errors.

For example, an asktom article on trapping sqlplus SP2 errors says:

surprisingly - there are no capabilities to catch those and exit or report an error.

For reference: a list of SP2 errors. From our build log, I see several "successful" builds that actually failed with SP2 errors.

The asktom article above, linked to an article that indicates with 11g and up you can check to see if any errors occurred in your session so maybe we could do a 1/0 test on the row count from that table to achieve what we want.

I one that failed with PLS-00905: object PCORNET_CDM.PCORNETLOADER is invalid - I haven't found a specific reference yet as to why sqlplus would still return 0 for PLS-* errors but maybe the same method of checking that error table would work.

[dckc]

I wonder if using ant to run our sql scripts would be more reliable.

[njgraham]

Perhaps so. Not getting errors from sqlplus is really annoying and ends up wasting a lot of time and now I'm having the same problem with the GROUSE project. I didn't want to introduce lots of other dependencies by writing yet another SQL parser/executor but I admit I don't have experience with much more the Python/cx_Oracle for running SQL - maybe using ant would be straightforward and dependency free enough to be worth it.