Executing Oracle Stored Procedures with both IN & OUT type parameter.

[debasisdas]

Oracle Procedure with both IN & OUT type as parameter mode.
=============== =============== ==========
[CODE=oracle]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,D EPTNO INTO V_NAME,V_SAL,V_ JOB,V_DEPTNO FROM EMP WHERE EMPNO=ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATI ON_ERROR(-20005,'NO RECORD FOUND......!');
WHEN OTHERS THEN
RAISE_APPLICATI ON_ERROR(-20006,'SOME OTHER ERROR ......!');
END;[/CODE]

To call the above Oracle procedure from Vb.
=============== =============== ==
[CODE=vb]
'general declarationDim CON As New ADODB.Connectio n
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=MSDAO RA.1;Password=D EBASIS;User ID=DEBASIS;Data Source=DAS;Pers ist Security Info=True"
Dim CMD As New ADODB.Command
CMD.ActiveConne ction = CON
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "IN_OUT"
Set PR = CMD.CreateParam eter("ENO", adInteger, adParamInput, 4, Int(DC1.Text))
CMD.Parameters. Append PR
Set PR1 = CMD.CreateParam eter("V_NAME", adVarChar, adParamOutput, 10)
CMD.Parameters. Append PR1
Set PR2 = CMD.CreateParam eter("V_SAL", adNumeric, adParamOutput, 6)
CMD.Parameters. Append PR2
Set PR3 = CMD.CreateParam eter("V_JOB", adVarChar, adParamOutput, 9)
CMD.Parameters. Append PR3
Set PR4 = CMD.CreateParam eter("V_DEPTNO" , adNumeric, adParamOutput, 2)
CMD.Parameters. Append PR4
CMD.Execute
Text1.Text = IIf(Not IsNull(CMD.Para meters("V_NAME" ).Value), CMD.Parameters( "V_NAME").Value , "")
Text3.Text = IIf(Not IsNull(CMD.Para meters("V_SAL") .Value), CMD.Parameters( "V_SAL").Va lue, "")
Text2.Text = IIf(Not IsNull(CMD.Para meters("V_JOB") .Value), CMD.Parameters( "V_JOB").Va lue, "")
Text4.Text = IIf(Not IsNull(CMD.Para meters("V_DEPTN O").Value), CMD.Parameters( "V_DEPTNO").Val ue, "")
CMD.Cancel
CON.Close
Exit Sub
MYERR:
MsgBox Err.Description
I = Val(Mid(Err.Des cription, 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
[/CODE]


Also check Stored Procedures with IN OUT type parameter.