DSExecute

Post by **admin** » Wed Sep 24, 2003 3:21 pm

Hi,
I am using the following routine to run a SQL command from
DataStage.
This runs fine for calling stored procs without any parameters.
While calling stored procs with parameters by passing parameters
in the command section, the stored procs do not work. No error is
generated.
Any input on this?

Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
Equate RoutineName To "SQLPLUS"

* If InputArg is empty, log a Warning-type message and return
* ErrorCode = 1 (job will abort if called as Before routine).

If Trim(InputArg) = "" Then
Message = DSRMessage("DSTAGE_TRX_E_0011", "No command to
execute.", "")
GoTo ErrorExit
End

UserName = Trim(Field(InputArg, ",", 1))
Password = Trim(Field(InputArg, ",", 2))
DSN = Trim(Field(InputArg, ",", 3))
SQL = Field(InputArg, ",", 4)

*
* Determine platform
*
If SYSTEM(91) = 1 Then
SysType = "NT"
End Else
SysType = "UNIX"
End

command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
Password : "@" : DSN
command2 = "echo " : SQL : "| sqlplus -L ":UserName :
"/*****@" : DSN

Call DSExecute(SysType, command, Output, SystemReturnCode)

If Trim(Output) "" Then
Message = DSRMessage("DSTAGE_TRX_I_0005", "*** Output
from command was: ***", ""):Output
End Else
Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No output
from command ***", "")
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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
command: ", ""):command2: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:

With regards,
Wilcy

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Wed Sep 24, 2003 3:58 pm

Wilcy

Neat trick. I think your SQL is being stripped off. I think you need:

SQL = Field(InputArg, ",", 4, 9999)

Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com

-----Original Message-----
From: Wilcy William [mailto:wilcy@rediffmail.com]
Sent: Wednesday, September 24, 2003 9:21 AM
To: datastage-users@oliver.com
Subject: DSExecute

Hi,
I am using the following routine to run a SQL command from
DataStage.
This runs fine for calling stored procs without any parameters.
While calling stored procs with parameters by passing parameters
in the command section, the stored procs do not work. No error is
generated.
Any input on this?

Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
Equate RoutineName To "SQLPLUS"

* If InputArg is empty, log a Warning-type message and return
* ErrorCode = 1 (job will abort if called as Before routine).

If Trim(InputArg) = "" Then
Message = DSRMessage("DSTAGE_TRX_E_0011", "No command to
execute.", "")
GoTo ErrorExit
End

UserName = Trim(Field(InputArg, ",", 1))
Password = Trim(Field(InputArg, ",", 2))
DSN = Trim(Field(InputArg, ",", 3))
SQL = Field(InputArg, ",", 4)

*
* Determine platform
*
If SYSTEM(91) = 1 Then
SysType = "NT"
End Else
SysType = "UNIX"
End

command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
Password : "@" : DSN
command2 = "echo " : SQL : "| sqlplus -L ":UserName :
"/*****@" : DSN

Call DSExecute(SysType, command, Output, SystemReturnCode)

If Trim(Output) "" Then
Message = DSRMessage("DSTAGE_TRX_I_0005", "*** Output
from command was: ***", ""):Output
End Else
Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No output
from command ***", "")
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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
command: ", ""):command2: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:

With regards,
Wilcy

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Wed Sep 24, 2003 4:57 pm

Kim,
I tried this, but still it doesnt work.
the SQL I am passing is just EXECUTE TEST(00874).

With regards,
Wilcy

On Wed, 24 Sep 2003 Kim Duke wrote :
>Wilcy
>
>Neat trick. I think your SQL is being stripped off. I think you
>need:
>
>SQL = Field(InputArg, ",", 4, 9999)
>
>Thanks Kim.
>Kim Duke
>DsWebMon - Safely Monitor DataStage from the Web
>www.Duke-Consulting.com
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Wednesday, September 24, 2003 9:21 AM
>To: datastage-users@oliver.com
>Subject: DSExecute
>
>Hi,
> I am using the following routine to run a SQL command from
>DataStage.
>This runs fine for calling stored procs without any parameters.
>While calling stored procs with parameters by passing
>parameters
>in the command section, the stored procs do not work. No error
>is
>generated.
>Any input on this?
>
>Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
>Equate RoutineName To "SQLPLUS"
>
>
>
>* If InputArg is empty, log a Warning-type message and return
>* ErrorCode = 1 (job will abort if called as Before routine).
>
> If Trim(InputArg) = "" Then
> Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
>to
>execute.", "")
> GoTo ErrorExit
> End
>
> UserName = Trim(Field(InputArg, ",", 1))
> Password = Trim(Field(InputArg, ",", 2))
> DSN = Trim(Field(InputArg, ",", 3))
> SQL = Field(InputArg, ",", 4)
>
>
>*
>* Determine platform
>*
> If SYSTEM(91) = 1 Then
> SysType = "NT"
> End Else
> SysType = "UNIX"
> End
>
> command = "echo " : SQL : "| sqlplus -L ":UserName : "/"
>:
>Password : "@" : DSN
> command2 = "echo " : SQL : "| sqlplus -L ":UserName :
>"/*****@" : DSN
>
>
> Call DSExecute(SysType, command, Output, SystemReturnCode)
>
> If Trim(Output) "" Then
> Message = DSRMessage("DSTAGE_TRX_I_0005", "***
>Output
> from command was: ***", ""):Output
> End Else
> Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
>output
> from command ***", "")
> 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
>command: ", ""):command2: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:
>
>
>With regards,
>Wilcy
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Wed Sep 24, 2003 7:40 pm

Wilcy

Try quoting it.

"EXECUTE TEST(00874);"

Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com

-----Original Message-----
From: Wilcy William [mailto:wilcy@rediffmail.com]
Sent: Wednesday, September 24, 2003 10:58 AM
To: datastage-users@oliver.com
Subject: Re: RE: DSExecute

Kim,
I tried this, but still it doesnt work.
the SQL I am passing is just EXECUTE TEST(00874).

With regards,
Wilcy

On Wed, 24 Sep 2003 Kim Duke wrote :
>Wilcy
>
>Neat trick. I think your SQL is being stripped off. I think you
>need:
>
>SQL = Field(InputArg, ",", 4, 9999)
>
>Thanks Kim.
>Kim Duke
>DsWebMon - Safely Monitor DataStage from the Web
>www.Duke-Consulting.com
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Wednesday, September 24, 2003 9:21 AM
>To: datastage-users@oliver.com
>Subject: DSExecute
>
>Hi,
> I am using the following routine to run a SQL command from
>DataStage.
>This runs fine for calling stored procs without any parameters.
>While calling stored procs with parameters by passing
>parameters
>in the command section, the stored procs do not work. No error
>is
>generated.
>Any input on this?
>
>Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
>Equate RoutineName To "SQLPLUS"
>
>
>
>* If InputArg is empty, log a Warning-type message and return
>* ErrorCode = 1 (job will abort if called as Before routine).
>
> If Trim(InputArg) = "" Then
> Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
>to
>execute.", "")
> GoTo ErrorExit
> End
>
> UserName = Trim(Field(InputArg, ",", 1))
> Password = Trim(Field(InputArg, ",", 2))
> DSN = Trim(Field(InputArg, ",", 3))
> SQL = Field(InputArg, ",", 4)
>
>
>*
>* Determine platform
>*
> If SYSTEM(91) = 1 Then
> SysType = "NT"
> End Else
> SysType = "UNIX"
> End
>
> command = "echo " : SQL : "| sqlplus -L ":UserName : "/"
>:
>Password : "@" : DSN
> command2 = "echo " : SQL : "| sqlplus -L ":UserName :
>"/*****@" : DSN
>
>
> Call DSExecute(SysType, command, Output, SystemReturnCode)
>
> If Trim(Output) "" Then
> Message = DSRMessage("DSTAGE_TRX_I_0005", "***
>Output
> from command was: ***", ""):Output
> End Else
> Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
>output
> from command ***", "")
> 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
>command: ", ""):command2: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:
>
>
>With regards,
>Wilcy
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Fri Sep 26, 2003 5:24 pm

Kim,
Quoting doesnt help.

With regards,
Wilcy

On Thu, 25 Sep 2003 Kim Duke wrote :
>Wilcy
>
>Try quoting it.
>
>"EXECUTE TEST(00874);"
>
>Thanks Kim.
>Kim Duke
>DsWebMon - Safely Monitor DataStage from the Web
>www.Duke-Consulting.com
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Wednesday, September 24, 2003 10:58 AM
>To: datastage-users@oliver.com
>Subject: Re: RE: DSExecute
>
>Kim,
> I tried this, but still it doesnt work.
>the SQL I am passing is just EXECUTE TEST(00874).
>
>With regards,
>Wilcy
>
>On Wed, 24 Sep 2003 Kim Duke wrote :
> >Wilcy
> >
> >Neat trick. I think your SQL is being stripped off. I think
>you
> >need:
> >
> >SQL = Field(InputArg, ",", 4, 9999)
> >
> >Thanks Kim.
> >Kim Duke
> >DsWebMon - Safely Monitor DataStage from the Web
> >www.Duke-Consulting.com
> >
> >
> >-----Original Message-----
> > From: Wilcy William [mailto:wilcy@rediffmail.com]
> >Sent: Wednesday, September 24, 2003 9:21 AM
> >To: datastage-users@oliver.com
> >Subject: DSExecute
> >
> >Hi,
> > I am using the following routine to run a SQL command
> from
> >DataStage.
> >This runs fine for calling stored procs without any
>parameters.
> >While calling stored procs with parameters by passing
> >parameters
> >in the command section, the stored procs do not work. No
>error
> >is
> >generated.
> >Any input on this?
> >
> >Deffun DSRMessage(A1, A2, A3) Calling
>"*DataStage*DSR_MESSAGE"
> >Equate RoutineName To "SQLPLUS"
> >
> >
> >
> >* If InputArg is empty, log a Warning-type message and return
> >* ErrorCode = 1 (job will abort if called as Before routine).
> >
> > If Trim(InputArg) = "" Then
> > Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
> >to
> >execute.", "")
> > GoTo ErrorExit
> > End
> >
> > UserName = Trim(Field(InputArg, ",", 1))
> > Password = Trim(Field(InputArg, ",", 2))
> > DSN = Trim(Field(InputArg, ",", 3))
> > SQL = Field(InputArg, ",", 4)
> >
> >
> >*
> >* Determine platform
> >*
> > If SYSTEM(91) = 1 Then
> > SysType = "NT"
> > End Else
> > SysType = "UNIX"
> > End
> >
> > command = "echo " : SQL : "| sqlplus -L ":UserName :
>"/"
> >:
> >Password : "@" : DSN
> > command2 = "echo " : SQL : "| sqlplus -L ":UserName :
> >"/*****@" : DSN
> >
> >
> > Call DSExecute(SysType, command, Output,
>SystemReturnCode)
> >
> > If Trim(Output) "" Then
> > Message = DSRMessage("DSTAGE_TRX_I_0005", "***
> >Output
> > from command was: ***", ""):Output
> > End Else
> > Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
> >output
> > from command ***", "")
> > 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
> >command: ", ""):command2: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:
> >
> >
> >With regards,
> >Wilcy
> >
> >___________________________________________________
> >Art meets Anesthesia; Shefali Weds Dr. Raman.
> >Rediff Matchmaker strikes another interesting match !!
> >Visit http://matchmaker.rediff.com?1
> >
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Fri Sep 26, 2003 5:31 pm

Wilcy

I give up. Dont know what to say.

Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com

-----Original Message-----
From: Wilcy William [mailto:wilcy@rediffmail.com]
Sent: Friday, September 26, 2003 11:24 AM
To: datastage-users@oliver.com
Subject: Re: RE: RE: DSExecute

Kim,
Quoting doesnt help.

With regards,
Wilcy

On Thu, 25 Sep 2003 Kim Duke wrote :
>Wilcy
>
>Try quoting it.
>
>"EXECUTE TEST(00874);"
>
>Thanks Kim.
>Kim Duke
>DsWebMon - Safely Monitor DataStage from the Web
>www.Duke-Consulting.com
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Wednesday, September 24, 2003 10:58 AM
>To: datastage-users@oliver.com
>Subject: Re: RE: DSExecute
>
>Kim,
> I tried this, but still it doesnt work.
>the SQL I am passing is just EXECUTE TEST(00874).
>
>With regards,
>Wilcy
>
>On Wed, 24 Sep 2003 Kim Duke wrote :
> >Wilcy
> >
> >Neat trick. I think your SQL is being stripped off. I think
>you
> >need:
> >
> >SQL = Field(InputArg, ",", 4, 9999)
> >
> >Thanks Kim.
> >Kim Duke
> >DsWebMon - Safely Monitor DataStage from the Web
> >www.Duke-Consulting.com
> >
> >
> >-----Original Message-----
> > From: Wilcy William [mailto:wilcy@rediffmail.com]
> >Sent: Wednesday, September 24, 2003 9:21 AM
> >To: datastage-users@oliver.com
> >Subject: DSExecute
> >
> >Hi,
> > I am using the following routine to run a SQL command
> from
> >DataStage.
> >This runs fine for calling stored procs without any
>parameters.
> >While calling stored procs with parameters by passing
> >parameters
> >in the command section, the stored procs do not work. No
>error
> >is
> >generated.
> >Any input on this?
> >
> >Deffun DSRMessage(A1, A2, A3) Calling
>"*DataStage*DSR_MESSAGE"
> >Equate RoutineName To "SQLPLUS"
> >
> >
> >
> >* If InputArg is empty, log a Warning-type message and return
> >* ErrorCode = 1 (job will abort if called as Before routine).
> >
> > If Trim(InputArg) = "" Then
> > Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
> >to
> >execute.", "")
> > GoTo ErrorExit
> > End
> >
> > UserName = Trim(Field(InputArg, ",", 1))
> > Password = Trim(Field(InputArg, ",", 2))
> > DSN = Trim(Field(InputArg, ",", 3))
> > SQL = Field(InputArg, ",", 4)
> >
> >
> >*
> >* Determine platform
> >*
> > If SYSTEM(91) = 1 Then
> > SysType = "NT"
> > End Else
> > SysType = "UNIX"
> > End
> >
> > command = "echo " : SQL : "| sqlplus -L ":UserName :
>"/"
> >:
> >Password : "@" : DSN
> > command2 = "echo " : SQL : "| sqlplus -L ":UserName :
> >"/*****@" : DSN
> >
> >
> > Call DSExecute(SysType, command, Output,
>SystemReturnCode)
> >
> > If Trim(Output) "" Then
> > Message = DSRMessage("DSTAGE_TRX_I_0005", "***
> >Output
> > from command was: ***", ""):Output
> > End Else
> > Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
> >output
> > from command ***", "")
> > 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
> >command: ", ""):command2: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:
> >
> >
> >With regards,
> >Wilcy
> >
> >___________________________________________________
> >Art meets Anesthesia; Shefali Weds Dr. Raman.
> >Rediff Matchmaker strikes another interesting match !!
> >Visit http://matchmaker.rediff.com?1
> >
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Fri Sep 26, 2003 8:54 pm

You may have to something like what is necessary on the command line
before-job
options like "" cmd ""

experiment and you should be able to pass your value

Kim Duke wrote:

>Wilcy
>
>I give up. Dont know what to say.
>
>Thanks Kim.
>Kim Duke
>DsWebMon - Safely Monitor DataStage from the Web
>www.Duke-Consulting.com
>
>
>-----Original Message-----
>From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Friday, September 26, 2003 11:24 AM
>To: datastage-users@oliver.com
>Subject: Re: RE: RE: DSExecute
>
>Kim,
> Quoting doesnt help.
>
>With regards,
>Wilcy
>
>On Thu, 25 Sep 2003 Kim Duke wrote :
>
>
>>Wilcy
>>
>>Try quoting it.
>>
>>"EXECUTE TEST(00874);"
>>
>>Thanks Kim.
>>Kim Duke
>>DsWebMon - Safely Monitor DataStage from the Web
>>www.Duke-Consulting.com
>>
>>
>>-----Original Message-----
>>From: Wilcy William [mailto:wilcy@rediffmail.com]
>>Sent: Wednesday, September 24, 2003 10:58 AM
>>To: datastage-users@oliver.com
>>Subject: Re: RE: DSExecute
>>
>>Kim,
>> I tried this, but still it doesnt work.
>>the SQL I am passing is just EXECUTE TEST(00874).
>>
>>With regards,
>>Wilcy
>>
>>On Wed, 24 Sep 2003 Kim Duke wrote :
>>
>>
>>>Wilcy
>>>
>>>Neat trick. I think your SQL is being stripped off. I think
>>>
>>>
>>you
>>
>>
>>>need:
>>>
>>>SQL = Field(InputArg, ",", 4, 9999)
>>>
>>>Thanks Kim.
>>>Kim Duke
>>>DsWebMon - Safely Monitor DataStage from the Web
>>>www.Duke-Consulting.com
>>>
>>>
>>>-----Original Message-----
>>>From: Wilcy William [mailto:wilcy@rediffmail.com]
>>>Sent: Wednesday, September 24, 2003 9:21 AM
>>>To: datastage-users@oliver.com
>>>Subject: DSExecute
>>>
>>>Hi,
>>> I am using the following routine to run a SQL command
>>>
>>>
>>from
>>
>>
>>>DataStage.
>>>This runs fine for calling stored procs without any
>>>
>>>
>>parameters.
>>
>>
>>>While calling stored procs with parameters by passing
>>>parameters
>>>in the command section, the stored procs do not work. No
>>>
>>>
>>error
>>
>>
>>>is
>>>generated.
>>>Any input on this?
>>>
>>>Deffun DSRMessage(A1, A2, A3) Calling
>>>
>>>
>>"*DataStage*DSR_MESSAGE"
>>
>>
>>>Equate RoutineName To "SQLPLUS"
>>>
>>>
>>>
>>>* If InputArg is empty, log a Warning-type message and return
>>>* ErrorCode = 1 (job will abort if called as Before routine).
>>>
>>> If Trim(InputArg) = "" Then
>>> Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
>>>to
>>>execute.", "")
>>> GoTo ErrorExit
>>> End
>>>
>>> UserName = Trim(Field(InputArg, ",", 1))
>>> Password = Trim(Field(InputArg, ",", 2))
>>> DSN = Trim(Field(InputArg, ",", 3))
>>> SQL = Field(InputArg, ",", 4)
>>>
>>>
>>>*
>>>* Determine platform
>>>*
>>> If SYSTEM(91) = 1 Then
>>> SysType = "NT"
>>> End Else
>>> SysType = "UNIX"
>>> End
>>>
>>> command = "echo " : SQL : "| sqlplus -L ":UserName :
>>>
>>>
>>"/"
>>
>>
>>>:
>>>Password : "@" : DSN
>>> command2 = "echo " : SQL : "| sqlplus -L ":UserName :
>>>"/*****@" : DSN
>>>
>>>
>>> Call DSExecute(SysType, command, Output,
>>>
>>>
>>SystemReturnCode)
>>
>>
>>> If Trim(Output) "" Then
>>> Message = DSRMessage("DSTAGE_TRX_I_0005", "***
>>>Output
>>> from command was: ***", ""):Output
>>> End Else
>>> Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
>>>output
>>> from command ***", "")
>>> 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
>>>command: ", ""):command2: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:
>>>
>>>
>>>With regards,
>>>Wilcy
>>>
>>>___________________________________________________
>>>Art meets Anesthesia; Shefali Weds Dr. Raman.
>>>Rediff Matchmaker strikes another interesting match !!
>>>Visit http://matchmaker.rediff.com?1
>>>
>>>
>>>
>>___________________________________________________
>>Art meets Anesthesia; Shefali Weds Dr. Raman.
>>Rediff Matchmaker strikes another interesting match !!
>>Visit http://matchmaker.rediff.com?1
>>
>>
>>
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>
>
>
>

Post by **admin** » Fri Sep 26, 2003 8:55 pm

Kim Duke wrote:

>Wilcy
>
>I give up. Dont know what to say.
>
>Thanks Kim.
>Kim Duke
>DsWebMon - Safely Monitor DataStage from the Web
>www.Duke-Consulting.com
>
>
>-----Original Message-----
>From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Friday, September 26, 2003 11:24 AM
>To: datastage-users@oliver.com
>Subject: Re: RE: RE: DSExecute
>
>Kim,
> Quoting doesnt help.
>
>With regards,
>Wilcy
>
>On Thu, 25 Sep 2003 Kim Duke wrote :
>
>
>>Wilcy
>>
>>Try quoting it.
>>
>>"EXECUTE TEST(00874);"
>>
>>Thanks Kim.
>>Kim Duke
>>DsWebMon - Safely Monitor DataStage from the Web
>>www.Duke-Consulting.com
>>
>>
>>-----Original Message-----
>>From: Wilcy William [mailto:wilcy@rediffmail.com]
>>Sent: Wednesday, September 24, 2003 10:58 AM
>>To: datastage-users@oliver.com
>>Subject: Re: RE: DSExecute
>>
>>Kim,
>> I tried this, but still it doesnt work.
>>the SQL I am passing is just EXECUTE TEST(00874).
>>
>>With regards,
>>Wilcy
>>
>>On Wed, 24 Sep 2003 Kim Duke wrote :
>>
>>
>>>Wilcy
>>>
>>>Neat trick. I think your SQL is being stripped off. I think
>>>
>>>
>>you
>>
>>
>>>need:
>>>
>>>SQL = Field(InputArg, ",", 4, 9999)
>>>
>>>Thanks Kim.
>>>Kim Duke
>>>DsWebMon - Safely Monitor DataStage from the Web
>>>www.Duke-Consulting.com
>>>
>>>
>>>-----Original Message-----
>>>From: Wilcy William [mailto:wilcy@rediffmail.com]
>>>Sent: Wednesday, September 24, 2003 9:21 AM
>>>To: datastage-users@oliver.com
>>>Subject: DSExecute
>>>
>>>Hi,
>>> I am using the following routine to run a SQL command
>>>
>>>
>>from
>>
>>
>>>DataStage.
>>>This runs fine for calling stored procs without any
>>>
>>>
>>parameters.
>>
>>
>>>While calling stored procs with parameters by passing
>>>parameters
>>>in the command section, the stored procs do not work. No
>>>
>>>
>>error
>>
>>
>>>is
>>>generated.
>>>Any input on this?
>>>
>>>Deffun DSRMessage(A1, A2, A3) Calling
>>>
>>>
>>"*DataStage*DSR_MESSAGE"
>>
>>
>>>Equate RoutineName To "SQLPLUS"
>>>
>>>
>>>
>>>* If InputArg is empty, log a Warning-type message and return
>>>* ErrorCode = 1 (job will abort if called as Before routine).
>>>
>>> If Trim(InputArg) = "" Then
>>> Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
>>>to
>>>execute.", "")
>>> GoTo ErrorExit
>>> End
>>>
>>> UserName = Trim(Field(InputArg, ",", 1))
>>> Password = Trim(Field(InputArg, ",", 2))
>>> DSN = Trim(Field(InputArg, ",", 3))
>>> SQL = Field(InputArg, ",", 4)
>>>
>>>
>>>*
>>>* Determine platform
>>>*
>>> If SYSTEM(91) = 1 Then
>>> SysType = "NT"
>>> End Else
>>> SysType = "UNIX"
>>> End
>>>
>>> command = "echo " : SQL : "| sqlplus -L ":UserName :
>>>
>>>
>>"/"
>>
>>
>>>:
>>>Password : "@" : DSN
>>> command2 = "echo " : SQL : "| sqlplus -L ":UserName :
>>>"/*****@" : DSN
>>>
>>>
>>> Call DSExecute(SysType, command, Output,
>>>
>>>
>>SystemReturnCode)
>>
>>
>>> If Trim(Output) "" Then
>>> Message = DSRMessage("DSTAGE_TRX_I_0005", "***
>>>Output
>>> from command was: ***", ""):Output
>>> End Else
>>> Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
>>>output
>>> from command ***", "")
>>> 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
>>>command: ", ""):command2: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:
>>>
>>>
>>>With regards,
>>>Wilcy
>>>
>>>___________________________________________________
>>>Art meets Anesthesia; Shefali Weds Dr. Raman.
>>>Rediff Matchmaker strikes another interesting match !!
>>>Visit http://matchmaker.rediff.com?1
>>>
>>>
>>>
>>___________________________________________________
>>Art meets Anesthesia; Shefali Weds Dr. Raman.
>>Rediff Matchmaker strikes another interesting match !!
>>Visit http://matchmaker.rediff.com?1
>>
>>
>>
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>
>
>
>

Post by **admin** » Sat Sep 27, 2003 1:18 pm

Your routine attempts to cater for both Windows and Unix. Which one are you
actually testing it in?

In Windows, the line:

command = "echo " : SQL : "| sqlplus -L ":UserName : "/" : Password : "@"
: DSN

Needs to have the single quotes removed so that it looks like:

command = "echo " : SQL : "| sqlplus -L ":UserName : "/" : Password : "@" :
DSN

However, I dont have a Unix environment to test this. The above would not
explain your error anyway, as it needs the quotes removed to work with or
without parameters.

What output do you get from DSExecute? I guess Im wondering if the problem
is whether the command is presented to the OS correctly or in how SQL*Plus
is interpreting it.

If Unix Needs the single quotes, then I have to wonder how it will handle
the single quotes around your parameter. Perhaps your SQL needs to look
like:

EXECUTE TEST(00874) (They are 2 single quotes, not double quotes)

Or

EXECUTE TEST(0874)

Sorry, it is many years since I have used Unix, so Im not sure about these,
but it might be something to think about.

I assume you are using the Oracle 9 client (and therefore DataStage 6), as
the -L option does not exist in Oracle 8i (just checking).

-----Original Message-----
From: Wilcy William [mailto:wilcy@rediffmail.com]
Sent: Thursday, 25 September 2003 1:21 AM
To: datastage-users@oliver.com
Subject: DSExecute

Hi,
I am using the following routine to run a SQL command from
DataStage.
This runs fine for calling stored procs without any parameters.
While calling stored procs with parameters by passing parameters
in the command section, the stored procs do not work. No error is
generated.
Any input on this?

Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
Equate RoutineName To "SQLPLUS"

* If InputArg is empty, log a Warning-type message and return
* ErrorCode = 1 (job will abort if called as Before routine).

If Trim(InputArg) = "" Then
Message = DSRMessage("DSTAGE_TRX_E_0011", "No command to
execute.", "")
GoTo ErrorExit
End

UserName = Trim(Field(InputArg, ",", 1))
Password = Trim(Field(InputArg, ",", 2))
DSN = Trim(Field(InputArg, ",", 3))
SQL = Field(InputArg, ",", 4)

*
* Determine platform
*
If SYSTEM(91) = 1 Then
SysType = "NT"
End Else
SysType = "UNIX"
End

command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
Password : "@" : DSN
command2 = "echo " : SQL : "| sqlplus -L ":UserName :
"/*****@" : DSN

Call DSExecute(SysType, command, Output, SystemReturnCode)

If Trim(Output) "" Then
Message = DSRMessage("DSTAGE_TRX_I_0005", "*** Output
from command was: ***", ""):Output
End Else
Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No output
from command ***", "")
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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
command: ", ""):command2: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:

With regards,
Wilcy

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Tue Sep 30, 2003 7:34 pm

David,
I tried the options u gave me. No luck.
Ia m using 9i client. I have a problem with passing parameters
only.
If the stored proc has no parameters it executes correctly.

With regards,
Wilcy

On Sat, 27 Sep 2003 David Barham wrote :
>Your routine attempts to cater for both Windows and Unix. Which
>one are you
>actually testing it in?
>
>In Windows, the line:
>
>command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
>Password : "@"
>: DSN
>
>Needs to have the single quotes removed so that it looks like:
>
>command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
>Password : "@" :
>DSN
>
>However, I dont have a Unix environment to test this. The above
>would not
>explain your error anyway, as it needs the quotes removed to work
>with or
>without parameters.
>
>What output do you get from DSExecute? I guess Im wondering if
>the problem
>is whether the command is presented to the OS correctly or in how
>SQL*Plus
>is interpreting it.
>
>If Unix Needs the single quotes, then I have to wonder how it
>will handle
>the single quotes around your parameter. Perhaps your SQL needs
>to look
>like:
>
>EXECUTE TEST(00874) (They are 2 single quotes, not
>double quotes)
>
>Or
>
>EXECUTE TEST(0874)
>
>Sorry, it is many years since I have used Unix, so Im not sure
>about these,
>but it might be something to think about.
>
>I assume you are using the Oracle 9 client (and therefore
>DataStage 6), as
>the -L option does not exist in Oracle 8i (just checking).
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Thursday, 25 September 2003 1:21 AM
>To: datastage-users@oliver.com
>Subject: DSExecute
>
>Hi,
> I am using the following routine to run a SQL command from
>DataStage.
>This runs fine for calling stored procs without any parameters.
>While calling stored procs with parameters by passing
>parameters
>in the command section, the stored procs do not work. No error
>is
>generated.
>Any input on this?
>
>Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
>Equate RoutineName To "SQLPLUS"
>
>
>
>* If InputArg is empty, log a Warning-type message and return
>* ErrorCode = 1 (job will abort if called as Before routine).
>
> If Trim(InputArg) = "" Then
> Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
>to
>execute.", "")
> GoTo ErrorExit
> End
>
> UserName = Trim(Field(InputArg, ",", 1))
> Password = Trim(Field(InputArg, ",", 2))
> DSN = Trim(Field(InputArg, ",", 3))
> SQL = Field(InputArg, ",", 4)
>
>
>*
>* Determine platform
>*
> If SYSTEM(91) = 1 Then
> SysType = "NT"
> End Else
> SysType = "UNIX"
> End
>
> command = "echo " : SQL : "| sqlplus -L ":UserName : "/"
>:
>Password : "@" : DSN
> command2 = "echo " : SQL : "| sqlplus -L ":UserName :
>"/*****@" : DSN
>
>
> Call DSExecute(SysType, command, Output, SystemReturnCode)
>
> If Trim(Output) "" Then
> Message = DSRMessage("DSTAGE_TRX_I_0005", "***
>Output
> from command was: ***", ""):Output
> End Else
> Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
>output
> from command ***", "")
> 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
>command: ", ""):command2: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:
>
>
>With regards,
>Wilcy
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>
>

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Tue Sep 30, 2003 8:17 pm

You didnt mention the DataStage version, so Ill assume DataStage 6.

What output is generated by the DSExecute?

What do you mean when you say it doesnt work with parameters? It does
nothing? It does the wrong thing?

-----Original Message-----
From: Wilcy William [mailto:wilcy@rediffmail.com]
Sent: Wednesday, 1 October 2003 5:35 AM
To: datastage-users@oliver.com
Subject: Re: RE: DSExecute

David,
I tried the options u gave me. No luck.
Ia m using 9i client. I have a problem with passing parameters
only.
If the stored proc has no parameters it executes correctly.

With regards,
Wilcy

On Sat, 27 Sep 2003 David Barham wrote :
>Your routine attempts to cater for both Windows and Unix. Which
>one are you
>actually testing it in?
>
>In Windows, the line:
>
>command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
>Password : "@"
>: DSN
>
>Needs to have the single quotes removed so that it looks like:
>
>command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
>Password : "@" :
>DSN
>
>However, I dont have a Unix environment to test this. The above
>would not
>explain your error anyway, as it needs the quotes removed to work
>with or
>without parameters.
>
>What output do you get from DSExecute? I guess Im wondering if
>the problem
>is whether the command is presented to the OS correctly or in how
>SQL*Plus
>is interpreting it.
>
>If Unix Needs the single quotes, then I have to wonder how it
>will handle
>the single quotes around your parameter. Perhaps your SQL needs
>to look
>like:
>
>EXECUTE TEST(00874) (They are 2 single quotes, not
>double quotes)
>
>Or
>
>EXECUTE TEST(0874)
>
>Sorry, it is many years since I have used Unix, so Im not sure
>about these,
>but it might be something to think about.
>
>I assume you are using the Oracle 9 client (and therefore
>DataStage 6), as
>the -L option does not exist in Oracle 8i (just checking).
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Thursday, 25 September 2003 1:21 AM
>To: datastage-users@oliver.com
>Subject: DSExecute
>
>Hi,
> I am using the following routine to run a SQL command from
>DataStage.
>This runs fine for calling stored procs without any parameters.
>While calling stored procs with parameters by passing
>parameters
>in the command section, the stored procs do not work. No error
>is
>generated.
>Any input on this?
>
>Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
>Equate RoutineName To "SQLPLUS"
>
>
>
>* If InputArg is empty, log a Warning-type message and return
>* ErrorCode = 1 (job will abort if called as Before routine).
>
> If Trim(InputArg) = "" Then
> Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
>to
>execute.", "")
> GoTo ErrorExit
> End
>
> UserName = Trim(Field(InputArg, ",", 1))
> Password = Trim(Field(InputArg, ",", 2))
> DSN = Trim(Field(InputArg, ",", 3))
> SQL = Field(InputArg, ",", 4)
>
>
>*
>* Determine platform
>*
> If SYSTEM(91) = 1 Then
> SysType = "NT"
> End Else
> SysType = "UNIX"
> End
>
> command = "echo " : SQL : "| sqlplus -L ":UserName : "/"
>:
>Password : "@" : DSN
> command2 = "echo " : SQL : "| sqlplus -L ":UserName :
>"/*****@" : DSN
>
>
> Call DSExecute(SysType, command, Output, SystemReturnCode)
>
> If Trim(Output) "" Then
> Message = DSRMessage("DSTAGE_TRX_I_0005", "***
>Output
> from command was: ***", ""):Output
> End Else
> Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
>output
> from command ***", "")
> 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
>command: ", ""):command2: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:
>
>
>With regards,
>Wilcy
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>
>

___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Tue Sep 30, 2003 9:17 pm

DataStage 6 is the version used.
It does nothing. But if there is no parameter and the value is
harcoded in the job. It works fine.

The message I get is

Untitled1..JobControl (SQLPLUS): Executed command: echo EXECUTE
TEST(00874);| sqlplus -L pm2_dba/*****@pmd1
*** Output from command was: ***

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 30 14:34:19
2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>
PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle9i Enterprise Edition Release
9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

On Wed, 01 Oct 2003 David Barham wrote :
>You didnt mention the DataStage version, so Ill assume
>DataStage 6.
>
>What output is generated by the DSExecute?
>
>What do you mean when you say it doesnt work with parameters?
>It does
>nothing? It does the wrong thing?
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Wednesday, 1 October 2003 5:35 AM
>To: datastage-users@oliver.com
>Subject: Re: RE: DSExecute
>
>David,
> I tried the options u gave me. No luck.
>Ia m using 9i client. I have a problem with passing parameters
>only.
>If the stored proc has no parameters it executes correctly.
>
>With regards,
>Wilcy
>
>On Sat, 27 Sep 2003 David Barham wrote :
> >Your routine attempts to cater for both Windows and Unix.
>Which
> >one are you
> >actually testing it in?
> >
> >In Windows, the line:
> >
> >command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
> >Password : "@"
> >: DSN
> >
> >Needs to have the single quotes removed so that it looks
>like:
> >
> >command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
> >Password : "@" :
> >DSN
> >
> >However, I dont have a Unix environment to test this. The
>above
> >would not
> >explain your error anyway, as it needs the quotes removed to
>work
> >with or
> >without parameters.
> >
> >What output do you get from DSExecute? I guess Im wondering
>if
> >the problem
> >is whether the command is presented to the OS correctly or in
>how
> >SQL*Plus
> >is interpreting it.
> >
> >If Unix Needs the single quotes, then I have to wonder how it
> >will handle
> >the single quotes around your parameter. Perhaps your SQL
>needs
> >to look
> >like:
> >
> >EXECUTE TEST(00874) (They are 2 single quotes, not
> >double quotes)
> >
> >Or
> >
> >EXECUTE TEST(0874)
> >
> >Sorry, it is many years since I have used Unix, so Im not
>sure
> >about these,
> >but it might be something to think about.
> >
> >I assume you are using the Oracle 9 client (and therefore
> >DataStage 6), as
> >the -L option does not exist in Oracle 8i (just checking).
> >
> >
> >-----Original Message-----
> > From: Wilcy William [mailto:wilcy@rediffmail.com]
> >Sent: Thursday, 25 September 2003 1:21 AM
> >To: datastage-users@oliver.com
> >Subject: DSExecute
> >
> >Hi,
> > I am using the following routine to run a SQL command
> from
> >DataStage.
> >This runs fine for calling stored procs without any
>parameters.
> >While calling stored procs with parameters by passing
> >parameters
> >in the command section, the stored procs do not work. No
>error
> >is
> >generated.
> >Any input on this?
> >
> >Deffun DSRMessage(A1, A2, A3) Calling
>"*DataStage*DSR_MESSAGE"
> >Equate RoutineName To "SQLPLUS"
> >
> >
> >
> >* If InputArg is empty, log a Warning-type message and return
> >* ErrorCode = 1 (job will abort if called as Before routine).
> >
> > If Trim(InputArg) = "" Then
> > Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
> >to
> >execute.", "")
> > GoTo ErrorExit
> > End
> >
> > UserName = Trim(Field(InputArg, ",", 1))
> > Password = Trim(Field(InputArg, ",", 2))
> > DSN = Trim(Field(InputArg, ",", 3))
> > SQL = Field(InputArg, ",", 4)
> >
> >
> >*
> >* Determine platform
> >*
> > If SYSTEM(91) = 1 Then
> > SysType = "NT"
> > End Else
> > SysType = "UNIX"
> > End
> >
> > command = "echo " : SQL : "| sqlplus -L ":UserName :
>"/"
> >:
> >Password : "@" : DSN
> > command2 = "echo " : SQL : "| sqlplus -L ":UserName :
> >"/*****@" : DSN
> >
> >
> > Call DSExecute(SysType, command, Output,
>SystemReturnCode)
> >
> > If Trim(Output) "" Then
> > Message = DSRMessage("DSTAGE_TRX_I_0005", "***
> >Output
> > from command was: ***", ""):Output
> > End Else
> > Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
> >output
> > from command ***", "")
> > 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
> >command: ", ""):command2: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:
> >
> >
> >With regards,
> >Wilcy
> >
> >___________________________________________________
> >Art meets Anesthesia; Shefali Weds Dr. Raman.
> >Rediff Matchmaker strikes another interesting match !!
> >Visit http://matchmaker.rediff.com?1
> >
> >
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>
>

Best Regards,
Wilcy
___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Wed Oct 01, 2003 11:50 am

Wilcy,

Try this on your DSExecute line:

Call DSExecute(SysType, ":command:", Output,SystemReturnCode)

That is, I am deliberately adding a double quote to the start and end of the
entire command string. I think the problem is to do with how DSExecute
issues the command. The single quotes seem to disappear and adding the
double quotes to the string passed to DSExecute seems to stop them from
disappearing. Works under windows anyway. I was also able to replicate
your problem in a Windows environment.

>From what I can tell, DSExecute issues to UniVerse
DOS /C command line
for windows and
SH -C command line
for Unix. Adding the quotes clarifies this to UniVerse. Ray could probably
give a more accurate explanation as I have negligible experience with
UniVerse.

Thanks for this ... it explains another problem I had a long time ago where
I couldnt use single quotes in a command path to enclose white space (eg
c:program files...).

David

-----Original Message-----
From: Wilcy William [mailto:wilcy@rediffmail.com]
Sent: Wednesday, 1 October 2003 7:17 AM
To: datastage-users@oliver.com
Subject: Re: RE: RE: DSExecute

DataStage 6 is the version used.
It does nothing. But if there is no parameter and the value is
harcoded in the job. It works fine.

The message I get is

Untitled1..JobControl (SQLPLUS): Executed command: echo EXECUTE
TEST(00874);| sqlplus -L pm2_dba/*****@pmd1
*** Output from command was: ***

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 30 14:34:19
2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>
PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle9i Enterprise Edition Release
9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

On Wed, 01 Oct 2003 David Barham wrote :
>You didnt mention the DataStage version, so Ill assume
>DataStage 6.
>
>What output is generated by the DSExecute?
>
>What do you mean when you say it doesnt work with parameters?
>It does
>nothing? It does the wrong thing?
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Wednesday, 1 October 2003 5:35 AM
>To: datastage-users@oliver.com
>Subject: Re: RE: DSExecute
>
>David,
> I tried the options u gave me. No luck.
>Ia m using 9i client. I have a problem with passing parameters
>only.
>If the stored proc has no parameters it executes correctly.
>
>With regards,
>Wilcy
>
>On Sat, 27 Sep 2003 David Barham wrote :
> >Your routine attempts to cater for both Windows and Unix.
>Which
> >one are you
> >actually testing it in?
> >
> >In Windows, the line:
> >
> >command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
> >Password : "@"
> >: DSN
> >
> >Needs to have the single quotes removed so that it looks
>like:
> >
> >command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
> >Password : "@" :
> >DSN
> >
> >However, I dont have a Unix environment to test this. The
>above
> >would not
> >explain your error anyway, as it needs the quotes removed to
>work
> >with or
> >without parameters.
> >
> >What output do you get from DSExecute? I guess Im wondering
>if
> >the problem
> >is whether the command is presented to the OS correctly or in
>how
> >SQL*Plus
> >is interpreting it.
> >
> >If Unix Needs the single quotes, then I have to wonder how it
> >will handle
> >the single quotes around your parameter. Perhaps your SQL
>needs
> >to look
> >like:
> >
> >EXECUTE TEST(00874) (They are 2 single quotes, not
> >double quotes)
> >
> >Or
> >
> >EXECUTE TEST(0874)
> >
> >Sorry, it is many years since I have used Unix, so Im not
>sure
> >about these,
> >but it might be something to think about.
> >
> >I assume you are using the Oracle 9 client (and therefore
> >DataStage 6), as
> >the -L option does not exist in Oracle 8i (just checking).
> >
> >
> >-----Original Message-----
> > From: Wilcy William [mailto:wilcy@rediffmail.com]
> >Sent: Thursday, 25 September 2003 1:21 AM
> >To: datastage-users@oliver.com
> >Subject: DSExecute
> >
> >Hi,
> > I am using the following routine to run a SQL command
> from
> >DataStage.
> >This runs fine for calling stored procs without any
>parameters.
> >While calling stored procs with parameters by passing
> >parameters
> >in the command section, the stored procs do not work. No
>error
> >is
> >generated.
> >Any input on this?
> >
> >Deffun DSRMessage(A1, A2, A3) Calling
>"*DataStage*DSR_MESSAGE"
> >Equate RoutineName To "SQLPLUS"
> >
> >
> >
> >* If InputArg is empty, log a Warning-type message and return
> >* ErrorCode = 1 (job will abort if called as Before routine).
> >
> > If Trim(InputArg) = "" Then
> > Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
> >to
> >execute.", "")
> > GoTo ErrorExit
> > End
> >
> > UserName = Trim(Field(InputArg, ",", 1))
> > Password = Trim(Field(InputArg, ",", 2))
> > DSN = Trim(Field(InputArg, ",", 3))
> > SQL = Field(InputArg, ",", 4)
> >
> >
> >*
> >* Determine platform
> >*
> > If SYSTEM(91) = 1 Then
> > SysType = "NT"
> > End Else
> > SysType = "UNIX"
> > End
> >
> > command = "echo " : SQL : "| sqlplus -L ":UserName :
>"/"
> >:
> >Password : "@" : DSN
> > command2 = "echo " : SQL : "| sqlplus -L ":UserName :
> >"/*****@" : DSN
> >
> >
> > Call DSExecute(SysType, command, Output,
>SystemReturnCode)
> >
> > If Trim(Output) "" Then
> > Message = DSRMessage("DSTAGE_TRX_I_0005", "***
> >Output
> > from command was: ***", ""):Output
> > End Else
> > Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
> >output
> > from command ***", "")
> > 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
> >command: ", ""):command2: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:
> >
> >
> >With regards,
> >Wilcy
> >
> >___________________________________________________
> >Art meets Anesthesia; Shefali Weds Dr. Raman.
> >Rediff Matchmaker strikes another interesting match !!
> >Visit http://matchmaker.rediff.com?1
> >
> >
>
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>
>

Best Regards,
Wilcy
___________________________________________________
Art meets Anesthesia; Shefali Weds Dr. Raman.
Rediff Matchmaker strikes another interesting match !!
Visit http://matchmaker.rediff.com?1

Post by **admin** » Thu Oct 02, 2003 6:17 pm

David,
Still no luck. I tried ith quoted in all posible combinations.

With regards,
Wilcy

On Wed, 01 Oct 2003 David Barham wrote :
>Wilcy,
>
>
>Try this on your DSExecute line:
>
>Call DSExecute(SysType, ":command:", Output,SystemReturnCode)
>
>That is, I am deliberately adding a double quote to the start and end of the
>entire command string. I think the problem is to do with how DSExecute
>issues the command. The single quotes seem to disappear and adding the
>double quotes to the string passed to DSExecute seems to stop them from
>disappearing. Works under windows anyway. I was also able to replicate
>your problem in a Windows environment.
>
> From what I can tell, DSExecute issues to UniVerse
> DOS /C command line
>for windows and
> SH -C command line
>for Unix. Adding the quotes clarifies this to UniVerse. Ray could probably
>give a more accurate explanation as I have negligible experience with
>UniVerse.
>
>Thanks for this ... it explains another problem I had a long time ago where
>I couldnt use single quotes in a command path to enclose white space (eg
>c:program files...).
>
>David
>
>
>-----Original Message-----
> From: Wilcy William [mailto:wilcy@rediffmail.com]
>Sent: Wednesday, 1 October 2003 7:17 AM
>To: datastage-users@oliver.com
>Subject: Re: RE: RE: DSExecute
>
>DataStage 6 is the version used.
>It does nothing. But if there is no parameter and the value is
>harcoded in the job. It works fine.
>
>The message I get is
>
> Untitled1..JobControl (SQLPLUS): Executed command: echo EXECUTE
>TEST(00874);| sqlplus -L pm2_dba/*****@pmd1
>*** Output from command was: ***
>
>SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 30 14:34:19
>2003
>
>Copyright (c) 1982, 2002, Oracle Corporation. All rights
>reserved.
>
>
>Connected to:
>Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
>With the Partitioning, OLAP and Oracle Data Mining options
>JServer Release 9.2.0.1.0 - Production
>
>SQL>
>PL/SQL procedure successfully completed.
>
>SQL> Disconnected from Oracle9i Enterprise Edition Release
>9.2.0.1.0 - 64bit Production
>With the Partitioning, OLAP and Oracle Data Mining options
>JServer Release 9.2.0.1.0 - Production
>
>
>On Wed, 01 Oct 2003 David Barham wrote :
> >You didnt mention the DataStage version, so Ill assume
> >DataStage 6.
> >
> >What output is generated by the DSExecute?
> >
> >What do you mean when you say it doesnt work with parameters?
> >It does
> >nothing? It does the wrong thing?
> >
> >
> >-----Original Message-----
> > From: Wilcy William [mailto:wilcy@rediffmail.com]
> >Sent: Wednesday, 1 October 2003 5:35 AM
> >To: datastage-users@oliver.com
> >Subject: Re: RE: DSExecute
> >
> >David,
> > I tried the options u gave me. No luck.
> >Ia m using 9i client. I have a problem with passing parameters
> >only.
> >If the stored proc has no parameters it executes correctly.
> >
> >With regards,
> >Wilcy
> >
> >On Sat, 27 Sep 2003 David Barham wrote :
> > >Your routine attempts to cater for both Windows and Unix.
> >Which
> > >one are you
> > >actually testing it in?
> > >
> > >In Windows, the line:
> > >
> > >command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
> > >Password : "@"
> > >: DSN
> > >
> > >Needs to have the single quotes removed so that it looks
> >like:
> > >
> > >command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
> > >Password : "@" :
> > >DSN
> > >
> > >However, I dont have a Unix environment to test this. The
> >above
> > >would not
> > >explain your error anyway, as it needs the quotes removed to
> >work
> > >with or
> > >without parameters.
> > >
> > >What output do you get from DSExecute? I guess Im wondering
> >if
> > >the problem
> > >is whether the command is presented to the OS correctly or in
> >how
> > >SQL*Plus
> > >is interpreting it.
> > >
> > >If Unix Needs the single quotes, then I have to wonder how it
> > >will handle
> > >the single quotes around your parameter. Perhaps your SQL
> >needs
> > >to look
> > >like:
> > >
> > >EXECUTE TEST(00874) (They are 2 single quotes, not
> > >double quotes)
> > >
> > >Or
> > >
> > >EXECUTE TEST(0874)
> > >
> > >Sorry, it is many years since I have used Unix, so Im not
> >sure
> > >about these,
> > >but it might be something to think about.
> > >
> > >I assume you are using the Oracle 9 client (and therefore
> > >DataStage 6), as
> > >the -L option does not exist in Oracle 8i (just checking).
> > >
> > >
> > >-----Original Message-----
> > > From: Wilcy William [mailto:wilcy@rediffmail.com]
> > >Sent: Thursday, 25 September 2003 1:21 AM
> > >To: datastage-users@oliver.com
> > >Subject: DSExecute
> > >
> > >Hi,
> > > I am using the following routine to run a SQL command
> > from
> > >DataStage.
> > >This runs fine for calling stored procs without any
> >parameters.
> > >While calling stored procs with parameters by passing
> > >parameters
> > >in the command section, the stored procs do not work. No
> >error
> > >is
> > >generated.
> > >Any input on this?
> > >
> > >Deffun DSRMessage(A1, A2, A3) Calling
> >"*DataStage*DSR_MESSAGE"
> > >Equate RoutineName To "SQLPLUS"
> > >
> > >
> > >
> > >* If InputArg is empty, log a Warning-type message and return
> > >* ErrorCode = 1 (job will abort if called as Before routine).
> > >
> > > If Trim(InputArg) = "" Then
> > > Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
> > >to
> > >execute.", "")
> > > GoTo ErrorExit
> > > End
> > >
> > > UserName = Trim(Field(InputArg, ",", 1))
> > > Password = Trim(Field(InputArg, ",", 2))
> > > DSN = Trim(Field(InputArg, ",", 3))
> > > SQL = Field(InputArg, ",", 4)
> > >
> > >
> > >*
> > >* Determine platform
> > >*
> > > If SYSTEM(91) = 1 Then
> > > SysType = "NT"
> > > End Else
> > > SysType = "UNIX"
> > > End
> > >
> > > command = "echo " : SQL : "| sqlplus -L ":UserName :
> >"/"
> > >:
> > >Password : "@" : DSN
> > > command2 = "echo " : SQL : "| sqlplus -L ":UserName :
> > >"/*****@" : DSN
> > >
> > >
> > > Call DSExecute(SysType, command, Output,
> >SystemReturnCode)
> > >
> > > If Trim(Output) "" Then
> > > Message = DSRMessage("DSTAGE_TRX_I_0005", "***
> > >Output
> > > from command was: ***", ""):Output
> > > End Else
> > > Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
> > >output
> > > from command ***", "")
> > > 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
> > >command: ", ""):command2: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:
> > >
> > >
> > >With regards,
> > >Wilcy
> > >
> > >___________________________________________________
> > >Art meets Anesthesia; Shefali Weds Dr. Raman.
> > >Rediff Matchmaker strikes another interesting match !!
> > >Visit http://matchmaker.rediff.com?1
> > >
> > >
> >
> >___________________________________________________
> >Art meets Anesthesia; Shefali Weds Dr. Raman.
> >Rediff Matchmaker strikes another interesting match !!
> >Visit http://matchmaker.rediff.com?1
> >
> >
>
>
>Best Regards,
>Wilcy
>___________________________________________________
>Art meets Anesthesia; Shefali Weds Dr. Raman.
>Rediff Matchmaker strikes another interesting match !!
>Visit http://matchmaker.rediff.com?1
>
>

Post by **admin** » Fri Oct 03, 2003 2:03 am

did you try using " " cmd " " as I said in earlier email?

Wilcy William wrote:

>David,
> Still no luck. I tried ith quoted in all posible combinations.
>
>With regards,
>Wilcy
>
>On Wed, 01 Oct 2003 David Barham wrote :
>
>
>>Wilcy,
>>
>>
>>Try this on your DSExecute line:
>>
>>Call DSExecute(SysType, ":command:", Output,SystemReturnCode)
>>
>>That is, I am deliberately adding a double quote to the start and end of the
>>entire command string. I think the problem is to do with how DSExecute
>>issues the command. The single quotes seem to disappear and adding the
>>double quotes to the string passed to DSExecute seems to stop them from
>>disappearing. Works under windows anyway. I was also able to replicate
>>your problem in a Windows environment.
>>
>>From what I can tell, DSExecute issues to UniVerse
>> DOS /C command line
>>for windows and
>> SH -C command line
>>for Unix. Adding the quotes clarifies this to UniVerse. Ray could probably
>>give a more accurate explanation as I have negligible experience with
>>UniVerse.
>>
>>Thanks for this ... it explains another problem I had a long time ago where
>>I couldnt use single quotes in a command path to enclose white space (eg
>>c:program files...).
>>
>>David
>>
>>
>>-----Original Message-----
>>From: Wilcy William [mailto:wilcy@rediffmail.com]
>>Sent: Wednesday, 1 October 2003 7:17 AM
>>To: datastage-users@oliver.com
>>Subject: Re: RE: RE: DSExecute
>>
>>DataStage 6 is the version used.
>>It does nothing. But if there is no parameter and the value is
>>harcoded in the job. It works fine.
>>
>>The message I get is
>>
>> Untitled1..JobControl (SQLPLUS): Executed command: echo EXECUTE
>>TEST(00874);| sqlplus -L pm2_dba/*****@pmd1
>>*** Output from command was: ***
>>
>>SQL*Plus: Release 9.2.0.1.0 - Production on Tue Sep 30 14:34:19
>>2003
>>
>>Copyright (c) 1982, 2002, Oracle Corporation. All rights
>>reserved.
>>
>>
>>Connected to:
>>Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
>>With the Partitioning, OLAP and Oracle Data Mining options
>>JServer Release 9.2.0.1.0 - Production
>>
>>SQL>
>>PL/SQL procedure successfully completed.
>>
>>SQL> Disconnected from Oracle9i Enterprise Edition Release
>>9.2.0.1.0 - 64bit Production
>>With the Partitioning, OLAP and Oracle Data Mining options
>>JServer Release 9.2.0.1.0 - Production
>>
>>
>>On Wed, 01 Oct 2003 David Barham wrote :
>>
>>
>>>You didnt mention the DataStage version, so Ill assume
>>>DataStage 6.
>>>
>>>What output is generated by the DSExecute?
>>>
>>>What do you mean when you say it doesnt work with parameters?
>>>It does
>>>nothing? It does the wrong thing?
>>>
>>>
>>>-----Original Message-----
>>>From: Wilcy William [mailto:wilcy@rediffmail.com]
>>>Sent: Wednesday, 1 October 2003 5:35 AM
>>>To: datastage-users@oliver.com
>>>Subject: Re: RE: DSExecute
>>>
>>>David,
>>> I tried the options u gave me. No luck.
>>>Ia m using 9i client. I have a problem with passing parameters
>>>only.
>>>If the stored proc has no parameters it executes correctly.
>>>
>>>With regards,
>>>Wilcy
>>>
>>>On Sat, 27 Sep 2003 David Barham wrote :
>>>
>>>
>>>>Your routine attempts to cater for both Windows and Unix.
>>>>
>>>>
>>>Which
>>>
>>>
>>>>one are you
>>>>actually testing it in?
>>>>
>>>>In Windows, the line:
>>>>
>>>>command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
>>>>Password : "@"
>>>>: DSN
>>>>
>>>>Needs to have the single quotes removed so that it looks
>>>>
>>>>
>>>like:
>>>
>>>
>>>>command = "echo " : SQL : "| sqlplus -L ":UserName : "/" :
>>>>Password : "@" :
>>>>DSN
>>>>
>>>>However, I dont have a Unix environment to test this. The
>>>>
>>>>
>>>above
>>>
>>>
>>>>would not
>>>>explain your error anyway, as it needs the quotes removed to
>>>>
>>>>
>>>work
>>>
>>>
>>>>with or
>>>>without parameters.
>>>>
>>>>What output do you get from DSExecute? I guess Im wondering
>>>>
>>>>
>>>if
>>>
>>>
>>>>the problem
>>>>is whether the command is presented to the OS correctly or in
>>>>
>>>>
>>>how
>>>
>>>
>>>>SQL*Plus
>>>>is interpreting it.
>>>>
>>>>If Unix Needs the single quotes, then I have to wonder how it
>>>>will handle
>>>>the single quotes around your parameter. Perhaps your SQL
>>>>
>>>>
>>>needs
>>>
>>>
>>>>to look
>>>>like:
>>>>
>>>>EXECUTE TEST(00874) (They are 2 single quotes, not
>>>>double quotes)
>>>>
>>>>Or
>>>>
>>>>EXECUTE TEST(0874)
>>>>
>>>>Sorry, it is many years since I have used Unix, so Im not
>>>>
>>>>
>>>sure
>>>
>>>
>>>>about these,
>>>>but it might be something to think about.
>>>>
>>>>I assume you are using the Oracle 9 client (and therefore
>>>>DataStage 6), as
>>>>the -L option does not exist in Oracle 8i (just checking).
>>>>
>>>>
>>>>-----Original Message-----
>>>>From: Wilcy William [mailto:wilcy@rediffmail.com]
>>>>Sent: Thursday, 25 September 2003 1:21 AM
>>>>To: datastage-users@oliver.com
>>>>Subject: DSExecute
>>>>
>>>>Hi,
>>>> I am using the following routine to run a SQL command
>>>>
>>>>
>>>from
>>>
>>>
>>>>DataStage.
>>>>This runs fine for calling stored procs without any
>>>>
>>>>
>>>parameters.
>>>
>>>
>>>>While calling stored procs with parameters by passing
>>>>parameters
>>>>in the command section, the stored procs do not work. No
>>>>
>>>>
>>>error
>>>
>>>
>>>>is
>>>>generated.
>>>>Any input on this?
>>>>
>>>>Deffun DSRMessage(A1, A2, A3) Calling
>>>>
>>>>
>>>"*DataStage*DSR_MESSAGE"
>>>
>>>
>>>>Equate RoutineName To "SQLPLUS"
>>>>
>>>>
>>>>
>>>>* If InputArg is empty, log a Warning-type message and return
>>>>* ErrorCode = 1 (job will abort if called as Before routine).
>>>>
>>>> If Trim(InputArg) = "" Then
>>>> Message = DSRMessage("DSTAGE_TRX_E_0011", "No command
>>>>to
>>>>execute.", "")
>>>> GoTo ErrorExit
>>>> End
>>>>
>>>> UserName = Trim(Field(InputArg, ",", 1))
>>>> Password = Trim(Field(InputArg, ",", 2))
>>>> DSN = Trim(Field(InputArg, ",", 3))
>>>> SQL = Field(InputArg, ",", 4)
>>>>
>>>>
>>>>*
>>>>* Determine platform
>>>>*
>>>> If SYSTEM(91) = 1 Then
>>>> SysType = "NT"
>>>> End Else
>>>> SysType = "UNIX"
>>>> End
>>>>
>>>> command = "echo " : SQL : "| sqlplus -L ":UserName :
>>>>
>>>>
>>>"/"
>>>
>>>
>>>>:
>>>>Password : "@" : DSN
>>>> command2 = "echo " : SQL : "| sqlplus -L ":UserName :
>>>>"/*****@" : DSN
>>>>
>>>>
>>>> Call DSExecute(SysType, command, Output,
>>>>
>>>>
>>>SystemReturnCode)
>>>
>>>
>>>> If Trim(Output) "" Then
>>>> Message = DSRMessage("DSTAGE_TRX_I_0005", "***
>>>>Output
>>>> from command was: ***", ""):Output
>>>> End Else
>>>> Message = DSRMessage("DSTAGE_TRX_I_0006", "*** No
>>>>output
>>>> from command ***", "")
>>>> 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 = DSRMessage("DSTAGE_TRX_I_0003", "Executed
>>>>command: ", ""):command2: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:
>>>>
>>>>
>>>>With regards,
>>>>Wilcy
>>>>
>>>>___________________________________________________
>>>>Art meets Anesthesia; Shefali Weds Dr. Raman.
>>>>Rediff Matchmaker strikes another interesting match !!
>>>>Visit http://matchmaker.rediff.com?1
>>>>
>>>>
>>>>
>>>>
>>>___________________________________________________
>>>Art meets Anesthesia; Shefali Weds Dr. Raman.
>>>Rediff Matchmaker strikes another interesting match !!
>>>Visit http://matchmaker.rediff.com?1
>>>
>>>
>>>
>>>
>>Best Regards,
>>Wilcy
>>___________________________________________________
>>Art meets Anesthesia; Shefali Weds Dr. Raman.
>>Rediff Matchmaker strikes another interesting match !!
>>Visit http://matchmaker.rediff.com?1
>>
>>
>>
>>
>
>
>