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.
{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.
{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
-- 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?
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