MariaF

Thank you so much! This answer was so helpful to me!!

@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!!!

@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!

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!

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!

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.

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.

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:

Solution: 1) Each time we change the parameters inside the query, we have to refresh manually the Return $F value of the Expression that calls the plsql function in the property panel. 2) Make sure the parameters are in the same order than in the calling plsql function. 3) Here is the query syntax that returned the correct value: select mysch.CF_CostPerTreatmentFormula ( $P{p_asset_id}, $P{p_plant_treated}, $P{p_treatment}, $P{p_treatment_method_abrev}, $P{p_date_from}, $P{p_date_to} ) from dual

Friends, Solution: 1) Each time we change the parameters inside the query, we have to refresh manually the Return $F value of the Expression that calls the plsql function in the property panel. 2) Make sure the parameters are in the same order than in the calling plsql function. 3) Here is the query syntax that returned the correct value: select mysch.CF_CostPerTreatmentFormula ( $P{p_asset_id}, $P{p_plant_treated}, $P{p_treatment}, $P{p_treatment_method_abrev}, $P{p_date_from}, $P{p_date_to} ) from dual

I just noticed that every time I changed the parameters in the SQL query in Jasper, I had to refresh the Parameter Expression in the properties. By doing so, now, the errors have disappeared. The query is just returning $0.00 instead of $400.25 (correct response). Which at least seem to be trying to do something... Here is the XML source of the Jasper: <?xml version="1.0" encoding="UTF-8"?> <!-- Created with Jaspersoft Studio version 8.2.0.final using JasperReports Library version 6.20.3-415f9428cffdb6805c6f85bbb29ebaf18813a2ab --> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="CPT" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="46f27a19-fb15-4a26-b91e-feb2118a5684"> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="myschGENT_DataAdapter"/> <parameter name="p_asset_id" class="java.lang.String" evaluationTime="Early"/> <parameter name="p_treatment" class="java.lang.String"/> <parameter name="p_plant_treated" class="java.lang.String"/> <parameter name="p_treatment_method_abrev" class="java.lang.String"/> <parameter name="p_date_from" class="java.lang.String"/> <parameter name="p_date_to" class="java.lang.String"/> <queryString language="plsql"> <![CDATA[select mysch.CF_CostPerTreatmentFormula ( $P{p_asset_id}, $P{p_treatment}, $P{p_plant_treated}, $P{p_treatment_method_abrev}, $P{p_date_from}, $P{p_date_to} ) from dual ]]> </queryString> <field name="mysch.CF_COSTPERTREATMENTFORMULA(:1,:2,:3,:4,:5,:6)" class="java.math.BigDecimal"> <property name="com.jaspersoft.studio.field.name" value="mysch.CF_COSTPERTREATMENTFORMULA(:1,:2,:3,:4,:5,:6)"/> <property name="com.jaspersoft.studio.field.label" value="mysch.CF_COSTPERTREATMENTFORMULA(:1,:2,:3,:4,:5,:6)"/> </field> <background> <band splitType="Stretch"/> </background> <detail> <band height="125" splitType="Stretch"> <textField pattern="¤#,##0.00;¤-#,##0.00"> <reportElement x="81" y="2" width="100" height="30" uuid="20535428-833b-4a3a-aa01-c324e4c78f8c"/> <textElement> <font size="12"/> </textElement> <textFieldExpression><![CDATA[$F{mysch.CF_COSTPERTREATMENTFORMULA(:1,:2,:3,:4,:5,:6)}]]></textFieldExpression> </textField> <staticText> <reportElement x="10" y="0" width="61" height="30" uuid="74e72295-6c49-4c2f-8e68-cee0ba1cff00"/> <text><![CDATA[Hello]]></text> </staticText> </band> </detail> </jasperReport>

@shreekrishna.shankhwar Here is my original post. Here are the other screenshots I shared in the other post. When I try to drag a paramater from the list below into the query, it doesn't let me. Also, when I type $P{p_asset_id} it turns red in the query. But when I type it with the "!" it stays blue. The "Read Fields" button does not report an error either way. But when I compile the report I get the error above. Disclaimer: This is for a pesticide treatment application 🙂

When I try to drag a paramater from the list below into the query, it doesn't let me. Also, when I type $P{p_asset_id} it turns red in the query. But when I type it with the "!" it stays blue. The Read Fields button does not report an error either way. But when I compile the report I get the error above. Disclaimer: This is for a pesticide treatment application 🙂

@shreekrishna.shankhwar Hi, I have tried passing the parameter as $P{p_asset_id} and $P!{p_asset_id}

@tchen @jgust Hi, I am trying to do something similar as above, and I have been reading all the posts related to this topic. I am trying to call a plsql with multiple parameters from Jasper. When I use hard coded parameters, the dummy select with the function works fine. But as soon as I try to change the hard coded strings for parameters values, I get sql error of column or field not found. The parameters have been defined in the Parameters section as Strings. Code not working from Jasper with plsql setting: select mysch.CF_CostPerTreatmentFormula ( $P{p_asset_id}, 'CORAL ARDISIA' , 'FOLIAR HAND GUN' ,'GUN', '01-jan-2023', '31-jan-2023') from dual select mysch.CF_CostPerTreatmentFormula ( $P!{p_asset_id}, 'CORAL ARDISIA' , 'FOLIAR HAND GUN' ,'GUN', '01-jan-2023', '31-jan-2023') from dual This query works fine from Jasper with Hardcoded values from Jasper and plsql settting: select mysch.CF_CostPerTreatmentFormula ( 'CC-100', 'CORAL ARDISIA' , 'FOLIAR HAND GUN' ,'GUN', '01-jan-2023', '31-jan-2023') from dual I need to pass the six values as parameters. Any help is greatly appreciated!