Hİ,
I wrote a routine to run sql string.I used DSExecute function. When i use below, sql there is no problem.
Select NVL(COUNT(*),0) FROM gen_takvim.
But i use below, it return errors.
Select NVL(COUNT(*),0) FROM gen_takvim where tamgun = 'D'
the error is "ORA-00904: "D": invalid identifier"
I tried so many like '''D''' OR : " ' D ' " and more. But i havent archievied yet.
May you help me?
sqlplus routine on windows platform
Moderators: chulett, rschirm, roy
You may need to write your own routine. Your quotes are getting messed up.
Something like this should work. Try it.
Code: Select all
cmd = "sqlplus select ... 'D' "
execute cmd capturing output returning rtncode
Mamu Kim
My routine is below.
Code: Select all
Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
Equate RoutineName To "SQLPLUS"
If Trim(InputArg) = "" Then
Message = DSRMessage("DSTAGE_TRX_E_0011", "No command to execute.", "")
GoTo ErrorExit
End
DSN = Trim(Field(InputArg, ",", 1))
UserName = Trim(Field(InputArg, ",", 2))
Password = Trim(Field(InputArg, ",", 3))
SQL = Field(InputArg, ",", 4,9999)
*
* Determine platform
*
If SYSTEM(91) = 1 Then
SysType = "NT"
End Else
SysType = "UNIX"
End
SQL =ereplace(SQL, "'", ASCII(39))
command = 'echo ' : SQL : '|sqlplus -S ':UserName : '/' : Password : '@' : DSN
command2 = "echo " : SQL : "| sqlplus -S ":UserName : "/*****@" : DSN
Call DSExecute("NT", command, Output, SystemReturnCode)
If Trim(Output) "" Then
Message = Field(Output,Char(254),4)
End
Else
Message = DSRMessage("DSTAGE_TRX_I_0006", "*****", "")
End
* Check contents of output for an error
if Index(Output, "ERROR", 1) > 0 OR Index(Output, "Invalid option",1) > 0 OR Index(Output, "Usage",1) > 0 then
Message = DSRMessage("DSTAGE_TRX_I_0004", "Error when executing command: ", ""):command2:Message
GoTo ErrorExit
End
If SystemReturnCode = 0 Then
Message = Message
Call DSLogInfo(Message, RoutineName)
End Else
Message = DSRMessage("DSTAGE_TRX_I_0004", "Error when executing command: ", ""):command2:Message
GoTo ErrorExit
End
* Exit with no error.
ErrorCode = 0
GoTo NormalExit
* Exit with error, logging warning message first.
ErrorExit:
Call DSLogWarn(Message, RoutineName)
ErrorCode = 1
NormalExit:
Ans=Field(Output,Char(254),4)
InputArg=Field(Output,Char(254),4)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
As Kim said, your quotes are getting messed up. You need to take control of that. You also may need to take control of the fact that, when opening a new shell, DataStage does sometimes strip a layer of quotes - you may, for example, need to "escape" your quote signs.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.