Execute Command and Aborting while sql error

[thanush9sep]

This is the unix script that I have written.
Length of RBX_PROJ_NAME varchar2(3).

CONNECT_ODS=$1/$2@$3
sqlplus -s <<EOF
$CONNECT_ODS
INSERT INTO RBX_LOG
(
RBX_PROJ_NAME
)
VALUES
(
'RBX'
)
/
exit
EOF

In Datastage, I am using Execute command to run this script and Trigger is set to OK-(Conditional)

1. The sequence job only aborts due to this execute command when the oracle user or password or SID are wrong (i.e., connection problem).
2. But it does not abort when the insert command fails.

For example;

As you can see in the script I have given a value 'RBX' to the column 'RBX_PROJ_NAME' this will not generate any sql error because the length of RBX_PROJ_NAME is 3. If I give a value more than length 3 like 'RBX1' this will generate an sql error something like

Reply=0
Output from command ====>
'RBX1',
*
ERROR at line 18:
ORA-12899: value too large for column "RBX_ODS_RBX"."RBX_LOG"."RBX_PROJ_NAME"
(actual: 4, maximum: 3)

My issue with my design and trigger option is that the sequence job does not abort when there is a sql error.

How can I abort the sequence job when I face issues like this.
I tried using the returnvalue setting it to 0 or 1 but they seem to ignore the sql error

Additional Info:
Enabled: Log warnings after activities that finish with status other than OK
Enabled: Automatically handle activities that fail
Enabled: Log report messages after each job run
disabled: Add checkpoints so sequence is restartable on failure

Actual Design:

Executecommand(Insert script)--> Extraction Job--> Transformation--Job

The design includes
Exceptional Handler-->UserdefinedVariableActivity-->Executecommand(Update script)-->Terminator Acitivty

If Execute command (Insert script) fails then the exceptional handler should capture it,

[chulett]

You need a smarter script. As you've found, sqlplus only reports back an error if the session itself has issues running but could care less what happens with the sql itself that you run inside it.

Capture the output of the session to a file and then grep through it afterwards for ORA (etc) errors. If the grep returns anything other than a count of zero, force a non-zero return code from your script. We would redirect it off to /tmp and use the metacharacters that translate into the PID for part of the filename ($$ ?) so it stays unique and then delete it afterwards.