1 Reply Latest reply: Aug 17, 2012 10:45 AM by gdarling - oracle RSS

    Call Stored procedure with output parameter using VBA

    AnnPricks E
      Hi All,

      I have a stored procedure with output parameter as varchar2. Now i need to call it from Excel VBA.

      Oracle procedure is given below

      create or replace procedure ann_chk(p_in1 varchar2,
      p_in2 varchar2,
      p_out1 out varchar2,
      p_errot1 out varchar2)
      as
      l_cnt number;
      begin
      select count(1) into l_cnt from ann_login_chk
      where user_emailid = p_in1 and password = p_in2;
      if(l_cnt = 1) then
      p_out1 := 'Success'
      else
      p_out1 := 'Fail'
      end if;
      exception
      when others then
      p_errot1 := sqlerrm;
      end;

      Can anyone please tell me, how i can call this procedure using Excel VBA?
        • 1. Re: Call Stored procedure with output parameter using VBA
          gdarling - oracle
          Here's a vbs example I had laying around.
          You can find the constant definitions in C:\Program Files\Common Files\System\ado\adovbs.inc
          'create or replace procedure pass_it_out(v1 in varchar2, v2 out varchar2) as
          'begin
          'v2 := v1 || ' from stored proc';
          'end;
          '/
          strval = "hello world"
          set con = createobject("adodb.connection")
          con.open "provider=oraoledb.oracle;user id=scott;password=tiger;data source=orcl"
          'con.open "dsn=orcl;uid=scott;pwd=tiger"
          set cmd = CreateObject("adodb.command")
          cmd.CommandText = "pass_it_out"
          cmd.commandtype=&H0004
          cmd.ActiveConnection = con
          Set prminput = cmd.CreateParameter("", 200, 1, Len(strval), strval)
          Set prmoutput = cmd.CreateParameter("", 200,2, 500)
          cmd.Parameters.Append prminput
          cmd.Parameters.Append prmoutput
          cmd.execute
          wscript.echo prmoutput.value
          ' cleanup ommitted
          Hope it helps,
          Greg