DSXchange

Posted: **Mon Nov 01, 2010 9:37 am**

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?

Posted: **Mon Nov 01, 2010 9:57 am**

You may need to write your own routine. Your quotes are getting messed up.

Code: Select all

cmd = "sqlplus select ... 'D' "
execute cmd capturing output returning rtncode

Something like this should work. Try it.

Posted: **Mon Nov 01, 2010 10:04 am**

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)

Posted: **Mon Nov 01, 2010 12:59 pm**

Change your ASCII() to a CHAR(). Not sure the value for a single quote. Otherwise looks good.

Posted: **Tue Nov 02, 2010 2:14 am**

But it doesnt work.
when i look sql on oracle site
Select NVL(COUNT(*),0) FROM gen_takvim WHERE TAMGUN= D
because of this, i took Invalid Identifier Error

Posted: **Tue Nov 02, 2010 5:58 am**

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.

DSXchange

sqlplus routine on windows platform

sqlplus routine on windows platform