Executing Oracle stored procedures through VB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Executing Oracle stored procedures through VB

  1. #1
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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?

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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;")

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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?


    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Aug 2001
    Posts
    20
    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

  5. #5
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70
    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!?



  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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


  7. #7
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547

    Thanks

    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  9. #9
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547

    Eureka!

    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!!

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width