Calling Oracle Stored Proc from RS, 8.2

[CognosPaul]

I am in the process of converting a report from 8.4/SQL Server 2005 to 8.2/Oracle. The database is identical in both environments, aside from the normal differences of Oracle/SQL Server. Due to reasons I will not go into I am not able to modify the FM.

In this report I reference a stored procedure in a SQL Item.

Code Select Expand


{EXEC [dbo].[SP] N#sq(CamPassport())#,
#sq(prompt('a','integer','1'))#,
#sq(prompt('b','integer','1'))#,
#sq(prompt('c','integer','1'))#,
#sq(prompt('d','integer','1'))#,
#sq(prompt('e','integer','1'))#
}

This works perfectly in 8.4/SQL Server. Alas I am then requested to make a duplicate on an unfamiliar database in an obsolete version of Cognos. No amount of cajoling, begging, or threatening will convince Cognos to validate the expression.

Code Select Expand


{BEGIN SP (#sq(CamPassport())#,
#sq(prompt('a','integer','1'))#,
#sq(prompt('b','integer','1'))#,
#sq(prompt('c','integer','1'))#,
#sq(prompt('d','integer','1'))#,
#sq(prompt('e','integer','1'))#);
END;
}

Someone suggested that it might be the cursor reference that is confusing Cognos. If it's a problem with the out parameter being the cursor reference, I am able to modify the SP. It is a very simple insert statement with 0 chance of error.

The SP ends with

Code Select Expand

  -- return the status in a result set
  OPEN o_cur1 FOR
    SELECT something AS something FROM DUAL;

EXCEPTION
  WHEN OTHERS THEN
    DEBUG.raise_app_error('FAILED', ticket);
END;
/


I'm thinking I could replace all of that with select 1 as something from dual; and be done with it.

The only other possible workaround I can think of is to create a new FM, import the SP into that, build a report around that package and reference it in an iFrame, passing the parameters to it as needed. That is another less-than-optimal solution that I'd like to avoid.

Any ideas?

[imts]

Can u send me the SP that u r calling from SQL Item from Cognos 8.4 firing on SQL server.

As i have never called SP from Cognos RS . I will still try to come with a way to call SP by sending i/p parameters from cognos and getting back an out parameter from Oracle.  I use 8.4 and Oracle .

Not sure but I will let u know if i m successful in doing that :)

- TS