Error in routine- not in VOC

[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.

[chulett]

All that and you don't show us your actual DSExecute syntax.

[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.

[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.

[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.

[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.

[chulett]

There should also be a log entry that shows the actual command executed. Please post that.

[ssunda6]

Chulett,

There is no other log entry which shows the actual command executed.

Regards,
ssunda.

[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.

[ray.wurlod]

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) 

[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.

[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.

[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.

[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...