10 Replies Latest reply: Oct 27, 2008 8:04 PM by 524753 RSS

    ORA-06502:PL/SQL:numeric or value error: character to number conversion err

    V
      Hi,

      I am developing a BI Publisher Report in which I need to give parameter value as either null(select all) or one or (one or more values)...

      so I have a query like

      ((coalesce(null, :p_product_id ) is null) or (mp.manager_product_id in (:p_product_id )))

      when I run the report on BI Publisher Server it's giving up an error as

      ORA-06502:PL/SQL:numeric or value error: character to number conversion error

      But..when I use the same condition in another report its working fine..what might be the problem..?
      Thanks..
        • 1. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
          V
          I'm using data template as my data set..

          Is it possible to pass a list of values(more than 1) in the parameter using data template? because in the other report I mentioned above was done using sql query as data set.


          Thanks..
          • 2. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
            ScottC
            Is it possible to pass a list of values(more than 1) in the parameter using data template?
            No, it is not, I would suggest you use the old fashioned decode or try to deconstruct your sql to avoid using this type of syntax.

            Scott
            • 3. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
              V
              Thanks for the reply scott...

              I'll give a try using decode in my SQL..
              • 4. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
                V
                scott,

                I tried using decode it seems to be not working....this time what I did is I changed the parameter datatype as "char" in the parameter declaration part..(as i'm using LOV for this parameter)...I got an error saying .

                ORA-06502: PL/SQL: numeric or value error: character string buffer too small

                I tried playing with varchar2,char,character increasing and decreasing there sizes..but ended up with the same error..(string buffer too small)

                sample of my code is below..

                <dataTemplate name="dataTemplateName" description="Template description" defaultPackage="fund_fact_pkg">
                     <properties>
                          <property name="include_parameters" value="true"/>
                     </properties>
                     <parameters>
                          <parameter name="p_fund_class_id" dataType="char"/>
                     </parameters>
                     <dataQuery>
                          <sqlStatement name="Q1">
                               <![CDATA[select tfcd.FUND_CLASS_ID fund_id
                ,tfcd.FUND_CLASS_CODE
                ,tfcd.LINKED_FUND_CLASS_CODE
                from TMP_FUND_CLASS_DIM tfcd
                where 1=1
                and ((coalesce(null,:p_fund_class_id ) is null) or (tfcd.fund_class_id in (:p_fund_class_id )))
                ]]>
                          </sqlStatement>
                     </dataQuery>
                     <dataStructure>
                          <group name="Q1" source="Q1">
                               <element name="FUND_CLASS_ID" value="fund_id"/>
                               <element name="FUND_CLASS_CODE" value="FUND_CLASS_CODE"/>
                               <element name="LINKED_FUND_CLASS_CODE" value="LINKED_FUND_CLASS_CODE"/>
                          </group>
                     </dataStructure>
                </dataTemplate>

                Thanks..
                • 5. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
                  andymc
                  How are you writing you SQL statement? From the sqlStatement above it looks like you're still using COALESCE. Have you tried:
                  WHERE  tfcd.fund_class_id IN DECODE(:p_fund_class_id,NULL,tfcd.fund_class_id,:p_fund_class_id)
                  Cheers

                  Andy
                  • 6. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
                    V
                    Thanks Andy for the reply...

                    I tried using the decode statement and I tried using yours also...still showing the error ORA-06502:PL/SQL:numeric or value error: character to number conversion error

                    Im using LOV for the parameter...so I tried changing the datatype of parameter as char then showing an error as..
                    ORA-06502: PL/SQL: numeric or value error: character string buffer too small


                    Thanks...
                    • 7. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
                      andymc
                      It's strange, when I try the DECODE in a SQL Query within BI Publisher I get the correct results, yet when I put the same query into a Data Template I get an ORA-01722: invalid number error. I'll keep looking.

                      As an aside, have you tried removing the default package? I think that may be covering the real issue.

                      Cheers

                      Andy
                      • 8. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
                        V
                        Andy,

                        Issue got solved..

                        after doing google..I found the following

                        A use of multiple parameter values is not supported by Data Template. It can support only one value per each parameter. However, there is a workaround to get through this by using lexical parameter and plsql procedure. Here is an example.

                        1. Create a Package Header

                        CREATE OR REPLACE PACKAGE MULTIPLE_PARAMS AS
                        whereclause varchar2(1000) := ' ';
                        p_DEPTNO varchar2(400);
                        function return_where return boolean;
                        END MULTIPLE_PARAMS;

                        2. Create a Package Body

                        CREATE OR REPLACE PACKAGE BODY MULTIPLE_PARAMS AS

                        function return_where return boolean AS

                        begin
                        if p_DEPTNO is not null then
                        whereclause := 'where DEPTNO in ('||p_DEPTNO||')';
                        end if;
                        return true;
                        end;

                        END MULTIPLE_PARAMS;

                        3. Make sure you have registered a parameter.

                        <parameter name="p_DEPTNOT" datatype="char"/>

                        4. Add lexical parameter

                        <sqlStatement name="QueryDept">
                        <![CDATA[SELECT DEPTNO,DNAME,LOC from dept &whereclause order by DNAME]]>
                        </sqlStatement>

                        5. Call the function above from Data Template

                        <dataTrigger name="beforeReport" source="multiple_params.return_where"/>

                        Cheers..
                        • 9. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion err
                          andymc
                          Great bit of googling there! What did we do before it eh?

                          This really looks like something Oracle should try and improve on the next version of BI. What do you think? Larry, if you're ready this give the people what they want :)
                          • 10. Re: ORA-06502:PL/SQL:numeric or value error: character to number conversion
                            524753
                            "A use of multiple parameter values is not supported by Data Template. It can support only one value per each parameter."

                            Is it documented ? anywhere >