Home » Developer & Programmer » Reports & Discoverer » Custom Function returns REP-1401: 'cf_1' : Fatal PL/SQL error occurred
Custom Function returns REP-1401: 'cf_1' : Fatal PL/SQL error occurred [message #268785] Wed, 19 September 2007 12:53 Go to next message
ronin10
Messages: 17
Registered: July 2007
Junior Member
Hi

Oracle Reports
Oracle 10g

I am in the process of returning/reading data from a text file. I am able to return the correct data to display in my report using

function CF_1Formula return char is
begin
return (:ProductID); --product id is my field
end;


However, I am having an issue returning anything using my custom function. For example:

function CF_1Formula return char is
begin
return CUSTOM_FUNCTION.Custom(:ProductID,4); --4 is a parameter
end;


This gives me the numeric error.


function CF_1Formula return char is
begin
return CUSTOM_FUNCTION.Custom(':ProductID',4); --4 is a parameter
end;


This seems to work. so i know it can return static data. I am just having trouble reading data from an external source using my function.

Has anyone had a similar problem?

the :ProductID parameter is taking in a VARCHAR2.

Thanks in advance.


Re: Custom Function returns REP-1401: 'cf_1' : Fatal PL/SQL error occurred [message #268787 is a reply to message #268785] Wed, 19 September 2007 13:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I think
Quote:
CUSTOM_FUNCTION.Custom(:ProductID,4)
is giving error. If you know the value of the ProductID, you can cross check this from back end (if customer_function is a stored package) or check te code for that function.

By
Vamsi
Re: Custom Function returns REP-1401: 'cf_1' : Fatal PL/SQL error occurred [message #268798 is a reply to message #268785] Wed, 19 September 2007 14:20 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Custom function you wrote does some computing with the first parameter value brought by :productID.

As function works properly when parameter_1 = ':productID' and fails when parameter_1 = :productID, I suspect that
length(:productID) > length(':productID')
and, even more important,
length(:productID) > max size of a variable used in the 'custom function'

For example:
SQL> CREATE OR REPLACE FUNCTION fun_test (par_1 IN CHAR)
  2    RETURN NUMBER
  3  IS
  4    l_length NUMBER(1);         --> note local variable's size!
  5  BEGIN
  6    l_length := LENGTH(par_1);
  7    RETURN (l_length);
  8  END;
  9  /

Function created.

SQL>
SQL> SELECT fun_test('len < 10') result FROM dual;

    RESULT
----------
         8

SQL>
SQL> SELECT fun_test('length is > 10') result FROM dual;
SELECT fun_test('length is > 10') result FROM dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "SCOTT.FUN_TEST", line 6


SQL>
In other words, make sure that length of a value passed by the first parameter isn't too large for the custom function.
Re: Custom Function returns REP-1401: 'cf_1' : Fatal PL/SQL error occurred [message #268809 is a reply to message #268798] Wed, 19 September 2007 15:57 Go to previous message
ronin10
Messages: 17
Registered: July 2007
Junior Member
Thanks for your help, guys.
Previous Topic: Counter
Next Topic: Restrict number of pages
Goto Forum:
  


Current Time: Wed Dec 07 12:50:13 CST 2016

Total time taken to generate the page: 0.09738 seconds