Today I came across a link on how to solve sudoku puzzles using sql. It is written in T-SQL in SQLServer2000, but the code is published, so there is no reason for it NOT to be converted to PL/SQL so that it can be used in an Oracle database too ;-).
Read moreCategory Archives: SQL
Handy date functions…
I came across the need to define the first day of next month based on the current systemdate. I can do this using the following code: add_months(to_date(’01’||to_char(sysdate,’MMYYYY’),’DDMMYYYY’),1) But the I remembered that there was a post on the oracledeveloper.nl forum with similar date functions. First day of the week: select trunc(sysdate,’IW’) the_date from dual; First day […]
Read moreComments
If you want to temporarily disable your code you normally comment this piece out. I generally use either — (line comment) or /* */ (block comment). The code below: SELECT * FROM emp WHERE 1=1 AND empno < 1000 becomes either: --SELECT * -- FROM emp -- WHERE 1=1 -- AND empno < 1000 or: […]
Read morePronunciation
Eddie Awad has a couple of nice posts on his blog on how to pronounce the terms used in PL/SQL. The first one is Char or Car? The second one is see-quel wins, but what about the others? On the second one he has a poll on pronunciation of a couple of terms. The nicest […]
Read moreShrinking tablespaces
As you might know, Oracle uses tablespaces to store the tables and the data within them. If you drop tables and/or users, the tablespace is not automagically shrunk to it’s minimal size. While playing around with my local database, I imported a lot of (big) tables. I wanted to reload a schema, but before I […]
Read moreSimple comparison SQL
I am comparing tables in two different schemas. Since I am doing the same thing over and over again I created a small script which might be useful to you too. ACCEPT db1 CHAR prompt ‘owner 1 :’ ACCEPT db2 CHAR prompt ‘owner 2 :’ ACCEPT tabname CHAR prompt ‘tablename :’ PROMPT PROMPT &db1..&tabname minus […]
Read more