Home » SQL & PL/SQL » SQL & PL/SQL » Varray Issue in 10.2.0.3
Varray Issue in 10.2.0.3 [message #270456] Wed, 26 September 2007 23:59 Go to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Hi,

Can anyone put some light on this one.

I have created one object having 2 columns

OBJECT:

create or replace TYPE additional_info_type AS OBJECT
(
Add_field_name VARCHAR2(5),
Add_field_value VARCHAR2(35)
)

TYPE:

create or replace TYPE additional_info_varray AS VARRAY(10) OF additional_info_type;

I am using this varray type as a datatype for one of the column for policy_locations table.

This was working perfectly on Version 10.2.0.2
SQL*Plus: Release 9.0.1.4.0 - Production on Thu Sep 27 10:32:10 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> DECLARE
2 CURSOR policy_locations_cur
3 (prm_session_no policy_register.preg_session_no%TYPE)
4 IS
5 SELECT rowid, '*' ins_upd_flag, 0 rate_grid_code ,'*' rate_struct_change,policy_locations.
*
6 FROM policy_locations
7 WHERE pl_session_no = prm_session_no
8 ORDER BY pl_location_code;
9
10 mmy_epl_cur policy_locations_cur%ROWTYPE;
11 BEGIN
12
13 dbms_output.put_line('Before Cursor');
14 FOR mmy_epl_cur IN policy_locations_cur('50201')
15 LOOP
16 dbms_output.put_line('Location Slno:'||to_char(mmy_epl_cur.pl_slno));
17 --dbms_output.put_line('Group Code:'||mmy_epl_cur.epl_group_code);
18 dbms_output.put_line('Location Code:'||to_char(mmy_epl_cur.pl_location_code));
19 END LOOP;
20 dbms_output.put_line('After Cursor');
21
22
23 EXCEPTION
24 WHEN OTHERS THEN
25 dbms_output.put_line('IN Exception :'||SQLERRM);
26 END ;
27 /
Before Cursor
Location Slno:1
Location Code:103100
Location Slno:2
Location Code:103101
Location Slno:4
Location Code:103102
Location Slno:3
Location Code:103103
Location Slno:5
Location Code:103104
Location Slno:6
Location Code:103105
Location Slno:7
Location Code:103106
After Cursor

PL/SQL procedure successfully completed.


Now we have applied the Release 3 (10.2.0.3) patch on the database and so this cursor started giving error.
Getting disconnected from Oracle.

SQL*Plus: Release 9.0.1.4.0 - Production on Thu Sep 27 10:27:55 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> DECLARE
2 CURSOR Policy_Locations_Cur(prm_Session_No Policy_Register.preg_Session_No%TYPE) IS
3 SELECT RowId,
4 '*' Ins_Upd_Flag,
5 0 Rate_Grid_Code,
6 '*' Rate_struct_Change,
7 Policy_Locations.*
8 FROM Policy_Locations
9 WHERE Pl_Session_No = prm_Session_No
10 ORDER BY Pl_Location_Code;
11 mMy_ePl_Cur Policy_Locations_Cur%ROWTYPE;
12 BEGIN
13 dbms_Output.Put_Line('Before Cursor');
14
15 FOR mMy_ePl_Cur IN Policy_Locations_Cur('12711092') LOOP
16 dbms_Output.Put_Line('Location Slno:'
17 ||To_Char(mMy_ePl_Cur.Pl_slNo));
18 --dbms_output.put_line('Group Code:'||mmy_epl_cur.epl_group_code);
19
20 dbms_Output.Put_Line('Location Code:'
21 ||To_Char(mMy_ePl_Cur.Pl_Location_Code));
22 END LOOP;
23
24 dbms_Output.Put_Line('After Cursor');
25 EXCEPTION
26 WHEN OTHERS THEN
27 dbms_Output.Put_Line('IN Exception :'
28 ||SQLERRM);
29 END;
30 /
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Hoping for some suggestion and solutions.

Thnx
Atul
Re: Varray Issue in 10.2.0.3 [message #270461 is a reply to message #270456] Thu, 27 September 2007 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hoping for some suggestion and solutions.

Suggestion: Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Varray Issue in 10.2.0.3 [message #270464 is a reply to message #270461] Thu, 27 September 2007 01:01 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Hi,

I had pasted the Execution details in the previous message.

Below is the Formatted code:

DECLARE 
	CURSOR policy_locations_cur 
	(prm_session_no 	policy_register.preg_session_no%TYPE)
    IS
    SELECT  rowid, '*' ins_upd_flag, 0 rate_grid_code ,'*' rate_struct_change,policy_locations.*
		FROM	policy_locations
	    WHERE	pl_session_no = prm_session_no
	ORDER BY  pl_location_code;

mmy_epl_cur	policy_locations_cur%ROWTYPE;
BEGIN 
	
	dbms_output.put_line('Before Cursor'); 
	FOR mmy_epl_cur IN policy_locations_cur('50201')
	LOOP
		dbms_output.put_line('Location Slno:'||to_char(mmy_epl_cur.pl_slno));
		dbms_output.put_line('Location Code:'||to_char(mmy_epl_cur.pl_location_code));		
	END LOOP;
	dbms_output.put_line('After Cursor'); 

EXCEPTION 
	WHEN OTHERS THEN 
		dbms_output.put_line('IN Exception :'||SQLERRM);
END ;
/
show error


Rest of the details can be referenced from the 1st message of this thread

Has anyone faced with this issue this looks to be a Bug in 10.2.0.3 patch

[Updated on: Thu, 27 September 2007 01:03]

Report message to a moderator

Re: Varray Issue in 10.2.0.3 [message #270471 is a reply to message #270464] Thu, 27 September 2007 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SQL*Plus: Release 9.0.1.4.0 - Production on Thu Sep 27 10:27:55 2007

Use a supported and certified configuration.

Regards
Michel
Re: Varray Issue in 10.2.0.3 [message #270486 is a reply to message #270471] Thu, 27 September 2007 03:25 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Michel,

Please brief me about your sentence.

What is wrong with this verison and the configuration

Regards
Atul
Re: Varray Issue in 10.2.0.3 [message #270500 is a reply to message #270486] Thu, 27 September 2007 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ 9.0 is no more supported
2/ 9.0 was the most buggy version Oracle ever release
3/ 9.0 and 10g were never been certified to cooperate.

Client / Server / Interoperability Support Between Different Oracle Versions, Metalink note 207303.1

Regards
Michel
Re: Varray Issue in 10.2.0.3 [message #270507 is a reply to message #270464] Thu, 27 September 2007 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do not report your own messages in an attempt to get a quicker reply.
icon4.gif  Re: Varray Issue in 10.2.0.3 [message #270514 is a reply to message #270500] Thu, 27 September 2007 04:22 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
"SQL*Plus: Release 9.0.1.4.0 - Production on Thu Sep 27 10:27:55 2007"

9.0.1.4.0 is my client version.

Please look into the Server Verion

"Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production"

Also i am not facing any connection problem...

This error is happening when i executed the cursor for a
table which has column with varray datatype. This Varray datatype has 2 columns in it which was working file until the release 3 (10.2.0.3) patch was applied.

i have given the details of the object and Varray in my previous message.


Re: Varray Issue in 10.2.0.3 [message #270517 is a reply to message #270514] Thu, 27 September 2007 04:35 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Working from time to time does not mean working ALL the time.
You are lucky to connect but you are not sure it will last.
And it will not.
Full stop.

Stop wasting your time and ours trying to find a workaround that does not exist.

Regards
Michel
Previous Topic: Table Locks
Next Topic: Exception Handling
Goto Forum:
  


Current Time: Sat Dec 10 14:31:40 CST 2016

Total time taken to generate the page: 0.20716 seconds