Recordset from Oracle

Hi All
Every one is woried about return of tables from a stroed procedure.I've creat a stroed procedure which return a record table but receiving at VB
it is showing only one record i think there is a problem i VB coding
not in the stroed procedure .So pls check it out and give your reply
Private Sub Form_Load()
On Error GoTo errdoc

Dim str1 As String
str1 = "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=xyz;Persist Security Info=True"
cn.Open str1
str = "{call empname.get_enames(?, {resultset 3, e_ename})}"
Set cmd = New ADODB.Command
With cmd
.CommandText = str
.CommandType = adCmdText
.ActiveConnection = cn
Set prm = .CreateParameter("e_deptno", adBigInt, adParamInput, 3, 10)
.Parameters.Append prm
End With
Set rs = New ADODB.Recordset
'prm = 10

Set rs = cmd.Execute
'MsgBox rs.RecordCount
'Set DataGrid1.DataSource = rs
MsgBox rs.Fields(0)

If Err.Description <> "" Then
MsgBox (Err.Description)
End If

End Sub
create or replace package empname as
type tbl_ename is table of emp.ename%type
index by binary_integer;
procedure get_enames(e_deptno in emp.deptno%type , e_ename out tbl_ename);
end empname ;
create or replace
package body empname is
procedure get_enames(e_deptno in emp.deptno%type , e_ename out tbl_ename) is
cursor get_name is
select ename from emp where deptno=e_deptno ;
table_index number:=1;
for get_name_cur in get_name loop
e_ename(table_index) := get_name_cur.ename;
table_index := table_index +1;
end loop;

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!