Howdy, Stranger!

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

Categories

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)
rs.MoveNext

errdoc:
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;
begin
for get_name_cur in get_name loop
e_ename(table_index) := get_name_cur.ename;
table_index := table_index +1;
end loop;
end;
end;
/

Comments

  • Shawn CarterShawn Carter Member Posts: 0

    ___ { http://forcoder.org } free video tutorials and ebooks about ( Visual Basic, PHP, MATLAB, Ruby, Perl, Visual Basic .NET, C++, Objective-C, C, Python, Go, JavaScript, C#, Scratch, PL/SQL, R, Java, Assembly, Delphi, Swift Transact-SQL, Lua, Prolog, ABAP, Kotlin, Erlang, Awk, F#, Alice, Apex, COBOL, Lisp, Crystal, Bash, Rust, Ada, FoxPro, Fortran, D, Logo, Dart, ML, Clojure, VBScript, Hack, SAS, Julia, Scheme, LabVIEW, Scala )

Sign In or Register to comment.