SQLite Oracle Compatibility Layer
It aims to provide a minimal compatibility with Oracle. The need was having some regular expression functions, and I do not like to work with risky C code, like this university project did
Tag Archives: oracle
Categories
Archive
- September 2019
- August 2019
- July 2019
- June 2019
- May 2019
- April 2019
- March 2019
- February 2019
- January 2019
- December 2018
- November 2018
- October 2018
- September 2018
- August 2018
- July 2018
- June 2018
- May 2018
- April 2018
- March 2018
- February 2018
- November 2017
- September 2017
- July 2017
- June 2017
- March 2017
- February 2017
- January 2017
- December 2016
- November 2016
- September 2016
- July 2016
- June 2016
- April 2016
- March 2016
- February 2016
- December 2015
- November 2015
- October 2015
- September 2015
- August 2015
- July 2015
- June 2015
- April 2015
- March 2015
- February 2015
- January 2015
- November 2014
- October 2014
- September 2014
- August 2014
- July 2014
- June 2014
- May 2014
- April 2014
- October 2013
- September 2013
- August 2013
- July 2013
- June 2013
- May 2013
- April 2013
- March 2013
- February 2013
- January 2013
- December 2012
- November 2012
- October 2012
- September 2012
- August 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010
- October 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- February 2010
- January 2010
- December 2009
- November 2009
- October 2009
- September 2009
- August 2009
- July 2009
- June 2009
- May 2009
- April 2009
- March 2009
- February 2009
- January 2009
- December 2008
- November 2008
- October 2008
- September 2008
- August 2008
- July 2008
- June 2008
- May 2008
- April 2008
- March 2008
- February 2008
- January 2008
- December 2007
- November 2007
- October 2007
- September 2007
- August 2007
- July 2007
- June 2007
- May 2007
- April 2007
- March 2007
- February 2007
- January 2007
- December 2006
- November 2006
- October 2006
- September 2006
- August 2006
- July 2006
- June 2006
- May 2006
- January 2006
- December 2005
- November 2005
- September 2005
- November 2004
- November 2000
- July 1999
Removing duplicate rows in PostgreSQL, Oracle and SQLite
Sometimes you need to remove nasty duplicate on a table, based on a subset of the column. Oracle trick:
|
1 2 3 4 5 |
DELETE FROM your_table WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP BY column1, column2, column3); |
Postgresql equivalent is ctid, so you can try something like
|
1 2 3 4 5 6 7 8 |
delete from mytable where exists (select 1 from mytable t2 where t2.name = mytable.name and t2.address = mytable.address and t2.zip = mytable.zip and t2.ctid > mytable.ctid ); |
SQLite demo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create table dup_table (column1 text, column2 text, column3 text); insert into dup_table values('1','2','3'); insert into dup_table values('1','2','3'); insert into dup_table values('1','3','3'); DELETE FROM dup_table WHERE rowid not in (SELECT MIN(rowid) FROM dup_table GROUP BY column1, column2); select count(*) from dup_table; |
Also take a look at… Read more ›
Italian Accent Oracle fix
In Italian language we have accent words like àèéìòù They are quite important because for instance “is” is spelled è whereas “and” is spelled e Accent are bad guys even today: if you copy them from MS-Word inside an Oracle… Read more ›
Oracle SQL Developer Keep Alive plugin
On some environment, on some customers, Oracle connections are drop after some idle time (i.e. 2 minutes). I have found a small plugin for Sql Developer to solve this issue: it is very handy and logs its usage. Code is also… Read more ›
Oracle Auto increment trigger HOWTO
Oracle SQL Developer is full of nice feature, damned by a overwhelming options pane, like the one I will describe to you right now. Even if Oracle databases (<12) does not support auto increment, you can easily ask to your… Read more ›
Oracle Database Sync
Oracle SQL Developer is full of nice feature, damned by a overwhelming options pane, like the one I will describe to you right now. I will show here a very fast way of comparing different database and auto-generate migration script.… Read more ›
ORA-01461: can bind a LONG value only for insert into a LONG column
Okey, you know Oracle. A very good database, a very old database, a very solid rock. Not famous for its error messages. I have already stumbled upon a misleading error on the old rock. This error anyway is incredible. If you… Read more ›
Debian perfect work environment
Working in a big company, my work laptop came with MS-Windows7 Enterprise installed. But as you imagine, Unix is my preferred desktop environment. So let’s how to configure a perfect Debian Linux for a old wolf consultant like me. You… Read more ›
Oracle Invalid number ORA-01722
I stumbled upon a very brain-f**k error on Oracle 10g on these days. Context: the following query [sql]SELECT * FROM ( SELECT TO_NUMBER(CUSTOMER_ID) AS SNDG FROM BAD_CODES_TABLE WHERE AND I_LIKE=UPPER(‘STATIC_CONDITION’) AND CUSTOMER_ID NOT LIKE ‘%P%’ ) S WHERE TO_NUMBER(S.SNDG) >2000[/sql]… Read more ›
Twitter il gradasso
C’è una cosa fantastica di Internet: se qualsiasi società prova a fare la furba, inserendo delle righe scritte in piccolo quando revisiona la sua API da 1.0 a 1.1, state certi che ci sarà almeno una persona che si leggerà… Read more ›