Help ORA-07445: Struck again

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 23 Mar 2007 20:28:45 -0700
Message-ID: <1174706925.815053.255430@e65g2000hsc.googlegroups.com>

Greetings,

           While executing the following procedure I got an ORA-07445: error. I googled out on this error and came to know that I need to contact oracle support team.

But the same proc when made to run on some different application is running just fine.
moreover on 10g this runs just fine.

Here I am giving some information (proc content, error, alert.log entry, db version info, os info).

I am not able use .TRC files pointed by alert.log. Its giving some permission problem.

It seems my current version is missing some patch that is available in 10g.

I even tried with only one record and it still fails.

Can someone point me a loop hole in my current procedure or some better way of writing the following procedure or do i need to contact Oracle support time?

let me know if something else is required

Proc content:

CREATE OR REPLACE PROCEDURE proc_test
 AS

• variables used in this procedure v_file UTL_FILE.file_type; v_filename VARCHAR2 (100); v_l_text VARCHAR2 (32767) := NULL; v_scheduled_date VARCHAR2 (10); v_location table2.col1%TYPE;

      CURSOR cursor1
      IS
         SELECT tab1.col1 col1,
                tab1.col2 col2,
                tab1.col3 col3,
                tab1.col4 col4,
                TO_CHAR (SYSDATE, 'YYYYMMDD') col5
           FROM table1 tab1
          WHERE tab1.col6 = 'BLB';



      -- variable to hold Cursor return variables

      inst1   cursor1%ROWTYPE;

      CURSOR cursor2
      IS
         SELECT col1,
                col2,
                col3,
                col4,
                col5,
                col6,
                col7,
                col7_full_name,
                col7_iso,
                col8,
                col8_freq,
                col8_typ,
                col9,
                col10,
                col11,
	        col12,
                col13,
                col13_shrt,
                col14
           FROM table3;

      TYPE type_tab3 IS TABLE OF table3%ROWTYPE
      INDEX BY BINARY_INTEGER;



      -- instance of the record type declared above

      inst_tab3       type_tab3;

   BEGIN       OPEN cursor1;

      LOOP
         FETCH cursor1
          INTO inst1;

         EXIT WHEN cursor1%NOTFOUND;
         --assigning the file name to a variable
         v_filename :=
               inst1.col4
            || '_'
            || inst1.col5
            || '.txt';


         SELECT tab2.col2
           INTO v_location
           FROM table2 tab2
          WHERE tab2.col1 = inst1.col1
            AND tab2.col2 = inst1.col2;


         -- MY_FIR is a directory object mapped to a physical path on
O.S drive
	 v_file := UTL_FILE.fopen ('MY_FIR', v_filename, 'w');


         OPEN cursor2;

         LOOP
            FETCH cursor2
            BULK COLLECT INTO inst_tab3 LIMIT 1000;

            EXIT WHEN inst_tab3.COUNT = 0;

            FOR rec_loop IN 1 .. inst_tab3.COUNT
            LOOP
               -- Read and output first line.

               v_l_text :=
                     TRIM (inst_tab3 (rec_loop).col1)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col2)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col3)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col4)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col5)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col6)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col7)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col7_full_name)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col7_iso)
                  || inst1.field_delimiter
                  || inst_tab3 (rec_loop).col8
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col8_freq)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col8_typ)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col9)
                  || inst1.field_delimiter
                  || inst_tab3 (rec_loop).col10
                  || inst1.field_delimiter
	          || TRIM (inst_tab3 (rec_loop).col11)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col12)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col13)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col13_shrt)
                  || inst1.field_delimiter
                  || TRIM (inst_tab3 (rec_loop).col14);

		UTL_FILE.put_line (v_file, v_l_text);
		END LOOP;
         END LOOP;

         CLOSE cursor2;

         -- Close the file.
         UTL_FILE.fclose (v_file);
      END LOOP;

      CLOSE cursor1;

EXCEPTION WHEN OTHERS THEN

	RAISE;
	DBMS_OUTPUT.PUT_LINE('exception reached');

END proc_test;

>From Alert.log:

Errors in file /home/udump/psnorad1_ora_24885.trc: ORA-07445: exception encountered: core dump [ldxeti()+20] [SIGSEGV] [Address not mapped to object] [0x0] [] []

DB Version Info:

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for Solaris: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production

OS Info:

SunOS 5.9 Received on Fri Mar 23 2007 - 22:28:45 CDT