Home » Developer & Programmer » Reports & Discoverer » Rep-1401: '' Fatal PL/sql error, issue with Clob and varchar2 in Oracle 11.2.0.4.0 (Windows7 and Solaris 10)
Rep-1401: '' Fatal PL/sql error, issue with Clob and varchar2 in Oracle 11.2.0.4.0 [message #624130] Thu, 18 September 2014 02:39 Go to next message
abhisheknarolia
Messages: 3
Registered: September 2014
Location: Pune
Junior Member
Hello Experts,
I have following function created in RDF file of oracle reports 10g, and I have executed report having this function with oracle 11.2.0.3.0 and it works fine.
But when I try to execute report after oracle version upgrade from 11.2.0.3.0 to 11.2.0.4.0 with same data, report fails with runtime error Rep-1401: '' Fatal PL/sql error.

Kindly note when I run this funtion from sql developer on Oracle 11.2.0.4.0, that works fine.

With Oracle 11.2.0.4.0 version, if I typecast Text column from clob to varchar and execute report then it works... but report runs on old version (i.e. 11.2.0.3.0) without any explicit typecasting.

Is there any known issue with the Oracle 11.2.0.4.0 ? Or oracle removed implicit conversion ? But same function is working fine without type conversion in SQL Developer. Why this is only Oracle report specific ?

I will appreciate your expert comments, thanks in advance..

Note:- Text column from table msgnotes is of datatype CLOB.

CREATE OR REPLACE FUNCTION clobblobtest (TMP_MID VARCHAR2)
   RETURN VARCHAR
IS
   missed_text    VARCHAR2 (1000) := '';
   v_time_stamp   DATE;
   v_end_time     DATE;

   CURSOR c1 (c_time_stamp IN DATE, c_mid IN VARCHAR2)
   IS
      SELECT mn.TEXT
        FROM msgnotes mn
       WHERE mn.mid = c_mid AND mn.create_date >= c_time_stamp;

   CURSOR c2 (
      c_time_stamp   IN DATE,
      c_mid          IN VARCHAR2,
      c_end_time     IN DATE)
   IS
      SELECT mn.TEXT
        FROM msgnotes mn
       WHERE     mn.mid = c_mid
             AND mn.create_date >= c_time_stamp
             AND mn.create_date <= c_end_time;
BEGIN
   IF ('1' = 'INWARD RETURN')
   THEN
      SELECT bbi
        INTO missed_text
        FROM mtf1000
       WHERE mid = TMP_mid;
   ELSIF '1' = 'QPH Missed'
   THEN
      SELECT MAX (nj.update_date)
        INTO v_time_stamp
        FROM newjournal nj
       WHERE nj.mid = TMP_mid AND nj.status = 'CTOVRQ';

      FOR i IN c1 (v_time_stamp, TMP_mid)
      LOOP
         missed_text := missed_text || ' ' || i.text;
      END LOOP;
   ELSIF ('Rejected' = 'Rejected' OR 'REJECTED' = 'REJECTED')
   THEN
      IF ('TERMINATED' IN ('TERMINATED'))
      THEN
         SELECT MIN (nj.update_date), MAX (nj.ENDDATE)
           INTO v_time_stamp, v_end_time
           FROM newjournal nj
          WHERE     nj.mid = TMP_mid
                AND nj.actionid2 = '16'
                AND nj.actionid1 IS NULL;

         FOR i IN c2 (v_time_stamp, TMP_mid, v_end_time)
         LOOP
            missed_text := missed_text || ' ' || i.text;
         END LOOP;
      ELSE
         SELECT MAX (nj.update_date)
           INTO v_time_stamp
           FROM newjournal nj
          WHERE nj.mid = TMP_mid AND nj.status = 'UNSETLED';

         FOR i IN c1 (v_time_stamp, TMP_mid)
         LOOP
            missed_text := missed_text || ' ' || i.text;
         END LOOP;
      END IF;
   ELSE
      missed_text := NULL;
   END IF;

   RETURN (missed_text);
END;


Regards,
Abhishek

[LF reformatted code & applied [code] tags]

[Updated on: Thu, 18 September 2014 23:58] by Moderator

Report message to a moderator

Re: Rep-1401: '' Fatal PL/sql error, issue with Clob and varchar2 in Oracle 11.2.0.4.0 [message #624132 is a reply to message #624130] Thu, 18 September 2014 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try to recompile ALL (Ctrl + Shift + K)?

Would it help if you create a function in the database and call it from a report?
Re: Rep-1401: '' Fatal PL/sql error, issue with Clob and varchar2 in Oracle 11.2.0.4.0 [message #624140 is a reply to message #624132] Thu, 18 September 2014 05:48 Go to previous messageGo to next message
abhisheknarolia
Messages: 3
Registered: September 2014
Location: Pune
Junior Member
Yes, recompilation all is done already.
Creating and using function in database will not help in my case.
Re: Rep-1401: '' Fatal PL/sql error, issue with Clob and varchar2 in Oracle 11.2.0.4.0 [message #624206 is a reply to message #624140] Fri, 19 September 2014 00:04 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why will it not help? I don't quite understand what is so specific in your case that a function wouldn't do its job if it is not part of the report, but a stored function.

Just being curious: is this a real function you use, or did you adjust it for testing purposes? I hope it is the latter, because none of its code will ever be executed, except for
SELECT MIN (nj.update_date), MAX (nj.ENDDATE)
           INTO v_time_stamp, v_end_time
           FROM newjournal nj
          WHERE     nj.mid = TMP_mid
                AND nj.actionid2 = '16'
                AND nj.actionid1 IS NULL;

         FOR i IN c2 (v_time_stamp, TMP_mid, v_end_time)
         LOOP
            missed_text := missed_text || ' ' || i.text;
         END LOOP;
which means that you can remove the rest as it won't do any harm.
Re: Rep-1401: '' Fatal PL/sql error, issue with Clob and varchar2 in Oracle 11.2.0.4.0 [message #624215 is a reply to message #624206] Fri, 19 September 2014 02:33 Go to previous messageGo to next message
abhisheknarolia
Messages: 3
Registered: September 2014
Location: Pune
Junior Member
The report have 10+ columns from which at runtime one column gets populated using another columns of the same report.
Due to this scenario i mean to use function to populate one of report columns using another columns from same report at runtime, i cannot create static function in DB instead i need function in report which will take values of another columns at runtime and execute.
Re: Rep-1401: '' Fatal PL/sql error, issue with Clob and varchar2 in Oracle 11.2.0.4.0 [message #624217 is a reply to message #624215] Fri, 19 September 2014 02:42 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's not a problem either. Functions accept many parameters, and you can use them in Reports' query, such as
select 
  t.id,
  stored_function_1 (t.col_1) result_1,
  t.emp_name,
  stored_function_2 (t.col_5, t.col_8) result_5
from your_table t
where ...

Previous Topic: ERROR IN REPORT GROUP
Next Topic: button in reports
Goto Forum:
  


Current Time: Thu Mar 28 11:17:22 CDT 2024