×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How do you call an Oracle stored procedure from a VB program.

How do you call an Oracle stored procedure from a VB program.

How do you call an Oracle stored procedure from a VB program.

(OP)
I need help. Do you know the commands to call a stored procedure in oracle from a VB program. I have a VB code pulling data from one database and importing it to the Oracle database tables. Once this is done I need to run a stored procedure that will process the data. I have a standard ODBC connection. Can you give me an example or tell me where I can find the information I am looking for. Thanks.

RE: How do you call an Oracle stored procedure from a VB program.

Since PL/SQL is proprietary to Oracle, I'm not sure that there's a way to directly call a stored procedure through ODBC. What you could try however, is putting an on insert trigger on a table created for the purpose, inserting into this table through ODBC after your loading is done, and letting the trigger call your procedure.

RE: How do you call an Oracle stored procedure from a VB program.

SirPacs
You can run a Pass_thru Query, with the "Begin procname; End;" as the SQL.
Set ODBC Timeout to 0 if you expect a long running procedure
--Jim

RE: How do you call an Oracle stored procedure from a VB program.

You can make a call to an Oracle stored procedure in VB using ADO Command object. Please see the following sample code.

Dim Cmd As New ADODB.Command

Cmd.ActiveConnection = "DSN=oracle;UID=user"
Cmd.CommandText = "myADOProc"
Cmd.CommandTimeout = 15
Cmd.CommandType = adCmdStoredProc

Cmd.Execute

RE: How do you call an Oracle stored procedure from a VB program.

SirPacs,
Its a long time for me since working in VB, but anyway i am not sure about the syntax in VB.
There are many ways to execute a stored procedure in VB which is written in Oracle. This can be done using DAO or ADO or RDO.
What Jim has told is the easiest way of executing a stored procedure.
There are commands available in RDO and ADO ie CALL command.
Just check for the syntax in MSDN help.

Just try this out.

Dim Conn as New ADODB.Connection
StrSql = "Begin Myproc ; End ;"

Conn.open dsn ' dsn is the datasource for the connection
Conn.Execute StrSql
Conn.Close
Set Conn = Nothing

Hope this helps u.

Regds

Vinod

RE: How do you call an Oracle stored procedure from a VB program.

Another good option is to use the oracle objects for ole
that you get with the full client installation of oracle on your machine.

you can get good help on using this if you look in the oracleo.hlp file of the %oracle_home%\mshelp directory

also you can find a number of sample programs in

%oracle_home%\oo4o22\vb\samples subdirectories.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close