DirecTutor.com

Oracle Stored Procedure output parameter does not works in eScript

When invoking an Oracle Stored Procedure, if it is invoked using VB Script, the output parameter works as expected. If invoked using eScript, the output parameter does not get updated.Create a Stored Procedure.Set up a test to execute each of the scripts. The VB script returns the value, the eScript does not.

This is caused by a Product Defect.  Bug 10558509 , Work Around this by using VB Script instead of eScript.
 
Stored Procedure:  
 
CREATE OR REPLACE PROCEDURE Test(a IN VARCHAR2, b OUT VARCHAR2) IS ch_chk1 VARCHAR2(20); 
BEGIN 
SELECT row_id, ou_num 
INTO b, ch_chk1 
FROM siebel.s_org_ext 
WHERE NAME = a; 
END;
VB Script:  
 
Sub F_Exe 
 
  Dim objCon As Object 
  Dim objCmd As Object 
  Dim inParm As Object 
  Dim outParm As Object 
  Dim a As String 
  Dim b As String 
  a = "Daniel Test2" 
 
  Set objCon = CreateObject("ADODB.Connection") 
  objCon.Open "DSN=External", "SIEBEL", "SIEBEL" 
  Set objCmd = CreateObject("ADODB.Command") 
  Set objCmd.ActiveConnection = objCon 
  objCmd.CommandText = "sys.Test" 
  objCmd.CommandType = 4 
  Set inParm = objCmd.CreateParameter("a", 200, 1, 20, a) 
  Set outParm = objCmd.CreateParameter("b", 200, 2, 20) 
  objCmd.Parameters.Append inParm 
  objCmd.Parameters.Append outParm 
  objCmd.Execute 
  b = objCmd.Parameters("b").Value 
 
  TheApplication().RaiseErrorText b 
 
  objCon.Close 
 
End Sub
eScript:  
 
function F_Exe(Inputs, Outputs) 
{ 
  var objCon; 
  var objCmd; 
  var inParm; 
  var outParm; 
  var a = "Daniel Test2"; 
  var b; 
 
  objCon = COMCreateObject("ADODB.Connection"); 
  objCon.Open("DSN=External", "SIEBEL", "SIEBEL"); 
  objCmd = COMCreateObject("ADODB.Command"); 
  objCmd.ActiveConnection = objCon; 
  objCmd.CommandText = "sys.Test"; 
  objCmd.CommandType = 4; 
  inParm = objCmd.CreateParameter("a", 200, 1, 20, a); 
  outParm = objCmd.CreateParameter("b", 200, 2, 20); 
  objCmd.Parameters.Append(inParm); 
  objCmd.Parameters.Append(outParm); 
  objCmd.Execute(); 
  b = objCmd.Parameters("b").Value; 
 
  TheApplication().RaiseErrorText("b="+b);   //The output parameter b is null!!! 
 
  objCon.Close(); 
}
Tags
Siebel
Recent content