How to run Oracle UDF/procedures

[JPCogAdmin]

Hi,

I wanted to know if there are people who have successfully implemented Oracle store procedures or functions and used them in Report Studio. Thanks for your help.

JP

 

[misscognos]

??
yes...

do you need help with something in particular?

 

[JPCogAdmin]

Hi misscognos,

Thank you for your reply. Actually at this point I'm using MS-Access and will have to transfer that to Oracle. So, my question at this moment is, do you have samples of UDF or procedures in Oracle that can be inserted in Report Studio?

J
;-)

 

[JPCogAdmin]

Ok, here is a question back on running stored procedures.
I've just created a SP inside a package. To print out the results I'm trying to do this in oracle:

variable x refcursor
exec proc_name_herex)

as soon as I do that it comes back and complaints about the following:

BEGIN proc_name);

*
ERROR at line 1:
ORA-06550: LINE 1, COLUMN 7:
PLS-00201: identifier 'proc_name' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statment ignored

The package and package body both compile without any errors or warnings. The simple sql I'm running goes like this:

open cursor_name for
select table_name.column from table_name;

Can someone tell me what I'm doing wrong?
Thank you for your help.

-J

 

[misscognos]

1. When you bring the UDF into FM and publish a package, do you see the function in the function list in FM and Report studio?

2. Make sure that you include something from a schema that has access to the UDF in report studio. You will find that even in a querying tool against oracle, you cannot call a UDF in a select statement without selecting somethign from a table.

3. I do not have too much experience writing UDFs myself. First of all, make sure that you can call it with Cognos not in hte picture through sql plus or toad or something.

 

[JPCogAdmin]

Misscognos,

Thanks. I figured it out by rebooting my machine. That's all it took.

-J