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!

@svenn Hoping you are still around this community using plsql. I created a post in this forum today. I have tried the dummy query two ways, and it is not working. It is giving error invalid column name and field not found. But the param value seems to be going in the function call. Hardcoding some values is working fine from Jasper, but as soon as I try to pass parameters, the query does not work. "Read Fields" works fine and does not return any errors. Here is what I have tried. 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. As you can see I am willing to go to 2008 to find an answer for this 🙂 select mysch.CF_CostPerTreatmentFormula ( 'CC-100', 'CORAL ARDISIA' , 'FOLIAR HAND GUN' ,'GUN', '01-jan-2023', '31-jan-2023') from dual Any help is greatly appreciated!

@Habib_E I just asked a question today in this forum about calling a plsql function with multiple parameters from Jasper reports. Do you have any tips for that? For proof of concept, I am using select mysch.my_function ('string 1', 'string2') from DUAL. This works. But when I try to do select mysch.my_function ($P{mypar1}, 'string2') from dual instead, I am getting jasper errors column not found and invalid design. Parameter mypar1 is just a String parameter at prompt. It doesn't like the parameter passing in the function.

As I wait for some ideas, I am reading any threads on this topic, and I saw one that said to save the report and try running it again, and now, I am getting this error:

Hi, I'm trying to call a pl/sql function with parameters from Jasper Reports. The query is working fine in Toad and in Jasper Reports when I hard code the parameters. The query bring the correct response $400.25 in both cases. I am trying to call the function the easiest way, just to do a proof of concept that I can use later generalizing later by passing all the parameters. This function has 6 parameters. I am using the pl/sql setting of Jasper for the query. However, when I change a parameter from the hard coded value to $P{p_asset_id}, I get the following error. Query that works from Jasper (with hard coded parameters): SELECT mysch.CF_CostPerTreatmentFormula ( 'CC-100', 'CORAL ARDISIA' , 'FOLIAR HAND GUN' , 'GUN', '01-jan-2023', '31-jan-2023' ) FROM DUAL Query that produces there error in Jasper, by just replacing one of the parameters in the query: SELECT mysch.CF_CostPerTreatmentFormula ( $P{p_asset_id}, 'CORAL ARDISIA' , 'FOLIAR HAND GUN' , 'GUN', '01-jan-2023', '31-jan-2023' ) FROM DUAL When I tried to drag the parameter from the list of available parameters in the Parameter Tab, it gives me a circle with the stop sign, and it is not able to do it. When I run the report, with the typed parameter, I get the following error: net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Unknown column name mysch.CF_COSTPERTREATMENTFORMULA('CC-100','CORALARDISIA','FOLIARHANDGUN','GUN','01-JAN-2023','31-JAN-2023') in result set. Notice that the value entered in the parameter prompt CC-100 actually made it to the calling function. Any suggestions? I am open to call the function other ways. This was the first that at least I got working hard coding the parameters. But I need to be able to pass the parameters to the plsql function. Thanks!