One insert or multiple inserts?

A colleague asked me a trick* question today which I failed 🙂



INSERT INTO mytable (a)
INSERT INTO mytable (a)


INSERT INTO mytable (a)

I quickly answered:

  1. The first approach requires one extra parse;



Customising a Navigation List




A. Modify the Vertical Images List template.


#A02# = tooltip text for the hyperlink;

Customising the Vertical Image List template



B. Set User Defined Attributes.


This is how it looks in a sample application:


Auto-print a page in APEX


To get any APEX page to print when it is opened, all you need to do is add two bits to the page definition:

HTML Header

function Print(){document.body.offsetHeight;window.print()}

Page HTML Body Attribute



Truncated Mean in Oracle



SELECT AVG(mystat)
      SELECT mystat,
             NTILE(4) OVER (ORDER BY mystat) n
      FROM (SELECT mystat
            FROM mytable)
WHERE n IN (2,3);



SELECT mycat, AVG(mystat)
      SELECT mycat, mystat,
             NTILE(4) OVER (PARTITION BY mycat
                            ORDER BY mystat) n,
             COUNT(1) OVER (PARTITION BY mycat) c 
      FROM (SELECT mycat, mystat
            FROM mytable)
WHERE n IN (2,3) OR c < 4
GROUP BY mycat
ORDER BY mycat;

Lesson learned today

Comment out all “DROP TABLE” commands in my scripts.



How to Change the ORDER BY When You Can’t Modify the SQL

Is it possible to change the ORDER BY on a query, if the SQL is defined in a wrapped PL/SQL package? A colleague asked me this interesting question today – and the answer was quite simply, Yes and No.

If the SQL already has an ORDER BY, then the answer is No. Fortunately, in our case, the SQL didn’t have any ORDER BY clause:

SELECT a.addressid
FROM address a, addresstype t
WHERE a.locationid = :locationid
AND a.typeid = t.typeid
AND t.typecode = 'STREET';

The plan currently being picked by the CBO (Oracle is:

   TABLE ACCESS BY ROWID addresstype
      INDEX RANGE SCAN pkx_addresstype
   INDEX RANGE SCAN ix_address

For background, here are the relevant schema details:

TABLE address
,locationid NUMBER NOT NULL
,typeid NUMBER NOT NULL --FOREIGN KEY to addresstype

INDEX idx_address (locationid, typeid, addressid);

TABLE addresstype
,typecode VARCHAR2(30) NOT NULL

typecode is actually unique, but no constraint has been declared to the database.

Because the first step in the plan (the TABLE ACCESS on addresstype) only ever gets one row (in practice), the index range scan on ix_address causes this query to return the rows in ascending order of addressid.

The process that runs this query gets rows one at a time, does some work to “check” it against some other data, and once it finds a “match”, it stops getting more rows.

This design probably seemed like a good idea at the time, but now the business would like this process changed so that in the (rare) occurrence when more than one row would “match”, the process should favour a more recent address rather than an old one. For our purposes, “recent” may be approximated by assuming that bigger addressids are more recent than smaller addressids.

We can’t change the code without a lengthy and costly call to the application vendor; it would be better if we can change it ourselves. We cannot change the PL/SQL package in which this query is defined; so what can we do? Can we change the order in which rows are returned from this query?

The answer is yes – firstly, the query has no ORDER BY, so the order of rows is affected only by the query plan. If we change the query plan, which we can do with a stored outline, the order of rows can be changed.

So in this case, all we have to do is create a stored outline to add an INDEX_DESC hint:

SELECT /*+INDEX_DESC(a)*/ a.addressid
FROM address a, addresstype t
WHERE a.locationid = :locationid
AND a.typeid = t.typeid
AND t.typecode = 'STREET';

This causes the following plan to be used:

   TABLE ACCESS BY ROWID addresstype
      INDEX RANGE SCAN pkx_addresstype

With the stored outline, the query now returns the rows in descending order of addressid.

If you want to use this approach, it is highly dependant on being able to find a query plan that produces the ordering that you want. This may mean creating a suitable index designed strategically for this purpose, if one doesn’t exist already. Even creating indexes won’t guarantee that you’ll be able to find a plan that gets the order that you want; generally I’d say this approach will only be feasible for relatively simple queries.

Create Excel file from SQL query using Python


import cx_Oracle
from table2Excel import write_cursor_to_excel

orcl = cx_Oracle.connect('scott/tiger')
curs = orcl.cursor()
  SELECT e.ename "Employee",
         e.job "Job",
         e.hiredate "Hire Date",
         e.sal "Salary",
         e.comm "Commission",
         d.dname "Department",
  FROM   emp e, dept d
  WHERE  e.deptno = d.deptno
write_cursor_to_excel(curs, 'emp.xls', 'Employees')


Add colours to your Shuttle item



I created an item on the page with the following attributes:

Display As: Shuttle


Post Element Text:
<script type="text/javascript">
(function() {



