Executing Oracle stored procedures through VB

[Raminder]

My VB application is connecting to the Oracle database fine and I can run many SQL statements through ADO connection. However, when I try to execute a stored PL/SQL procedure, it does'nt work.
Does anybody have an ideahow to execute Oracle stored procedures from VB?
Or through Shell commands?

[ales]

Since you didn't post any piece of code I can just say that people often forgot to use BEGIN and END.

... Execute("BEGIN your_procedure(p1, p2, ... ); END;")

[Raminder]

Thanks ales

I was doing
CON.Execute("procedure_name")

whereas it should have been
CON.Execute ("BEGIN procedure_name;END;")

where CON is the active connection to Oracle.

Is there any way I can display output generated by DBMS_OUTPUT.PUT_LINE on a VB form?

[kshailesh]

Hi

I Assume your stored procedure has been created sucessfully.

Below is a simple way to call stored procedure from VB

Dim comm As ADODB.Command
dim conn as ADODB.Connectin

set comm = new adodb.command
set conn = new adodb.connection

conn.open "username","password","host_string"
comm.activeconnection = conn

comm.CommandText = " {call Procedurename (?,?,?,?) }"

the ???? are the number of parameters to the procedure .

comm.Parameters(0) = value1
comm.Parameters(1) = value2
comm.Parameters(2) = value3
comm.Parameters(3) = value4
comm.Execute

set comm = nothing
conn.close
set conn = nothing

another way to do it is


Dim comm As ADODB.Command
Dim conn as ADODB.Connectin
Dim param as ADODB.Parameter


set comm = new adodb.command
set conn = new adodb.connection

conn.open "username","password","host_string"
comm.activeconnection = conn

comm.CommandType = adCmdText

comm.CommandText = "{CALL procedurename (?)}"

Set param = comm.CreateParameter("name", adVarChar, adParamInput, Len(variable), actual value)

comm.Parameters.Append param

comm.Execute

set comm = nothing
conn.close
set conn = nothing

The second way is the pro way to do it . It gives more control on the code .

Hope this helps
Happy Programming

Regards
Shailesh

[mageri]

Hi,

You can use the UTL_FILE package insted of the DBMS_OUTPUT package. UTL_FILE lets your PL/SQL programs read and write operating system (OS) text files.

Include the init.ora file with the parameter UTL_FILE_DIR='path to a directory where you want the output file'.

Read more in the doc. how to use the UTL_FILE package.

Maybe some hint!?

[ales]

You can't see output of dbms_output package in VB, AFAIK.
For "reports" from inside of stored procedures I usually use OUT parameter. Procedure stores required output information in a VARCHAR2 variable and passes it back via the parameter.
HTH,
Ales

[Raminder]

Thanks Ales and all you helpful guys out there.

Ales, can you give an example of getting the OUT parameter on VB?
I tried to get the OUT parameter onto a text box, but failed.

.....
......
Dim X, Y as Integers
X=text1.text
con.execute("begin myprocedure("+X+","+Y");end;")
text2.text=Y
end sub

I get a type mismatch error in VB.

[ales]

Hi, I'm sorry for delay.
I use the OUT parameters in Java and VB/OO4O so I'm not able to give you an advice for ADO.
kshailesh's post above seems to show good way. You'll have to see help for syntax to get the value of OUT parameter.
Regards,
Ales

[Raminder]

I could get it right. This is how:

Dim VALUE1 As Double
VALUE1 = Text1.Text
comm.activeconnection = con
comm.CommandText = " {CALL TEST1 (?,?) }"
comm.Parameters(0) = VALUE1
comm.Execute
Text2.Text = comm.Parameters(1)

WHERE COMM IS ADODB.COMMAND
& CON IS ACTIVE CONNECTION
TEST1 IS MY ORACLE PROCEDURE


Thanks a lot, all of you!!