473,237 Members | 1,075 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,237 developers and data experts.

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 5826

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

Similar topics

1
by: Mohammad | last post by:
hi when i try to send a blob or Longraw value with more than 32k as parameter to stored procedure in oracle 9i, the following exception raise "ORA-01460 : Unimplemented or Unreasonable...
11
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
3
by: IGotYourDotNet | last post by:
Can anyone point me to some example ASP.NET apps that use Oracle has a backend and C# as the language? I need to figure out how to populate a drop down, and then a datagrid depending on what the...
4
by: Stuart Ferguson | last post by:
I am currently writing code to execute SQL Server 2000 Stored procedured using the ODBC.NET SQL Server Driver. The particular stored procedure I am using has multiple paramaters which I have...
1
by: A1 Ronen | last post by:
Hi all I got problem regarding executing all stored procedure through common procedures where we have different parameter with different names, type and data type The Function is as follows...
0
by: george | last post by:
Hi world! Anyone experienced building gridview in Oracle environment using stored procedures, please help! Right now, I have issues using Oracle SP, I spent a whole day on this and still...
14
by: jehugaleahsa | last post by:
Hello: I am working with Oracle .NET Stored Procedures. I would like to know how to return the results of a SELECT statement. I have tried returning a OracleRefCursor and a DataTable, but...
4
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.