Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: datatypes
HIGH_VALUE (and other LONG columns) revisited
Just a quick post to ensure people don’t get caught out by a recent “trick” I saw on an AskTOM question for extracting the HIGH_VALUE out of it’s LONG storage in the dictionary to a more appropriate datatype. A reviewer (I’m sure in good…
Raw partitions?
Here’s a quirky one for you. It can happen when you are dealing with a partitioned table where the partition key is defined as RAW. To be honest, I really can’t think of a reason why you ever want a table with a raw…
PL/SQL – Don’t mix and match scope
Here’s a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure, and it’s value throughout the execution of that procedure. SQL> set serverout on SQL> declare 2…
Another day…another "use the right datatype" post
Here’s an interesting little oddity (aka bug) with scalar queries. We’ll start with a simple working example SQL> create table t1 ( c1 number ); Table created. SQL> insert into t1 values (1); 1 row created. SQL> create table t2 ( c1 int, c2…
Correcting datatypes with minimal downtime
Just a quick post here by request of an attendee of the September Office Hours. I had a demo converting the data type of a primary key without losing data and with minimal impact to the availability of the database using DBMS_REDEFINITION. You can…
Datatype conversion laziness … yet another reason
I’ve got two words to describe my thoughts on being lazy with data types …. JUST DON’T! There’s been many blog posts, videos, AskTOM questions etc over the years with numerous examples of how the optimizer gets confused, how the SQL engine gets confused…and…
Literals and VARCHAR2
We all know that comparing two VARCHAR2 fields of different lengths will be treated by Oracle as being not EQUAL: SQL> drop table T purge; Table dropped. SQL> create table T ( x varchar2(10), y varchar2(10)); Table created. SQL> insert into T values (‘abc’,’abc…
