What’s wrong with this code ?
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
SQL> drop table lsc_t ;
Table dropped.
SQL> create table lsc_t (x varchar2(255), y date);
Table created.
SQL> begin
2 null;
3 exception when others then
4 insert into lsc_t (x,y)
5 values (sqlerrm, sysdate);
6 end;
7 /
PL/SQL procedure successfully completed.
I was in the process of migrating a database with 10g and this piece of code became invalid 🙁
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table lsc_t ;
Table dropped.
SQL> create table lsc_t (x varchar2(255), y date);
Table created.
SQL> begin
2 null;
3 exception when others then
4 insert into lsc_t (x,y)
5 values (sqlerrm, sysdate);
6 end;
7 /
values (sqlerrm, sysdate);
*
ERROR at line 5:
ORA-06550: line 5, column 17:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
SQL>
Ok, the code is wrong. It has been running for half a decade in production but the exception was never reached.
Note it is a bug, and putting sqlerrm after sysdate would have generate the exception even in 9i
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
SQL> begin
2 null;
3 exception when others then
4 insert into lsc_t (y,x)
5 values (sysdate,sqlerrm);
6 end;
7 /
values (sysdate,sqlerrm);
*
ERROR at line 5:
ORA-06550: line 5, column 25:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
Just having fun with 10g migration 😉
well, i don’t remember, but this can be the reason because i make as an example in the manuals:
err_msg := SUBSTR(SQLERRM, 1, 100);
🙂
going against documentation is recipe for trouble anytime.
according to 9i docs..
”
You cannot use SQLERRM directly in a SQL statement. First, you must assign the value of SQLERRM to a local variable, as follows:
my_sqlerrm := SQLERRM;
…
INSERT INTO errors VALUES (my_sqlerrm, …);
”
what they missed out is this ” 9i compiler would not enforce you to use this guideline. But you are likely to run into issues when you migrate to our next release”
🙂
sure, this is the idea, you use a variable. The point is, the package was valid in 9i and migrating to 10g invalidated …
Hi Laurent,
I found and both documentations 9i and 10g and it says :”You cannot use SQLERRM directly in a SQL statement. First, you must assign the value of SQLERRM to a local variable, as follows..”.
I sincerily don’t know how it’s work on 9i!!
So,I’m Junior DBA and would like to know how do you get bugs easily ?Do you have a explanation?hehehe
Have a good day.
Regards,
Julio Cesar Correa
🙂 once you see a strange behaviour, you will find out the bug is most of the time between keyboard and chair, but sometimes it is really unexpected, then you try to reduce a huge 10K lines package to a single-liner until you found something that is clearly a bug !
I seldom publish bugs on my blog, I mostly report them to metalink and I harass the metalink engineer until they open a bug. The bug above is one of my migration experience, that’s why I published it.
One of my oldest bug report was in 8.1.7.0 where you could drop a table in database DB01 and still access the dropped table from database DB02. However the support engineer did not open a bug and said : “your case is not consistent”… I was quite junior at that time and did not chase this (the bug and many other distributed issues were also fixed in 8.1.7.2). Nowaday I would have chase it until they create the bug report or point me to the bug number.
Once it tooks me 1 year to convince them to open a security bug, because an user with only CREATE SESSION could create type. The bug was not reproducible at will (but it was an oneliner and Metalink did reproduce it). Sometimes bugs have so little impact but fixing them will be such a large architectural change that you must put lots of effort and find a business justification !
A long answer, thanks for reading so far
Hi Laurent,
Sincerely,I don’t remember if I read your answer on My 5th,2009,but always read your blog at least once a week and today I stop and look better!
Thanks for share your experience with me and others site readers!
Ahh,not bad for your oldest bug in 8.1.7.0 :)!
Now I need read something like it because I’ll take the SQL Exper again but on this time I want to learn much more than only know SQL but try to understand more details while I study for a test.
🙂 maybe a WordPress plugins to subscribe per email to the comments would help?
Yeah, I could not forget that one, ten years ago…
Hi,
Oh yeah.I’ll try.Your blog is in my “read list” on blogger but comments update doesn’t appear for me .
I’m look for a solution on Blogger now.
Speaking about wordpress,I’ve my “backup” of Blogger there and need to import my posts!
good luck then!