Samrat S Roy 29.Mar.10 03:17 AM a Web browser LC LSXAll ReleasesAll Platforms
Hi Forum,
I have written a procedure in Oracle which works fine when I call it from SQLPLUS interface, But when I try to call it from lotus script it does not returns anything. I am pretty new to lcconnection so may be I am getting wrong in some place.
need your help on this, If you can please go through the code once may be you can catch the error!
here is the Oracle Procedure:
=========================================
create or replace PROCEDURE TEST
( param1 IN VARCHAR2
, param2 IN VARCHAR2
, param3 out VARCHAR2) AS
BEGIN
INSERT INTO "SAMRAT"."TBLCOUNTRY"
(
COUNTRYCODE ,
COUNTRY
)
VALUES
(
param1 ,
param2
) ;
COMMIT ;
param3 := 'sa';
END TEST;
=========================================
Here is the LotusScript Agent:
=========================================
Option Public
Option Explicit
UseLSX "*lsxlc"
Sub Initialize()
Call conntest()
End Sub
Sub conntest()
Dim Con As New LCConnection ("oracle")
Dim Parms As New LCFieldList
Dim Result As New LCFieldList
Dim Parm As LCField
' set the connection property to the stored procedure name
Con.Procedure = "test"
' now connect
Con.Connect
Set Parm = Parms.Append ("param1", LCTYPE_TEXT)
Parm.text = "pak21"
Set Parm = Parms.Append ("param2", LCTYPE_TEXT)
Parm.text = "pakisthan"
Set Parm = Parms.Append ("param3", LCTYPE_TEXT)
Parm.text = "asdf"
' using the fieldlist containing the field with the
' stored procedure parameter, call the stored procedure
Dim count As Variant
count = Con.Call (Parms, 1, result)
'@Forum: Here Count returns -1
If count = 0 Then
msgbox "No results were generated from the procedure call."
ElseIf count = LCCOUNT_UNKNOWN Then
msgbox "A result set was generated but the number of results is unknown."
Else
msgbox "The stored procedure returned " & count & " records."
End If
con.Disconnect
End Sub
=========================================
Note: also if the Param3 value within the oracle procedure returns a long string say 'Samrat' the the agent throws the error:[numeric or value error: character string buffer too small]
-> how do i set the size of 'Result' container?
Return to top
call Oracle Procedure from lcconnec... (Samrat S Roy 29.Mar.10)
. . 
Print this page
