8,127
Hope the user finds them useful.
Oracle Procedure with only IN type as parameter mode.
========================================
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE PROCEDURE DEPTINS
- (
- DNO DEPT.DEPTNO%TYPE,
- DN DEPT.DNAME%TYPE,
- LC DEPT.LOC%TYPE
- )
- AUTHID CURRENT_USER
- IS
- BEGIN
- INSERT INTO DEPT VALUES(DNO,DN,LC);
- DBMS_OUTPUT.PUT_LINE('ONE ROW INSERTED......!');
- EXCEPTION
- WHEN DUP_VAL_ON_INDEX THEN
- RAISE_APPLICATION_ERROR(-20002,'DUPLICATE VALUE......!');
- WHEN OTHERS THEN
- RAISE_APPLICATION_ERROR(-20001,'SOME OTHER ERROR ......!');
- END;
================================
Expand|Select|Wrap|Line Numbers
- 'general declaration
- 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 CMD As New ADODB.Command
- 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"
- CMD.ActiveConnection = CON
- CMD.CommandType = adCmdStoredProc
- CMD.CommandText = "DEPTINS"
- Set PR = CMD.CreateParameter("DNO", adNumeric, adParamInput, 2, Val(T1.Text))
- CMD.Parameters.Append PR
- Set PR1 = CMD.CreateParameter("DN", adVarChar, adParamInput, 14, Trim(T2.Text))
- CMD.Parameters.Append PR1
- Set PR2 = CMD.CreateParameter("LC", adVarChar, adParamInput, 13, Trim(T3.Text))
- CMD.Parameters.Append PR2
- CMD.Execute
- CON.Close
- Exit Sub
- MYERR:
- I = Val(Mid(Err.Description, 4, 6))
- If I = -20002 Then
- MsgBox "DUPLICATE ENTRY"
- ElseIf I = -20001 Then
- MsgBox "SOME OTHER ERROR"
- End If
- End Sub
Stored Procedures with both IN & OUT type parameter.
