Page 1 of 1
Error in routine- not in VOC
Posted: Thu Sep 06, 2007 5:56 am
by ssunda6
Hi All,
The routine is trying to insert data into an oracle table. And Iam using DSExecute with sqlplus to insert data.
The code is as follows:
Code: Select all
SQLSTMT = "sqlplus<<endofsql -S abc@dddd/xxx":Char(254):" INSERT INTO APPs.LOGDATA(SERVER_NM,PROJ_NM,JOB_NM,START_DT)"
SQLSTMT := " VALUES(":sHostName:",":sProjName:",":sJobName:",":sJobStartTime:");":Char(254):" exit;":Char(254):" endofsql"
Call DSLogInfo("The sql statement is ":SQLSTMT, DSJ.ME)
The variables sHostName and all I have already initialised.
Iam getting the following error:
Code: Select all
.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.
Any inputs on this would be of great help.
Regards,
ssunda.
Posted: Thu Sep 06, 2007 7:41 am
by chulett
All that and you don't show us your actual DSExecute syntax.
Posted: Thu Sep 06, 2007 3:09 pm
by ray.wurlod
The first argument of DSExecute must specify a UNIX shell, not a UniVerse shell.
You are getting "not in VOC" because you are trying to execute in the DataStage (UniVerse) environment, not in the UNIX environment.
Posted: Fri Sep 07, 2007 3:21 am
by ssunda6
Sorry. By mistake I missed copying that statement here.
Code: Select all
Call DSExecute('UNIX',SQLSTMT,Output,SystemRetValue)
I used the above DSExecute statement. I have used "UNIX" only Ray.
Regards,
ssunda.
Posted: Fri Sep 07, 2007 5:37 am
by ray.wurlod
Then it was not this call that generated a "not in VOC" error. Please post the entire error message, verbatim (copy/paste, rather than transcribe).
You probably need some single quote characters in the VALUES clause, but that would not be the cause of a "not in VOC" error.
Posted: Fri Sep 07, 2007 6:27 am
by ssunda6
The whole code is as follows:
Code: Select all
$INCLUDE DSINCLUDE JOBCONTROL.H
$INCLUDE DSINCLUDE DSJ_XFUNCS.H
sJobName = DSGetJobInfo(DSJ.ME,DSJ.JOBNAME)
Call DSLogInfo("Job Name : ":sJobName, DSJ.ME)
sProjName = DSGetProjectInfo(DSJ.PROJECTNAME)
Call DSLogInfo("Project Name : ":sProjName, DSJ.ME)
sHostName = DSGetProjectInfo(DSJ.HOSTNAME)
Call DSLogInfo("Host Name : ":sHostName, DSJ.ME)
sJobStartTime = DSGetJobInfo(DSJ.ME,DSJ.JOBSTARTTIMESTAMP)
Call DSLogInfo("Job Start time : ":sJobStartTime, DSJ.ME)
sJobStatus = DSGetJobInfo(DSJ.ME,DSJ.JOBSTATUS)
Call DSLogInfo("Job Status : ":sJobStatus, DSJ.ME)
sJobStatusExp = DSTranslateCode(sJobStatus)
Call DSLogInfo("Job Status Exp is : ":sJobStatusExp, DSJ.ME)
SQLSTMT = 'sqlplus<<endofsql -S aaa@bbb/cc11':Char(254):' INSERT INTO APPS.XYZ(SERVER_NM,PROJ_NM,JOB_NM,STATUS)'
SQLSTMT := ' VALUES(':sHostName:",":sProjName:",":sJobName:",":sJobStatus:");":Char(254):" exit;":Char(254):" endofsql"
Call DSLogInfo("The sql statement is ":SQLSTMT, DSJ.ME)
Call DSExecute('UNIX',SQLSTMT,Output,SystemRetValue)
Call DSLogInfo("Output is ":Output: "Retcode is ":SystemRetValue, DSJ.ME)
ErrorCode = 0
And the Log statements are :
Code: Select all
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
Regards,
ssunda.
Posted: Fri Sep 07, 2007 6:54 am
by chulett
There should also be a log entry that shows the actual command executed. Please post that.
Posted: Fri Sep 07, 2007 7:16 am
by ssunda6
Chulett,
There is no other log entry which shows the actual command executed.
Regards,
ssunda.
Posted: Fri Sep 07, 2007 9:22 am
by leomauer
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.
Posted: Fri Sep 07, 2007 3:46 pm
by ray.wurlod
ssunda6 wrote:Chulett,
There is no other log entry which shows the actual command executed.
Regards,
ssunda.
There should be, because of
Code: Select all
Call DSLogInfo("The sql statement is ":SQLSTMT, DSJ.ME)
Posted: Fri Sep 07, 2007 4:10 pm
by chulett
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.
Posted: Mon Sep 10, 2007 5:17 am
by ssunda6
Leomauer,
Earlier I tried without CHAR(254) only. But it was giving error:
Code: Select all
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.
Regards,
ssunda.
Posted: Mon Sep 10, 2007 7:32 am
by leomauer
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.
Posted: Tue Nov 27, 2007 5:07 pm
by millea1
I'm getting this same error so am wondering if this was ever resolved? the parms, e.g. username/pswd/etc. are all 'hardcoded'
code:
cmd = "sqlplus -s hardcodeuserid/hardcodepswd@hardcodeDBname << EOI"
cmd<-1> = "INSERT INTO hardcodetablename VALUES ('hardcodestring', 1500);"
cmd<-1> = "quit;"
cmd<-1> = "EOI"
Call DSExecute('UNIX',cmd,output,rc)
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...