 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,508
# 1
Sep 8 '07
| |
Oracle Procedure with both IN & OUT type as parameter mode.
======================================== - 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;
To call the above Oracle procedure from Vb.
================================ -
'general declarationDim 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=DEBASIS;User ID=DEBASIS;Data Source=DAS;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
-
|
