developerWorks  >  Lotus  >  Forums & community  >  Notes/Domino 6 and 7 Forum

Notes/Domino 6 and 7 Forum

developerWorks




Error when calling oracle stored procedure using LSX
Anand Kishan Sarda 09/19/2013 09:02 AM
Applications Development 7.0.2 Windows 2003


Hi there,

I copied the Sample LC LSX Agent to Call an Oracle Stored Procedure from the following.


http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/index.jsp?topic=%2Fcom.ibm.designer.domino.main.doc%2FH_EXAMPLE_SAMPLE_LC_LSX_AGENT_TO_CALL_AN_ORACLE_STORED_PROCEDURE_9674_OVER.html



I modified the .Server, .username, etc with the relevant values. When I run the agent it generates the following error

"this operation cannot be performed on a fieldlist with no fields"



Following the Store Procedure (it works fine when run from sqlplus client)

PROCEDURE test_conn_txt1 ( p_in IN VARCHAR2 ,p_out OUT VARCHAR2) IS
l_out VARCHAR2(100);
BEGIN

--p_out :=p_in;
l_out := 'Testing';--||to_char(p_in);
p_out := l_out;
insert into xx_test1 values (to_char(p_in),to_char(p_out));
commit;

END ;




Following is the Agent code

Option Public
Option Explicit

UseLSX "*lsxlc"

Sub Initialize
Dim sess As New LCSession
Dim conn As New LCConnection ("oracle8")

'set the connection parameters...
conn.Server = "ora_prod"
conn.UserId = "user1"
conn.Password = "password"

src.OracleTextFormat = "UTF8"


'connect to the database... conn.Connect
'set the stored procedure owner and stored procedure name...
'conn.Owner = "OWNER"
conn.Procedure = "test_conn_txt1"
conn.connect

'set Fieldnames property with any output parameters declared in the stored procedure...
'conn.Fieldnames = "p_out"

'declare any fields and fieldlists for input/output data...
Dim input_fieldlist As New LCFieldList
Dim output_parms As New LCFieldlist
Dim in_field_int As New LCField (1, LCTYPE_INT)
Dim in_field_text As New LCField (1, LCTYPE_TEXT)
Dim out1 As New LCField (1, LCTYPE_INT)
Dim out2 As New LCField (1, LCTYPE_TEXT)
Dim out As Double

'set the input parameters of the stored procedure...
Set in_field_text = input_fieldlist.Append ("p_in", LCTYPE_TEXT)
in_field_text.Text = "testing of stored procedure for input values"

'Set in_field_text = input_fieldlist.Append ("p_out", LCTYPE_TEXT)
'in_field_text.Text = "testing of stored procedure for input values"

Set out2 = output_parms.Append ("p_out", LCTYPE_TEXT)
out2.Text = "testing of stored procedure for input values"


'with the input parameters set, call the stored procedure...
'the declared output_parms fieldlist will hold the output parameters of the stored procedure...

out = conn.Call (input_fieldlist, 1, output_parms)

'fetch parameter(s) into the output_parms fieldlist...

out = conn.Fetch (output_parms)
'retrieve the parameter(s) from the output_parms fieldlist...

Set out1 = output_parms.GetField (1)
'Set out2 = output_parms.GetField (2)
'use the output parameter value(s) in your script...

Print "Output parameter 1 is " & out1.Value(0)
'Print "Output parameter 2 is " & out2.Text(0)

conn.Disconnect

End Sub

Go back