Help: Calling stored procedure from Impromptu using OLE Automation

From: Alexander Kravchenko <akravchenko_at_bny18.bloomberg.com>
Date: 1997/10/23
Message-ID: <344F6D54.2F48B98A@bny18.bloomberg.com>#1/1

This is a multi-part message in MIME format.
--------------C249D9474E95A42E51075CCB

Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit

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
 *             akravchenko_at_bny18.bloomberg.com
 *               akravche_at_dg1.bloomberg.com
 */




--------------C249D9474E95A42E51075CCB

Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Alexander  Kravchenko
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Alexander  Kravchenko
n:              Kravchenko;Alexander 
email;internet: akravchenko_at_bny18.bloomberg.com
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard




--------------C249D9474E95A42E51075CCB--

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