Ado And Oracle Stored Procedure Problem Plzz Helpppp

[zrtv]

hi All,
Iam new in oracle stored procedure, i have one stored procedure and it has to be accessed from vb6. my stored procedure as follows

-- employee salary will be entered by the user and itshould show employees less than the the given salary

CREATE OR REPLACE PROCEDURE employee1(empcode OUT VARCHAR2,
empname OUT VARCHAR2, POSITION OUT VARCHAR2, sal IN NUMBER) IS

CURSOR c1 IS SELECT empid, empname, grade FROM empmst
WHERE basicsal < sal;

BEGIN
OPEN c1;
LOOP
FETCH c1
INTO empcode, empname, POSITION;
EXIT
WHEN c1 % NOTFOUND;
END LOOP;

-- my vbcode is the following
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
Set prempcode = New ADODB.Parameter
Set prempname = New ADODB.Parameter
Set prbasicsal = New ADODB.Parameter
Set prposition = New ADODB.Parameter
Set rsempmst = New ADODB.Recordset
rsempmst.CursorType = adOpenStatic

DBDriver = "Provider=MSDAORA.1;Password=ttt;User ID=ttt;Persist Security Info=True"

db.Open DBDriver

Set cm = New ADODB.Command
With cm
.ActiveConnection = db
.CommandType = adCmdStoredProc
.CommandText = "employee1" 'Name of the ORACLE Function, in String
.CommandTimeout = 10
End With
cm.CommandText = "employee1"

cm.Parameters.Append cm.CreateParameter("empcode", adVarChar, adParamOutput, 200)

cm.Parameters.Append cm.CreateParameter("empname", adVarChar, adParamOutput, 200)

cm.Parameters.Append cm.CreateParameter("position", adVarChar, adParamOutput, 200)


cm.Parameters.Append cm.CreateParameter("basicsal1", adNumeric, adParamInput, , Val(Text1.Text))


Set rsempmst.Source = cm.Execute
rsempmst.Open
Do Until rsempmst.EOF
MsgBox (rsempmst("empid"))
rsempmst.MoveNext
Loop

' this code showing error that
Run time error 3001
Arguments are wrong type are acceptable range, or are inconflict with
one another

Plz helppppppppppp
Thanks

[debasisdas]

Sample code with IN & OUT parameters in a oracle procedure
--------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE IN_OUT
(
ENO IN EMP.EMPNO%TYPE,
V_NAME OUT EMP.ENAME%TYPE,
V_SAL  OUT EMP.SAL%TYPE,
V_JOB OUT EMP.JOB%TYPE,
V_DEPTNO OUT EMP.DEPTNO%TYPE
)
IS
BEGIN
SELECT ENAME,SAL,JOB,DEPTNO INTO V_NAME,V_SAL,V_JOB,V_DEPTNO FROM EMP WHERE EMPNO=ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20005,'NO RECORD FOUND......!');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20006,'SOME OTHER ERROR ......!');
END;

sample code to call the above procedure from vb6.0
-----------------------------------------------------------------------------

Dim CON As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim PR As New ADODB.Parameter
Dim PR1 As New ADODB.Parameter
Dim PR2 As New ADODB.Parameter
Dim PR3 As New ADODB.Parameter
Dim PR4 As New ADODB.Parameter
 
Private Sub Command1_Click()
On Error GoTo MYERR
CON.Open "Provider=MSDAORA.1;Password=TIGER;User ID=SCOTT;Data Source=ORCL;Persist Security Info=True"
Dim CMD As New ADODB.Command
CMD.ActiveConnection = CON
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "IN_OUT"
Set PR = CMD.CreateParameter("ENO", adInteger, adParamInput, 4, Int(DC1.Text))
CMD.Parameters.Append PR
Set PR1 = CMD.CreateParameter("V_NAME", adVarChar, adParamOutput, 10)
CMD.Parameters.Append PR1
Set PR2 = CMD.CreateParameter("V_SAL", adNumeric, adParamOutput, 6)
CMD.Parameters.Append PR2
Set PR3 = CMD.CreateParameter("V_JOB", adVarChar, adParamOutput, 9)
CMD.Parameters.Append PR3
Set PR4 = CMD.CreateParameter("V_DEPTNO", adNumeric, adParamOutput, 2)
CMD.Parameters.Append PR4
CMD.Execute
Text1.Text = IIf(Not IsNull(CMD.Parameters("V_NAME").Value), CMD.Parameters("V_NAME").Value, "")
Text3.Text = IIf(Not IsNull(CMD.Parameters("V_SAL").Value), CMD.Parameters("V_SAL").Value, "")
Text2.Text = IIf(Not IsNull(CMD.Parameters("V_JOB").Value), CMD.Parameters("V_JOB").Value, "")
Text4.Text = IIf(Not IsNull(CMD.Parameters("V_DEPTNO").Value), CMD.Parameters("V_DEPTNO").Value, "")
CMD.Cancel
CON.Close
Exit Sub
MYERR:
MsgBox Err.Description
I = Val(Mid(Err.Description, 4, 6))
If I = -20005 Then
MsgBox "NO RECORD FOUND FOR THIS NO......!", vbCritical, "NO DATA"
ElseIf I = -20006 Then
MsgBox "SOME OTHER ERROR......!", vbCritical, "ERROR"
End If
End Sub
 
Private Sub Command2_Click()
Set CON = Nothing
End
End Sub

Check the above sample and change your code accordingly.