sql

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

Removing duplicate rows in PostgreSQL and Oracle

Oracle trick: [sql] DELETE FROM your_table WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP BY column1, column2, column3); [/sql] Postgresql equivalent is ctid, so you can try something like [sql] 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 …

Removing duplicate rows in PostgreSQL and 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 very clean keepalive extension for Oracle SQL Developer 4+https://github.com/scristalli/SQL-Developer-4-keepalive7 forks.80 stars.9 open issues.Recent commits: Fix …

Oracle SQL Developer Keep Alive plugin 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 try to push a very long text in a varchar2, you can end up with …

ORA-01461: can bind a LONG value only for insert into a LONG column 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] could trigger a Invalid number if CUSTOMER_ID column contains invalid numbers. Why? Well…if you ask …

Oracle Invalid number ORA-01722 Read More »

Come evitare iniezioni SQL lato DB in SQLServer, Oracle, MySQL

 Come evitare iniezione SQL: lato SQL Server (SP_EXECUTESQL) In generale va evitato nel modo più assoluto la scrittura di query sql diamiche. Va evitato cioè  l’uso lato SQL Server di sp_executesql e EXEC Di seguito mostriamo come trasformare una query “dinamica” in una “statica”   [sql]CREATE PROCEDURE search_orders @custid nchar(5) = NULL, @shipname nvarchar(40) = …

Come evitare iniezioni SQL lato DB in SQLServer, Oracle, MySQL Read More »

dapper-dot-net – Simple SQL object mapper for SQL Server – Google Project Hosting

   Dapper is a single file you can drop in to your project that will extend your IDbConnection interface. […] A key feature of Dapper is performance. The following metrics show how long it takes to execute 500 SELECT statements against a DB and map the data returned to objects. The performance tests are broken …

dapper-dot-net – Simple SQL object mapper for SQL Server – Google Project Hosting Read More »

SQL Server e Python

L’integrazione tra .NET e SQL Server è data per scontata: in questo articolo esploreremo invece  come accedere a SQL Server da Python. Uno dei vantaggi di python è che è un linguaggio molto facile da imparare, con un notevole numero di librerie e un’ottima integrazione multi piattaforma. Oltre a ciò python offre un sistema di …

SQL Server e Python Read More »