Help: Calling stored procedure from Impromptu using OLE Automation

From: Alexander Kravchenko <akravche_at_dg1.bloomberg.com>
Date: 1997/10/23
Message-ID: <Pine.D-G.3.96.971023122920.11366B-100000_at_dg1>#1/1

Hi,
I wonder if somebody had a similar problem. I have a stored procedure that executes fine in sqlplus. But when I try to execute it on the client side in an Impromptu macro it gives me ole automation error. I wonder if somebody else experienced similar problems using Cognos tools.

I use Oracle7 Server Release 7.3.2.1.0 on dg unix, Impromptu 3.5 on nt4.0
Here is the source code for the macro:

'*************************************************************

sub main()

  dim impApp as object
  dim impSp as object
  dim cnt as long
  dim cnt1 as long
on Error goto myErr

  set impApp = createObject("Impromptu.Application.30")   set impSp = impApp.createStoredProcedure   impSp.setProcedure "sqltest(in, in out)"   cnt = 10
  cnt1 = 1
  impSp.execute cnt, cnt1
  cnt1 = impSp.getParameter(2)
  msgBox "Total Rows:" & cstr(cnt1),0
  set impSp=nothing
  set impApp = nothing
  exit sub

myErr:

   errString = error$
   msg = cstr(Err) & " " & Error$
   msgBox msg
   if err = 440 then

      resume next
   end if
   set impSp=nothing
   set impApp = nothing
   end sub

'******************************************************************

Here is the source code of stored procedure: PROCEDURE SQLTEST (var_a IN number, var_b IN OUT number)

   IS
fhandle utl_file.file_type;
BEGIN     fhandle := utl_file.fopen('/bb/data/','sqltest.log','w');     utl_file.put_line(fhandle, 'var_a = ' || var_a || 'var_b = ' || var_b);

    var_b := var_a + 1;
    utl_file.put_line(fhandle, 'var_a = ' || var_a || 'var_b = ' || var_b);

    utl_file.fclose(fhandle);
END;

---

• Alex Kravchenko *
• akravche_at_dg1.bloomberg.com *

  ---

Received on Thu Oct 23 1997 - 00:00:00 CEST