Home » SQL & PL/SQL » SQL & PL/SQL » migration to Oracle 10gR2 issue (Oracle,10.2.0.4, Windows)
icon4.gif  migration to Oracle 10gR2 issue [message #376665] Thu, 18 December 2008 03:50 Go to next message
sengol.susai@kumaran.com
Messages: 7
Registered: December 2008
Location: Chennai
Junior Member
We have a aplication running under Oracle 9.2.0.4 DB. we migrated our DB to Oracle 10gR2. While running the report, we got an error:

ORA-00904: "A3"."FACILITY_SYSTEM_ID": invalid identifier
ORA-06512: at "CEAS_MK_RPT.GET_LIST", line 12

The SQL query which causes this error is:

INSERT INTO gtt_facility_seq_generator
(date_range)
SELECT get_list
(CURSOR (SELECT auf.facility_system_id
FROM authorized_facility auf
WHERE auf.facility_system_id = afs.facility_system_id
AND auf.credit_application_system_id = :p_cred_appln_id
)
)
FROM authorized_facility afs,
(SELECT af.facility_system_id facility_system_id
FROM facility_obligor fo, authorized_facility af, party p
WHERE fo.facility_system_id = af.facility_system_id
AND fo.party_system_id = p.party_system_id
AND NOT EXISTS (
SELECT NULL
FROM facility_third_party_subst iftps
WHERE iftps.facility_system_id =
fo.facility_system_id)
) tab1
WHERE afs.credit_application_system_id = :p_cred_appln_id
AND tab1.facility_system_id = afs.facility_system_id
ORDER BY afs.creation_date;

The content of function get_list() is:

CREATE OR REPLACE FUNCTION CEAS_MK_RPT.Get_List
(
p_cursor IN sys_refcursor
)
RETURN VARCHAR2
IS
l_sep VARCHAR2(4);
l_text VARCHAR2(30000);
l_text_return VARCHAR2(30000);
BEGIN
LOOP
FETCH p_cursor INTO l_text;
EXIT WHEN p_cursor%NOTFOUND;

l_text_return := l_text_return || l_sep || l_text;
l_sep := CHR(10);
END LOOP;
CLOSE p_cursor;
RETURN l_text_return;
dbms_output.put_line ('a');
END Get_List;

The same report was executed perfectly in Oracle 9i. Kindly help us to short out this issue. Thanks in advance.

Regards,
Sengol S
Re: migration to Oracle 10gR2 issue [message #376669 is a reply to message #376665] Thu, 18 December 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have not the same user environment in both cases.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: migration to Oracle 10gR2 issue [message #376701 is a reply to message #376669] Thu, 18 December 2008 05:32 Go to previous messageGo to next message
sengol.susai@kumaran.com
Messages: 7
Registered: December 2008
Location: Chennai
Junior Member
Thanks for your reply.
what you mean by user environment? and how to solve this issue?
- Sengol S
Re: migration to Oracle 10gR2 issue [message #376708 is a reply to message #376701] Thu, 18 December 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
schema, table, index, privilege...

Regards
Michel
icon4.gif  Re: migration to Oracle 10gR2 issue [message #376712 is a reply to message #376708] Thu, 18 December 2008 06:10 Go to previous messageGo to next message
sengol.susai@kumaran.com
Messages: 7
Registered: December 2008
Location: Chennai
Junior Member
we had exported database dump from Oracle 9i and imported into Oracle 10gR2 with out any change..

Moreover the same query was executed successfully in 10gR2 after removeing the INSERT INTO part and returns records.

[Updated on: Thu, 18 December 2008 06:27]

Report message to a moderator

Re: migration to Oracle 10gR2 issue [message #376725 is a reply to message #376712] Thu, 18 December 2008 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which kind of export and import?

Regards
Michel
icon4.gif  Re: migration to Oracle 10gR2 issue [message #376743 is a reply to message #376665] Thu, 18 December 2008 07:35 Go to previous messageGo to next message
sengol.susai@kumaran.com
Messages: 7
Registered: December 2008
Location: Chennai
Junior Member
System level full backup.
Re: migration to Oracle 10gR2 issue [message #376744 is a reply to message #376743] Thu, 18 December 2008 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.
Post it formatted.

Regards
Michel

icon4.gif  Re: migration to Oracle 10gR2 issue [message #376747 is a reply to message #376744] Thu, 18 December 2008 07:53 Go to previous messageGo to next message
sengol.susai@kumaran.com
Messages: 7
Registered: December 2008
Location: Chennai
Junior Member
In Oracle 9i

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> INSERT INTO gtt_facility_seq_generator
2 (date_range)
3 SELECT get_list
4 (CURSOR (SELECT auf.facility_system_id
5 FROM authorized_facility auf
6 WHERE auf.facility_system_id = afs.facility_system_id
7 AND auf.credit_application_system_id = 1000107
8 )
9 )
10 FROM authorized_facility afs,
11 (SELECT af.facility_system_id facility_system_id
12 FROM facility_obligor fo, authorized_facility af, party p
13 WHERE fo.facility_system_id = af.facility_system_id
14 AND fo.party_system_id = p.party_system_id
15 AND NOT EXISTS (
16 SELECT NULL
17 FROM facility_third_party_subst iftps
18 WHERE iftps.facility_system_id =
19 fo.facility_system_id)
20 ) tab1
21 WHERE afs.credit_application_system_id = 1000107
22 AND tab1.facility_system_id = afs.facility_system_id
23 ORDER BY afs.creation_date;

2 rows created.

SQL>


In Oracle 10g

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> INSERT INTO gtt_facility_seq_generator
2 (date_range)
3 SELECT get_list
4 (CURSOR (SELECT auf.facility_system_id
5 FROM authorized_facility auf
6 WHERE auf.facility_system_id = afs.facility_system_id
7 AND auf.credit_application_system_id = 1000107
8 )
9 )
10 FROM authorized_facility afs,
11 (SELECT af.facility_system_id facility_system_id
12 FROM facility_obligor fo, authorized_facility af, party p
13 WHERE fo.facility_system_id = af.facility_system_id
14 AND fo.party_system_id = p.party_system_id
15 AND NOT EXISTS (
16 SELECT NULL
17 FROM facility_third_party_subst iftps
18 WHERE iftps.facility_system_id =
19 fo.facility_system_id)
20 ) tab1
21 WHERE afs.credit_application_system_id = 1000107
22 AND tab1.facility_system_id = afs.facility_system_id
23 ORDER BY afs.creation_date;
SELECT get_list
*
ERROR at line 3:
ORA-00904: "A3"."FACILITY_SYSTEM_ID": invalid identifier
ORA-06512: at "CEAS_MK_RPT.GET_LIST", line 12


SQL>
Re: migration to Oracle 10gR2 issue [message #376749 is a reply to message #376747] Thu, 18 December 2008 07:57 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I'd have a look at the GET_LIST function.
icon4.gif  Re: migration to Oracle 10gR2 issue [message #376756 is a reply to message #376749] Thu, 18 December 2008 08:08 Go to previous messageGo to next message
sengol.susai@kumaran.com
Messages: 7
Registered: December 2008
Location: Chennai
Junior Member
CREATE OR REPLACE FUNCTION CEAS_MK_RPT.Get_List
(
p_cursor IN sys_refcursor
)
RETURN VARCHAR2
IS
l_sep VARCHAR2(4);
l_text VARCHAR2(30000);
l_text_return VARCHAR2(30000);
BEGIN
LOOP
FETCH p_cursor INTO l_text;
EXIT WHEN p_cursor%NOTFOUND;

l_text_return := l_text_return || l_sep || l_text;
l_sep := CHR(10);
END LOOP;
CLOSE p_cursor;
RETURN l_text_return;
dbms_output.put_line ('a');
END Get_List;
Re: migration to Oracle 10gR2 issue [message #376760 is a reply to message #376756] Thu, 18 December 2008 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 18 December 2008 11:12
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


Re: migration to Oracle 10gR2 issue [message #376764 is a reply to message #376749] Thu, 18 December 2008 08:52 Go to previous message
sengol.susai@kumaran.com
Messages: 7
Registered: December 2008
Location: Chennai
Junior Member
CREATE OR REPLACE FUNCTION CEAS_MK_RPT.Get_List
(
        p_cursor                IN      sys_refcursor
)
RETURN VARCHAR2
IS
        l_sep           VARCHAR2(4);
        l_text          VARCHAR2(10000);
        l_text_return   VARCHAR2(10000);
BEGIN
        LOOP
                FETCH p_cursor INTO l_text;
                EXIT WHEN p_cursor%NOTFOUND;
                l_text_return := l_text_return || l_sep || l_text;
                l_sep := CHR(10);
        END LOOP;

        CLOSE p_cursor;

        RETURN l_text_return;

EXCEPTION
WHEN OTHERS THEN
	 dbms_output.put_line ('a');
END Get_List;

[Updated on: Thu, 18 December 2008 23:11]

Report message to a moderator

Previous Topic: [Problem] Stored Procedure and Scheduler
Next Topic: Dynamic SQL Update using WHERE CURRENT OF CLAUSE
Goto Forum:
  


Current Time: Fri Dec 09 23:19:10 CST 2016

Total time taken to generate the page: 0.10208 seconds