Re: Help ORA-07445: Struck again

On Mar 24, 3:28 am, "pankaj_wolfhun..._at_yahoo.co.in" <pankaj_wolfhun..._at_yahoo.co.in> wrote:
> 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

Not that it will help, but I'm wondering why you would use BINARY_INTEGER in 9i?

Also you might try avoiding the final bulk collect by changing the ".COUNT = 0". In your code there will be one final iteration where rows retrieved is less than limit.

Also what is this supposed to do?

WHEN OTHERS THEN

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