Calling pl/sql package from Jasper Reports

[MariaF]

Hi,

Can we call an Oracle Package Function from Jasper reports? I know we can call Oracle procedures and functions from Jasper. But can we call a function inside an Oracle Package?

Are Oracle Packages supported in Jasper reports?

I am connected to the DB from Jasper. I can run the function in the package from the Oracle SQL prompt. But when I placed the same function inside the package in Jasper Reports, I am getting the error ORA-00904.

From Oracle, the calling of the function inside the package works fine:

[alomine]

This should work just fine. What ojdbc driver are you using?

[MariaF]

It is the same ojdbc driver that I am using to call the function that works fine. When I call the function directly in the DB from Jasper that works. Schema.FunctionName (par1, par2, par3...) . But I have tried both Schema.PackageName.FunctionName (list of pars) and PackageName.FunctionName (list of pars), and neither one is working. I haven't found any info online that shows this is working or a sample code. 

[alomine]

Do You have function in both package spec and body? Are You sure u are calling it from right schema in both db and jasper?

Given package spec:

CREATE OR REPLACE 
PACKAGE P_TEST AS 

 FUNCTION f_test RETURN NUMBER;

END P_TEST;

Then calling:

select p_test.f_test from dual

Works just fine

 

Edited February 7 by alomine

[MariaF]

Yes, I do have the function in both the package spec and the package body. Also. notice how in one of the pics I sent before, I am calling the package function from SQL, and it returns the value of zero. Calling the stand alone function to the same schema works fine in Jasper. But when I try to change the select stmt in Jasper to call the package function, and click "Read Fields" returns the error that it cannot find the package as Invalid Identifier.

[alomine]

Can You check if you have quotation marks in package or function names? Can you provide package with just one of your functions? Or try using my package that i posted before, just add body that returns anything. As simple as you can

[MariaF]

No, I don't have any quotations in the package or the function names. Sure, I can try a simple version of the package and one function, and see how that goes. Have you done this before, or this is a "it should work" venture? I will try this today and will let you know. Thanks!

[alomine]

I am using package functions in jasper reports every day and thats the only way im doin it so its 100% works, even tested in mine example above

[MariaF]

I will verify this and keep trying then. This is my first jasper report conversion from Oracle Reports. I'm taking care of some formatting right now (a lot of fun between Studio and the Server, fields do not display the same way) since I am trying to meet a deadline. But I will get back to you about this in a week or sooner (asap). Since Jasper does not seem to recognize the names of the package and functions, I am starting to think the names are too long (out of bounds for Jasper maybe). I will try the simple package, short name asap. Thank you for your help so far!

[MariaF]

@alomine Hi, I just tried to create your simple package, and call it from Jasper in the Query section of the report, and I am getting the same error I was getting with the package I was trying to use before. Below is the package I created (following your sample), and the error I am getting from Jasper.

CREATE OR REPLACE 
PACKAGE P_TEST AS 

 FUNCTION f_test RETURN NUMBER;

END P_TEST;

CREATE OR REPLACE 
PACKAGE BODY P_TEST AS 

 FUNCTION f_test RETURN NUMBER IS
 myval                number := 0;
 BEGIN
 myval := 5;
 return myval;
 END f_test;

END P_TEST;

Select P_TEST.f_test as myval2 from dual;   (this works in Toad, and it returns the value of 5)

Here is a pic (with the same issue that I was having before) when I try to call the package from Jasper.

Any ideas? Thanks!

[alomine]

What is ur ojdbc driver?  Also what is oracle db version u are working on ? Also try using uppercase when declaring function and when calling it in jasper. Its working for me. My guess its somehow related to upper/lowercase sensitivity

[MariaF]

@Alomine Thank you so much for all your help. I just figured out that the individual Oracle  myacc account the DBA set up cannot find the package, not even from Toad. No matter if I grant execute on P_TEST to myacc;  However, the generic db where the package resides find the package from Jasper. I was able to find the package "Read Fields" when I changed the login account in the Data Adapter from my individual account to the generic db account where the package resides. For the Functions, I can grant execute on function to myacc, but for the package, it is giving me issues. All good, I can see the package from Jasper now. Thank you!!!