Calling Oracle stored procs with VB-6 and ADO

[PerlPro]

Hi,

I am trying to call an Oracle stored proc using VB-6 and ADO but getting the following error.

ORA-06550:Line1, column 25
PLS-00201:identifier teamtable must be declared
ORA-06550:Line1, column 7
PL/SQL statement ignored
---------------------------------------------------

The code I am executing in VB-6 is as follows.

Dim mCnn As ADODB.Connection
Dim mrsSelect As ADODB.Recordset
Dim mCmd As ADODB.Command
Dim msSelect As String
Dim mCmdPrmGender As New ADODB.Parameter

Dim sConnect As String 'Declare our connection string

conn = "Provider=OraOLEDB.Oracle.1;Password=projman;Persist Security Info=True;User ID=projman;Data Source=projman"
Set mCnn = New ADODB.Connection
With mCnn
.CommandTimeout = 10
.CursorLocation = adUseClient
.Open conn
End With

msSelect = " {Call opms.get_team1(?, {resultset 1000, teamTable})}"

Set mCmd = New ADODB.Command

With mCmd
.CommandText = msSelect
.CommandType = adCmdText
.ActiveConnection = mCnn
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50)
End With

Set mrsSelect = New ADODB.Recordset

With mrsSelect
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With

Set mrsSelect.Source = mCmd.Execute

MsgBox mrsSelect.Fields(0)

-------------------------------------------

The stored proc in ORacle (package OPMS) is as follows

procedure get_team1 (projid in varchar2, teamTable out teamtabletype) is

nCount binary_integer default 1;
v_projid project_master.projid%type;
v_projname project_master.name%type;
cursor c1 is
Select e.name, r.description, t.charge_rate, t.comments
from team t, employee e, roles_lkup r
where t.projid = v_projid
and t.empid = e.empid
and t.roleid = r.roleid;
teamTableObj teamTableType;
begin
v_projid := projid;
Select name into v_projname
from project_master
where projid = v_projid;
for cursorloopcounter in c1 loop
teamtableObj(nCount) := cursorloopcounter;
nCount := nCount + 1;
end loop;
projname := v_projname;
teamtable := teamTableObj;
exception
when no_data_found then
null;
end get_team1;

--------------------------------------------------
The type specification in the Oracle Package is declared as follows:

type teamRecType is record (
empName employee.name%type,
roleDesc roles_lkup.description%type,
rate team.charge_rate%type,
comments team.comments%type);

type teamTableType is table of documents%rowtype
Index by binary_integer;

Please help. Thanks a lot

 

[fabrice1010]

hello
i have the same problems.
i try to have a result since 3 days

 

[fabrice1010]

your provider is not good
conn = "Provider=OraOLEDB.Oracle.1;Password=projman;Persist Security Info=True;User ID=projman;Data

take provider:
conn = "Provider=MSDAORA;Password=projman;Persist Security Info=True;User ID=projman;Data