All posts tagged with 'SQL'

SQL Snippets

19-Dec-13

Some useful SQL Snippets: Oracle Create a date without any formatting hassle (like to_date): SELECT DATE’2014-01-01′ FROM dual;select date’2014-01-01′ from dual; Extract values as numbers from a date object SELECT EXTRACT (YEAR FROM DATE’2013-05-06’) FROM dual; SELECT EXTRACT (MONTH FROM DATE’2013-05-06’) FROM dual; SELECT EXTRACT (DAY FROM DATE’2013-05-06’) FROM dual;select extract (year from date’2013-05-06′) from […]

Read the complete article »

Optimizing the Oracle Query Optimizer

20-Oct-10

The Oracle Query Optimizer does a great job in rewriting Queries. For example the nested subquery in SELECT C.cust_last_name, C.country_id FROM customers C WHERE EXISTS (SELECT 1 FROM sales S WHERE S.quantity_sold > 1000 AND S.cust_id = C.cust_id);SELECT C.cust_last_name, C.country_id FROM customers C WHERE EXISTS (SELECT 1 FROM sales S WHERE S.quantity_sold > 1000 and […]

Read the complete article »

Oracle NLS-Lang Settings

13-Aug-10

To use SQL*Plus Windows correctly, export NLS_LANG like so: set NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252 If you prefer the command line client SQL*Plus, export NLS_LANG like so: set NLS_LANG=GERMAN_GERMANY.WE8PC850

Read the complete article »

Using SQL*Plus without tnsnames.ora

29-Mar-10

As often, a quick reminder for me. I’m using the Oracle Instaclient on my Mac without a tnsnames.ora and i keep forgetting the connectstring syntax: sqlplus USER/PASS@//HOST:PORT/SIDsqlplus USER/PASS@//HOST:PORT/SID Extra bonus points: Through in rlwrap to get a nice commandline history and completion as used to in a standard shell: rlwrap sqlplus USER/PASS@//HOST:PORT/SIDrlwrap sqlplus USER/PASS@//HOST:PORT/SID

Read the complete article »

Fun with sql

27-Oct-08

What’s all the fuss about this SQL Injection thing? It boils down getting some malicious crafted SQL code into the SQL code of an application, destroying data or authenticate yourself without knowing any real password. xkdc has a nice explanation. The simple cases base on wrong escaped strings and the like. But as this SQL […]

Read the complete article »