I am getting a "Syntax error" when I try to execute a store procedure in VB 6.0. The stored procedure has 1 input parm and 5 out parms. Based on my readings I thought my code was correct. Can anyone please tell me where my error is. Also, is there some VB function that's let you list or view the stored procedure being executed? Thanks.
VB CODE - Oeacle Stored proc below
Private Sub Get_Env(cnnpage As ADODB.Connection)
Dim cmdenv As ADODB.Command
Dim Rs As ADODB.Recordset
Dim strcorp, strresult, QSQL As String
Dim i As Integer
strcorp = "DD"
Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
End With
QSQL = "{call GET_ENV.IPS_GET_ENV(?, " _
& "{resultset 50 p_parameter}, p_value, rtn_code," _
& " rtn_code_block, num_rows)}"
Set cmdenv = New ADODB.Command
With cmdenv
Set .ActiveConnection = cnnpage
.CommandText = QSQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter("p_sp_type", adVarChar, adParamInput, 2)
End With
Set Rs.Source = cmdenv
' set parameter input value
cmdenv(0) = strcorp
Rs.Open cmdenv
i = 1
' find all the corps
While Not Rs.EOF
strresult = strresult & vbCrLf & i & " :- " & Rs(0) & ", " & Rs(1)
i = i + 1
Rs.MoveNext
Wend
Rs.Close
Set Rs = Nothing
End Sub
ORACLE STORED PROCEDURE
CREATE OR REPLACE PACKAGE GET_ENV
AS
TYPE CharArrayTyp1 IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
TYPE CharArrayTyp2 IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE IPS_GET_ENV(p_sp_type IN VARCHAR2,
p_parameter OUT CharArrayTyp1,
p_value OUT CharArrayTyp2,
rtn_code OUT VARCHAR2,
rtn_code_block OUT VARCHAR2,
num_rows OUT NUMBER);
END GET_ENV;
/
CREATE OR REPLACE PACKAGE BODY GET_ENV
AS
PROCEDURE IPS_GET_ENV(p_sp_type IN VARCHAR2,
p_parameter OUT CharArrayTyp1,
p_value OUT CharArrayTyp2,
rtn_code OUT VARCHAR2,
rtn_code_block OUT VARCHAR2,
num_rows OUT NUMBER)
IS
numrows BINARY_INTEGER := 0;
CURSOR get_env_cursor IS
select parameter,value from ips_env where sp_type = p_sp_type;
BEGIN
rtn_code := 'SU';
rtn_code_block := 'IPS_GET_ENV -- GET PARAMETERS';
open get_env_cursor;
LOOP
numrows:=numrows + 1;
num_rows:=numrows;
FETCH get_env_cursor into p_parameter(numrows),p_value(numrows);
EXIT WHEN get_env_cursor%NOTFOUND;
END LOOP;
close get_env_cursor;
EXCEPTION
WHEN NO_DATA_FOUND THEN rtn_code := 'ND';
WHEN STORAGE_ERROR THEN rtn_code := 'SE';
WHEN TIMEOUT_ON_RESOURCE THEN rtn_code := 'TO';
WHEN OTHERS THEN rtn_code := 'DE';
END;
END GET_ENV;
/
COMMIT;
/
