468,765 Members | 1,445 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Executing Oracle Stored Procedures with IN Type Parameter.

debasisdas
8,127 Expert 4TB
This thread contains some of the sample code showing the method of executing Oracle stored procedures and functions from VB .

Hope the user finds them useful.

Oracle Procedure with only IN type as parameter mode.
========================================
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE DEPTINS
  2. (
  3. DNO DEPT.DEPTNO%TYPE,
  4. DN DEPT.DNAME%TYPE,
  5. LC DEPT.LOC%TYPE
  6. )
  7. AUTHID CURRENT_USER
  8. IS
  9. BEGIN
  10. INSERT INTO DEPT VALUES(DNO,DN,LC);
  11. DBMS_OUTPUT.PUT_LINE('ONE ROW INSERTED......!');
  12. EXCEPTION
  13. WHEN DUP_VAL_ON_INDEX THEN
  14. RAISE_APPLICATION_ERROR(-20002,'DUPLICATE VALUE......!');
  15. WHEN OTHERS THEN
  16. RAISE_APPLICATION_ERROR(-20001,'SOME OTHER ERROR ......!');
  17. END;
To call the above Oracle procedure from Vb.
================================
Expand|Select|Wrap|Line Numbers
  1. 'general declaration
  2. Dim CON As New ADODB.Connection
  3. Dim RS As New ADODB.Recordset
  4. Dim PR As New ADODB.Parameter
  5. Dim PR1 As New ADODB.Parameter
  6. Dim PR2 As New ADODB.Parameter
  7. Dim CMD As New ADODB.Command
  8.  
  9. Private Sub Command1_Click()
  10. On Error GoTo MYERR
  11. CON.Open "Provider=MSDAORA.1;Password=DEBASIS;User ID=DEBASIS;Data Source=DAS;Persist Security Info=True"
  12. CMD.ActiveConnection = CON
  13. CMD.CommandType = adCmdStoredProc
  14. CMD.CommandText = "DEPTINS"
  15. Set PR = CMD.CreateParameter("DNO", adNumeric, adParamInput, 2, Val(T1.Text))
  16. CMD.Parameters.Append PR
  17. Set PR1 = CMD.CreateParameter("DN", adVarChar, adParamInput, 14, Trim(T2.Text))
  18. CMD.Parameters.Append PR1
  19. Set PR2 = CMD.CreateParameter("LC", adVarChar, adParamInput, 13, Trim(T3.Text))
  20. CMD.Parameters.Append PR2
  21. CMD.Execute
  22. CON.Close
  23. Exit Sub
  24. MYERR:
  25. I = Val(Mid(Err.Description, 4, 6))
  26. If I = -20002 Then
  27. MsgBox "DUPLICATE ENTRY"
  28. ElseIf I = -20001 Then
  29. MsgBox "SOME OTHER ERROR"
  30. End If
  31. End Sub
Also check
Stored Procedures with both IN & OUT type parameter.
Sep 8 '07 #1
0 5269

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mohammad | last post: by
4 posts views Thread by Rhino | last post: by
3 posts views Thread by IGotYourDotNet | last post: by
4 posts views Thread by Stuart Ferguson | last post: by
reply views Thread by george | last post: by
4 posts views Thread by --CELKO-- | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.