| How to downgrade Oracle version in a session [message #613568] |
Thu, 08 May 2014 02:37  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi,
I have procedure developed in Oracle version 11.2.0.3 which went fine.
Now the issue is, the same procedure is not working in Oracle 10.1.0.3 as its something to do with new features in Oracle 11g version i.e. the features I used are not supported in Oracle 10.1.0.3
Now, my question is how can test the 10g compatible procedure in in my oracle 11.2.0.3 database.
Is there a setting specific to session to downgrade the database version as I dont have 10.1.0.3
Thank you very much in advance.
Regards,
Pointers
|
|
|
|
|
|
| Re: How to downgrade Oracle version in a session [message #613571 is a reply to message #613569] |
Thu, 08 May 2014 02:46  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That only affects the optimizer. As far as I'm aware it has no effect on PL/SQL for starters. So if you're using a new 11g function it'll work regardless of that setting.
@pointers - If you're developing software for an oracle 10 DB then you need an oracle 10 DB to test on, so I suggest you get busy installing one.
|
|
|
|
| Re: How to downgrade Oracle version in a session [message #613574 is a reply to message #613571] |
Thu, 08 May 2014 02:58 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Yes, I missed to mention that point. It only effects the optimizer functionality, and is used to check the execution plans etc. It could be done at session and system level. You could also use it as a hint :
/*+ optimizer_features_enable('10.2.0.3') */
But all these settings will not disable any new SQL or PL/SQL features or enhancements. They will still work :
A small example with LISTAGG in 11g, would still work if I degrade the optimizer to 10g :
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Connected as LALIT@orcl
SQL> alter session set optimizer_features_enable='10.1.0.3';
Session altered
SQL> select deptno, listagg(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees from emp group by deptno;
DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
So LISTAGG still works in either cases.
|
|
|
|
| Re: How to downgrade Oracle version in a session [message #613575 is a reply to message #613568] |
Thu, 08 May 2014 03:01 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
pointers wrote on Thu, 08 May 2014 13:07the same procedure is not working in Oracle 10.1.0.3 as its something to do with new features in Oracle 11g version i.e. the features I used are not supported in Oracle 10.1.0.3
Could you please post what exactly is the error, or which feature is not compatible. You might have to rewrite and look for a workaround for that feature to make it work in 10g.
|
|
|
|
| Re: How to downgrade Oracle version in a session [message #613700 is a reply to message #613575] |
Fri, 09 May 2014 05:50 |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Yes, thats true, I am aware of the fact that I should re-write it, infact, I have done it. The issue started when I wanted to test it in 10g.
By the way, i was using collection of record in forall which worked in 11g but did not went well in 10g, so I had to declare individual column as seperate collections and I dot have many columns so i was able to declare
However, thank you for your time.
Regards,
Pointers
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
