.AfterJob (1): Output is Unmatched quotation marks.
DataStage/SQL: Table "xxx" does not exist.
Verb "EXIT;" is not in your VOC.
Verb "ENDOFSQL"" is not in your VOC.
Retcode is -1
The same sqlplus stmt when I execute on UNIX directly, it is inserting data into table.
JobNamesFromSequence..AfterJob (1): The sql statement is sqlplus<<endofsql -S aaa@bbb/cc11
INSERT INTO APPS.XYZ(SERVER_NM,PROJ_NM,JOB_NM,STATUS) VALUES(aaa,bbb,JobNamesFromSequence,0);
exit;
endofsql
JobNamesFromSequence..AfterJob (1): Output is Unmatched quotation marks.
DataStage/SQL: Table "APPS.XYZ" does not exist.
Verb "EXIT;" is not in your VOC.
Verb "ENDOFSQL"" is not in your VOC.
Retcode is -1
It seems to me that char(254) breaks the command line into several lines and the second line of SQL already executes not under UNIX shell but in Universe. That gives those errors.
Try to write without char(254).
I do not have Oracle and Unix now to prove this.
I guess I was thinking of the entry logged by an Execute Command stage in a Sequence job, something where the command was auto-logged by the running process. Never mind.
-craig
"You can never have too many knives" -- Logan Nine Fingers
AfterJob (1): The sql statement is
sqlplus<<endofsql -S aaa@bbb/cc11 INSERT INTO APPS.XYZ(SERVER_NM,PROJ_NM,JOB_NM,JOB_STATUS,START_DT) VALUES(pa12,Test,JobNamesFromSequence,0,2007-09-10 03:59:23); exit; endofsql
AfterJob (1): Output is SH: Syntax error at line 1 : `(' is not expected.
Retcode is 2
Hence I included CHAR(254) thinking each command would be recognized.
You are correct. It did not work without char(254), but using char(254) did not make it right.
Char(254) has a special meaning for universe shell and definitely does not work the way that you intended.
Because you are on UNIX, try to use char(10) instead.
It is just a guess. I am not able to test it.
Log message
ODE_FTI_PROJRES_F00..AfterJob (UpdateJobStats): SQLPlus output: Unmatched quotation marks.
DataStage/SQL: Table "hardcodetablename " does not exist.
Verb "QUIT;" is not in your VOC.
Verb "EOI"" is not in your VOC.
The sqlplus statements above work when entered natively...