I was recently asked when presented with a query along the lines:
SQL> select * from 2 ( select x 3 from t1 4 where x = :b1 5 union all 6 select x 7 from t2 8 where x = :b1 9 ) 10 where rownum = 1 11 /
and asked – will the “ROWNUM” lead Oracle to be smart enough to stop after the “top” half of the UNION ALL if it gets row back ?
Let’s find out. First we create tablespaces for tables to be stored, and plonk a table in each.
Having NOT NULL constraints declared on columns that are genuinely not nullable not only is good practice, but can yield performance gains. Here’s a little routine that lists those columns that probably need a NOT NULL constraint. It looks at all columns and
If the latter is zero, and no constraint was found, then we print out ‘alter table … ‘ DDL for those columns that may need such a constraint defined.
Overcoming Your Oracle Data Insecurity
Webinar – Wednesday, March 30 – 10am PT
In 2016, the risks and reasons why companies should secure their sensitive data are well known (Sony, Target, and Anthem just to name a few examples). Yet, most companies have pitiful or limited data security in their Oracle databases.
I had an interesting question on AskTom today, with a nicely provided test case !!!, so I thought I’d share it here with some additional commentary.
NFS is the future, has larger bandwidth than FC, market is growing faster than FC, cheaper, easier, more flexible, cloud ready and improving faster than FC.
photo by Paul Oka
In early Oracle releases, you could have 1 trigger per type of trigger per table (eg if you had a before-row-insert trigger, you could have only 1). Now there is no limit. But that does raise the question – if I have “n” BEFORE ROW triggers on a table, in which order do they fire ? Let’s look at the following example:
SQL> drop table t1 purge; Table dropped. SQL> create table T1 ( x int, y int, z int ); Table created. SQL> create or replace 2 trigger trg1 3 before insert on t1 4 for each row 5 begin 6 :new.y := :new.x; 7 end; 8 / Trigger created. SQL> create or replace 2 trigger trg2 3 before insert on t1 4 for each row 5 begin 6 :new.z := :new.y; 7 end; 8 / Trigger created.
Seems simple enough…Copy ‘x’ into ‘y’, and then copy ‘y’ into ‘z’. So lets see what happens
This post seems timely given that yesterday was Feb 29.
In almost every case I can think of, you should be relying on native Oracle date functions to perform any kind of date arithmetic.
This is perhaps one of the very very few exceptions
SQL> set timing off SQL> create or replace 2 function is_leap_year1(y number) return boolean is 3 x date; 4 begin 5 x := to_date('2902'||y,'ddmmyyyy'); 6 return true; 7 exception 8 when others then return false; 9 end; 10 / Function created. SQL> SQL> create or replace 2 function is_leap_year2(y number) return boolean is 3 begin 4 return mod(y,4)=0 and ( mod(y,100) != 0 or mod(y,400) = 0 ); 5 end; 6 / Function created. SQL>
I observed this idiosyncracy recently when loading some lob from external files using PL/SQL:
First we’ll create a file to be loaded, and ‘wc’ tells us it is 75 bytes in size
SQL> !echo "This is line 1" > /tmp/lobfile SQL> !echo "This is line 2" >> /tmp/lobfile SQL> !echo "This is line 3" >> /tmp/lobfile SQL> !echo "This is line 4" >> /tmp/lobfile SQL> !echo "This is line 5" >> /tmp/lobfile SQL> !wc /tmp/lobfile 5 20 75 /tmp/lobfile
Then create a standard routine to load it into the database
This “problem” rates in the top 10 all time viewed questions on AskTom, and it demonstrates the importance of reading the Concepts guide with Oracle
SQL> create table CARTOONS ( name varchar2(30)); Table created. SQL> insert into CARTOONS values ('Tom & Jerry'); Enter value for jerry:
And the question comes in: “How can I insert an ampersand into my table?”
A cool new feature in 12c is the ability to make a column invisible. The concept has existed since 8i to handle things like “set unused” and function based indexes, but now it is available to developers directly.
SQL> create table T ( c1 int, c2 int ); Table created. SQL> desc T Name Null? Type ----------------------------- -------- ----------------- C1 NUMBER(38) C2 NUMBER(38) SQL> alter table T modify c2 invisible; Table altered. SQL> desc T Name Null? Type ----------------------------- -------- ----------------- C1 NUMBER(38)
So at this point… how I can tell in SQL Plus that I even have an invisible column, without querying the data dictionary.