Home » SQL & PL/SQL » SQL & PL/SQL » oracle procedure out parameter and visual basic
oracle procedure out parameter and visual basic [message #206251] Wed, 29 November 2006 05:45 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
yesterday I have posted a doubt where I used a procedure with
in parameters to insert in a table, and called it from
visual basic

today, to learn more, i tried to use out parameters..but i am
getting error


the procedure is

 1  create or replace procedure apmc_sel(p_id in apmc.dno%type,
 2           p_name out apmc.dname%type)
 3    as
 4  begin
 5   select dname into p_name from apmc where dno=p_id;
 6*  end;





and the code in visual basic, this is public procedure
which i am calling at a button click


Public Sub MYPROC()

 Dim STR As String
'

   
      With p1
      .Direction = adParamInput
      .Type = adNumeric
      .Value = Form1.Text1.Text
      End With
      
      With p2
      .Direction = adParamOutput
      .Type = adBSTR
      
      End With
      

      With Q1
      .CommandText = "SCOTT.APMC_PROC"
      .CommandType = adCmdStoredProc
      .ActiveConnection = con
      .Parameters.Append p1
'      .Parameters.Append p2
      End With
      
     Q1.Execute (error here)
      
   
   MsgBox "procedure executed", vbInformation   
End Sub



i am getting an error saying wrong number of arguments in
call to procedure....

which means i have to do something with the out parameter

the procedure is simple...just passing in an id, and getting
the name...i think after that its upto us what to do
with that value...whether we want to display it, or store
it somewhere else....

my question is, how to get that out parameter value?

Re: oracle procedure out parameter and visual basic [message #206262 is a reply to message #206251] Wed, 29 November 2006 06:09 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
PL/SQL procedure: create or replace procedure apmc_sel
VB code: .CommandText = "SCOTT.APMC_PROC"

Are you sure you are calling the right procedure?

Moreover, if I remember well, this Oracle error says something about wrong number or type of arguments. Did you check the type as well?
Re: oracle procedure out parameter and visual basic [message #206264 is a reply to message #206251] Wed, 29 November 2006 06:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your procedurename does not match the procedure called.
Re: oracle procedure out parameter and visual basic [message #207166 is a reply to message #206251] Mon, 04 December 2006 06:45 Go to previous message
CoolBuddy
Messages: 10
Registered: December 2006
Location: India
Junior Member
Most probably u haven't created a parameter.
I don't have vb installed right here to check it out n tell u, but u can check for the method 'CreateParameter' or something like that. Create the parameter first and then pass the value for it.
Previous Topic: Unix + Oracle connection
Next Topic: droping the queue tables
Goto Forum:
  


Current Time: Sun Dec 04 08:56:36 CST 2016

Total time taken to generate the page: 0.09748 seconds