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

[SirPacs]

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.

 

[yaffle]

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.

 

[MikeLacey]

That's a good idea - you can build triggers so that they only fire once (for statements that affect more than one row)<br>
<br>
Mike<br>
<p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[JimHorton]

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

 

[ist]

You can make a call to an Oracle stored procedure in VB using ADO Command object. Please see the following sample code.<br>
<br>
Dim Cmd As New ADODB.Command<br>
<br>
Cmd.ActiveConnection = &quot;DSN=oracle;UID=user&quot;<br>
Cmd.CommandText = &quot;myADOProc&quot;<br>
Cmd.CommandTimeout = 15<br>
Cmd.CommandType = adCmdStoredProc<br>
<br>
Cmd.Execute<br>
<br>

 

[Vinod]

SirPacs, <br>
Its a long time for me since working in VB, but anyway i am not sure about the syntax in VB.<br>
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.<br>
What Jim has told is the easiest way of executing a stored procedure.<br>
There are commands available in RDO and ADO ie CALL command.<br>
Just check for the syntax in MSDN help.<br>
<br>
Just try this out.<br>
<br>
Dim Conn as New ADODB.Connection<br>
StrSql = &quot;Begin Myproc ; End ;&quot;<br>
<br>
Conn.open dsn ' dsn is the datasource for the connection<br>
Conn.Execute StrSql<br>
Conn.Close<br>
Set Conn = Nothing<br>
<br>
Hope this helps u.<br>
<br>
Regds<br>
<br>
Vinod<br>

 

[johnsod]

Another good option is to use the oracle objects for ole<br>
that you get with the full client installation of oracle on your machine.<br>
<br>
you can get good help on using this if you look in the oracleo.hlp file of the %oracle_home%\mshelp directory<br>
<br>
also you can find a number of sample programs in<br>
<br>
%oracle_home%\oo4o22\vb\samples subdirectories.<br>
<br>