Since I am quite likely to pass the milestone on the train tomorrow morning, I thought I’d commemorate ‘nearly’ getting there ahead of time:
Forty thousand pieces of music in 4 and a bit years… who’d have thought?!
You may be lucky and work in an all-11g (or all-10g, etc) environment… but I have to manage everything from version 7 upwards. This makes it tricky to write any SQL scripts which can be guaranteed to run in all environments. Try this, for example, in 10g and everything will be fine:
select a.sid, a.serial#, substr(b.sql_text,1,40) as sql, b.hash_value, b.sql_id from v$session a, v$sqlarea b where a.sql_address=b.address;
But run it against a version 9 (or earlier) database, and you’ll merely get an ORA-00904, because “SQL_ID” is not a column in the V$SQLAREA view in those versions. Of course, I could remove that column from my query entirely: coding to the least-common denominator and mentioning only things which exist in all possible versions… but that would mean missing out on useful information when I run against 10g or 11g databases. Why miss out on knowing stuff when the information is available?
What I want, of course, is one SQL script that can run in 9i, 10g and 11g environments without error, returning me SQL_ID if it’s possible and skipping it if it’s not.
Well, a possible generic query to tell you what version of the database you are running on might look like this:
select case when banner like '%9i%' then '9i' when banner like '%10g%' then '10g' when banner like '%11g%' then '11g' else '0' end from v$version where banner like 'Oracle%';
If the result of that query is selected ‘into’ a variable, we can test that variable’s value in an anonymous PL/SQL block and do conditional execution accordingly. In other words, logically, you’d have this sort of thing:
if v_version = '9i' then --run version of SQLAREA query that misses out SQL_ID column; elsif v_version = '10g' or v_version = '11g' then --run version of SQLAREA query that includes the SQL_ID column; end if;
Of course, at this point, you’re writing an anonymous PL/SQL block and you’ve accordingly lost the ability to have the results of queries effortlessly displayed. The trick there is to declare a refcursor variable, have your query select its results into that variable and then have SQL*Plus print out the contents of the refcursor.
You also can’t mention ‘SQL_ID’ even in the v_version=’10g’ section of the code, because it will be spotted at compile time as being a reference to a column that doesn’t exist on a 9i database. An error will then be thrown before the 9i-flavour of the query even gets a chance to run. The workaround for that is to use dynamic SQL -wrap the code in quotes, basically- so that what is inside the quotes is only evaluated at run-time …and if the logic means one part of your code isn’t actually run, then any “errors” it contains won’t be triggered.
Taking those things into account, then, you might end up with code like this:
set autoprint on variable y refcursor declare v_version varchar2(5); v_sql9 varchar2(4000); v_sql10 varchar2(4000); p_results sys_refcursor; begin v_sql9 := 'select a.sid, a.serial#, substr(b.sql_text,1,40) as sql, b.hash_value from v$session a, v$sqlarea b where a.sql_address=b.address'; v_sql10:= 'select a.sid, a.serial#, substr(b.sql_text,1,40) as sql, b.hash_value, b.sql_id from v$session a, v$sqlarea b where a.sql_address=b.address'; select case when banner like '%9i%' then '9i' when banner like '%10g%' then '10g' when banner like '%11g%' then '11g' else '0' end into v_version from v$version where banner like 'Oracle%'; if v_version = '9i' then open :y for v_sql9; elsif v_version = '10g' or v_version = '11g' then open :y for v_sql10; end if; end; /
Taking that apart, bit by bit:
set autoprint on variable y refcursor
Autoprint enables the contents of bind variables to be automatically displayed, once a query has completed. The next line then creates such a variable, called “y”, which is declared to be of type ‘refcursor‘ (essentially, a resultset, containing multiple rows).
declare v_version varchar2(5); v_sql9 varchar2(4000); v_sql10 varchar2(4000);
Here, I simply create three varchar2 variables to hold the contents of the Oracle version, the 9i and the 10g version of the query. The fourth variable is declared as type sys_refcursor, so that multi-row results can be selected into it.
v_sql9 := 'select a.sid, a.serial#, substr(b.sql_text,1,40) as sql, b.hash_value from v$session a, v$sqlarea b where a.sql_address=b.address';
Now I set up the actual values for the version-dependent query variables. Note that they are wrapped in single quotes: this stops the PL/SQL compiler spotting that the v_sql10 one contains a reference to something that doesn’t actually exist in a 9i database. The 9i version omits reference to the SQL_ID column; otherwise, the two are identical.
select case when banner like '%9i%' then '9i' [...]
Now comes the version-detection query I mentioned before, only this time it selects its results into the v_version variable previously declared.
if v_version = '9i' then open :y for v_sql9; elsif v_version = '10g' or v_version = '11g' then open :y for v_sql10; end if;
This is the bit of code that does all the work: it executes one of the dynamic SQL statements, depending on the Oracle version detected, selecting its results into the ‘y’ bind variable I declared right at the start. When this query has completed, the contents of ‘y’ will be printed out automatically (because of the autoprint setting I made at the start, too).
Using the one SQL script, therefore, I can now get results from both 9i and 10g databases:
SQL> / PL/SQL procedure successfully completed. SID SERIAL# SQL HASH_VALUE SQL_ID ---------- ---------- ---------------------------------------- --- ------ ------------- 306 38043 select 207344822 77s0p3s65rp5q 295 17502 INSERT /*+ append */ into scott.EMP 2033560799 0qtz8ftw bb6z 299 44668 INSERT /*+ append */ into scott.EMP 2033560799 0qtz8ftwmbb6z 293 9141 INSERT /*+ append */ into scott.EMP 2033560799 0qtz8ftwmbb6z
SQL> / PL/SQL procedure successfully completed. SID SERIAL# SQL HASH_VALUE ---------- ---------- ---------------------------------------- --- ------ 6 1 select con#,type#,condlength,intcols,rob 1937775682 7 1 select o.owner#,o.name,o.namespace,o.rem 431456802 213 11205 SELECT /*+ index(ORDER_INFO ORDER_INFO_P 1942381241 283 56071 declare nm owa.vc_arr; vl owa. 1860200125
My choice of a ‘CASE’ statement means this code isn’t truly cross-version-compatible, because it won’t work in version 7 or 8i, and I haven’t bothered to write any code in this example that handles the ”not 9i, 10g or 11g” situation, though the v_version variable is set to ’0′ in those cases and could be dealt with if needed. But that’s trivial (I could always stick to the decode function if I really wanted to, instead of using “CASE”, for example)… and not something I feel the need to do right now!
I was asked to pass on the following, and I have no hesitation in doing so:
I am unsure whether you are aware but we are now under three weeks away from closing Call for Papers for the UKOUG Conference 2012. As a well known Oracle blogger, I would like to ask if you wouldn’t mind mentioning the Call for Papers in your blog?
I would greatly appreciate your assistance to encourage more potential speakers to come forward to present, especially if sharing hints and tips or lessons learnt, which as you know user group conference attendees find so valuable.
If this is possible, I’d be extremely grateful and would love to see the finished article once online.
The link to the Call for Papers page is as follows – http://2012.ukoug.org/default.asp?p=9306
To assist, please see the end of the email for some extracts taken from a fantastic blog written by James Koopmann in support of a previous UKOUG Conference Call for Papers.
Should you have any further questions, please do not hesitate in contacting me.
Additionally, your blog has been very entertaining!
UK Oracle User Group
591-593 Kingston Road | Wimbledon | London | SW20 8SA
“If you have never been to the prestigious UKOUG Conference, you are missing something special.”
“The UKOUG annual conference is regularly rated as ‘top of the list’ for events to attend. The International representation is very strong and with the proliferation of a global economy, this is one of those conferences where you can actually get a flavour and appreciation for the issues and concerns on a global scale.
“Organised by an independent User Group, there is a great sense of community bringing Users, Partners and Oracle together for great days of learning, discussion and networking.”
“One of the main reasons for visiting this conference is to gain insight into solutions that are being developed within the database community. Some of these vendors are specific to this region of the world and your only avenue to meet them face to face, is by going to the UKOUG conference”
“Also there is no better way to get to know the issues than to actually talk to the users of Oracle products. Do you want to actually go through the headache of solving the same problem someone else has already solved? Sparking a conversation with a like-minded attendee that has had the same, or even similar, issue as yours allows you to drastically reduce the time involved in producing viable solutions and getting to ‘implementation’ sooner.”
“The UKOUG actively encourages the Oracle Community to get involved, this is your user group, it’s for you and about you. Share your knowledge and experiences with your peers by submitting an abstract and telling your story.”
I have to say, I’ve never been to UKOUG, though I’d like to, if only I had something to talk about. (And someone to pay the fare!) I have read lots of reports over the years from people who have been, though, and it sounds to be one of those user groups that remain fresh, informative, entertaining and (somewhat unusually these days) worthwhile. So, if you happen to be a reader of mine, with something Oracle-y to share, and are geographically proximate to the UK at the relevant time, please get in touch with Marisa.
These three files are of the same 57-second piece of music. One was ripped to FLAC, one was ripped to a 75%-quality WMA and one was ripped to a 10%-quality WMA:
When you look at the original file sizes involved, the FLAC is 3MB, the 75% WMA is 510KB and the 10% WMA is just 200KB.
However, I’ve now converted all three files back to [uncompressed] WAV, so that -give or take a dozen bytes or so- all file sizes are pretty much identical (9MB or thereabouts). You won’t be able to tell which is which just by comparing file size, in other words, but each has been encoded at wildly different quality levels.
So the question is, can you tell which file is which, just by listening (checking the wave form in something like Audacity is not allowed!)?
An interesting problem came up at work the other day. A DBA had been trying to import data from one database (created in a standard 8-bit characterset) to another (created in the UTF-8 characterset). The import failed because, on one table, the length of the data being imported exceeded the length of the column in the new database.
How could this be? Well, the column was a VARCHAR2(10) in both databases, but the new database was using a variable length characterset: UTF-8 allows between 1 and 4 bytes per character. Something that had been stored as a single byte in the original database was now being stored as a 2-, 3- or 4-byte character in the new database, thus blowing the budget on the original 10-bytes allowed for this particular column. (For the record, Microsoft’s smart quotes will do this!)
As a workaround, and a way to allow the import to finish without incident, the DBA involved made the decision to pre-create this table in the new database, before doing the import, with a declaration of VARCHAR2(10 CHAR) -the use of character semantics in this way ensures that the column is allowed to store 10 characters no matter how many bytes might be needed to do so. And that certainly solved the import problem.
But it presented a new worry, the answer to which I didn’t know for sure, but could only guess: if there was PL/SQL code in the original database which referenced that column and loaded it into a variable declared as a VARCHAR2(10), would that code now break when it tried to run in the new database?
Put it another way: does PL/SQL use the overall database byte/character semantics? Or does it match the table semantics it’s run against?
My guess was that the code would break, because PL/SQL would use the default database semantics for column/variable length, not the actual semantics used in any specific case. But I didn’t know for sure, and so a test was in order.
Well, the first thing is to make sure our client session is working in the right characterset, otherwise all bets are off:
[oracle@watt ~]$ export NLS_LANG=.AL32UTF8
And here’s the initial state of play:
SQL> select property_name, property_value 2 from database_properties 3 where property_name like 'NLS_CHAR%' 4 or property_name like '%SEMANT%'; PROPERTY_NAME PROPERTY_VALUE ---------------------------- --------------------- NLS_CHARACTERSET AL32UTF8 NLS_LENGTH_SEMANTICS BYTE
So both my client and database use the AL32UTF characterset (variable in length up to 4 bytes per character). My database also uses “BYTE semantics” (meaning that if something is said to be VARCHAR2(10), the ’10′ means 10 bytes, not 10 characters. This is actually the default value for NLS_LENGTH_SEMANTICS, and is how most Oracle databases (probably) would be configured.
Now let me create a table and do what my original DBA did: over-ride the default length semantics and make a column capable of storing a fixed number of characters no matter how many bytes are needed to store them:
SQL> create table hjr_t1 (col1 varchar2(10 CHAR)); Table created.
By specifying ‘CHAR’, I’m insisting that the ’10′ in the varchar(10) bit of syntax means ’10 characters’. No matter how many bytes are needed to store them all. This hopefully explains the following output:
SQL> select owner, data_length from dba_tab_columns 2 where table_name='HJR_T1'; OWNER DATA_LENGTH ------------------------------ ----------- SYS 40
Since UTF-8 is a variable width character set that, sometimes, needs four bytes to store certain characters, Oracle has internally made the column 40 bytes long to deal with that possibility.
Now let me store some data in this table:
SQL> insert into hjr_t1 values ('ÁÇÖÜßîĂĆŐş'); 1 row created. SQL> commit; Commit complete.
So that’s 10 ‘foreign’ characters, and they will each definitely take up more than a single byte of storage. You can see that with this query:
SQL> select col1, length(col1), lengthb(col1) from hjr_t1; COL1 LENGTH(COL1) LENGTHB(COL1) -------------------- ------------ ------------- ÁÇÖÜßîĂĆŐş 10 20
The LENGTH function always shows you the number of characters in a string (and has done so for all time). The LENGTHB function, though, shows you the number of bytes a string represents. Again, in a single-byte characterset environment (or one that only used plain English), there wouldn’t be a difference between the two… but that doesn’t apply here!
So now the original question: if I write code that assigns table data to a VARCHAR2(10) variable, what will happen? Er, this:
SQL> declare 2 v1 varchar2(10); 3 begin 4 select col1 into v1 from hjr_t1; 5 dbms_output.put_line(v1); 6 end; 7 / declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4
So I was right in my initial guess: code which mentions varchar2s without specifying byte or character semantics, when the database itself uses BYTE semantics by default, is assumed itself to have meant byte semantics… and when transposed to a multi-byte characterset database, that code can fail.
Of course, you could change your database semantics:
SQL> alter system set nls_LENGTH_SEMANTICS=CHAR; System altered. SQL> set serveroutput on SQL> declare 2 v1 varchar2(10); 3 begin 4 select col1 into v1 from hjr_t1; 5 dbms_output.put_line(v1); 6 end; 7 / ÁÇÖÜßîĂĆŐş PL/SQL procedure successfully completed.
And you could even re-write your code: if you specifically declare v1 to be varchar2(10 CHAR), the problem goes away, too.
But there’s a further problem:
alter system set nls_LENGTH_SEMANTICS=BYTE;
That just makes the database use byte semantics once more, as our original database would have done. Now let’s write some stored procedural code:
SQL> create or replace procedure hproc 2 is 3 v1 varchar2(10); 4 begin 5 select col1 into v1 from hjr_t1; 6 dbms_output.put_line(v1); 7 end; 8 / Procedure created.
Now that’s pretty much exactly the same code as before, only now it’s part of a stored procedure. Let’s try executing it:
SQL> exec hproc BEGIN hproc; END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SYS.HPROC", line 5 ORA-06512: at line 1
And we get the expected problem about the data not fitting the 10-bytes allocated to the v1 variable. No problems: let’s switch to CHAR semantics:
SQL> alter system set nls_LENGTH_SEMANTICS=CHAR; System altered. SQL> SQL> exec hproc BEGIN hproc; END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SYS.HPROC", line 5 ORA-06512: at line 1
Ooops. It seems that stored procedures created when BYTE semantics are in force always use BYTE semantics, even if you alter the system to use CHARACTER semantics.
Simply put, if you’ve got a bazillion triggers, functions, packages or procedures that thought they were dealing with a column of 10 bytes, they will always expect 10 bytes, even if you change the default database semantics to be characters. You could always switch the semantics to CHAR and re-compile all your code, of course. At the point of re-compilation, all mentions of varchar2(x) would be assumed to be referring to ‘x characters’. That would get my code working once more… but it’s an awful lot of re-compilation and therefore isn’t exactly a trivial fix on a big database! The other problem with doing that is the fact that you’d end up, effectively, assigning 4 times the amount of memory to every varchar2 variable ever used, anywhere… even though the DBA only altered the semantics on one column in one table. Goodbye code breakage -but hello memory problems!
The original DBA has, therefore, created a bit of a problem! Certainly, creating the column as VARCHAR2(10 CHAR) allowed the import to proceed. But now we have, potentially, lots of code in the database that will suddenly have to deal with 2, 3 or 4 times more data than it was expecting to, and it will break as a result. No ‘alter system’ or ‘alter session’ command can fix that up, either!
As it happens, the only data that was preventing the initial import were things like Microsoft smart qotes, em-dashes and other wonders produced by Word ‘auto-correcting’ what you type. None of that data was really indispensable, in other words: we could have done a bulk bit of data conversion using the TRANSLATE function, swapping smart quotes for dumb ones, for example. Had we done that, there would have been no need to mess about with character or byte semantics in the first place: UTF-8 remains a single-byte character set when it’s dealing with basic English, so no column data would have threatened to ‘expand’ during the import. And thus none of our code would be liable to break (at least, not for these reasons!)
As it is, the imported database is now in production …and we have a bit of an issue on our hands!
The moral of the story: don’t mess with your semantics. Pick one when you first create your database and leave it at that. Don’t start mixing-and-matching for different tables, still less for different columns within the same table… for that way, chaos lies!
Gracie is a somewhat peculiar cat: mis-sexed by the Vet at the start of his life, he got stuck with his somewhat feminine name before it was feasible to change it. He nevertheless went on to growl, scratch and bite with the best of them. It took him a long time (and the example of my other cat) to learn how to purr, but he’s been pretty good at it for several years now. He’s now playful, likes to stare you out as you’re enjoying dinner (so that he can get the scraps you feel guiltily obliged to share with him) and takes great pleasure in head-butting you when you’re least expecting it.
At one time he acquired the nickname ‘Adolph’, because he’d walk into a new home and immediately take it over, as though he’d lived there all his life. Let us politely call his a ‘dominant feline personality’!
He’s seldom in the garden (he likes his comfy chair too much for that), but has been seen on more than one occasion to let birds walk over him: the effort to do anything but watch them was clearly beyond his energy budget that day. He’s also been known to run up tree trunks and onto the underside of branches, before the law of gravity exerted itself and made him fall out. He is, possibly, the only cat that routinely landed on his head in such circumstances.
And for all those reasons and more, we shall miss him after Saturday, when he takes his final trip to the vet. He had two strokes last year and has been a bit wobbly on his legs ever since. This week, he’s just had two more, and although he seems as cheerful as ever, he’s struggling to get around in a straight line and he’s getting thin. We’ve decided we have to make the call for him, since he’s not in a position to do so.
I am not looking forward to Saturday.