Category: (geen)

Oracle export schema DDL

You can use the SQL/Developer wizzard for downloading DDL to a file. But you need to remember your preferences, select a file, assign proper name, etc. etc. etc.  And it’s not complete since you cannot select jobs to export (version 4.1), as we found out the hard way…

SQL/Plus to the rescue! Attached is a file I compiled based on several information on the internet. You only have to start a SQL/Plus session and run the file (rename it from qa_export_db_ddl.txt to qa_export_db_ddl.sql) with one parameter: The schema you want to be exported. Tadaaaa!

Download qa_export_db_ddl.txt here.


I very much like Carnaval. But since the date of this varies per year because it is related to Eastern, I was looking for a way calculate it. And I found it. Below you see the formulas for Excel:

Copy the fomulas below where “B40” must be in A40  so that the formula after the vertical bar comes in B40.
Separator is vertical bar “|”; list separator  is the semicolon “;”.

Then enter a year like 2001 in cel  A1 and … voilá, Eastern is in B51.

B41|=A1 - 19*INT(A1/19)
B43|=B40 - INT(B40/4) - INT((B40-B42)/3) + 19*B41 + 15
B45|=B44-INT(B44/28)*(1-INT(B44/28)*INT(29/(B44+1))) * INT((21-B41)/11)
B46|=A1 + INT(A1/4)+B45+2-B40+INT(B40/4)
B47|=B46 - 7*INT(B46/7)
B49|=3 + INT((B48+40)/44)

(Algoritm published in Nature, 1876 April 20, vol. 13, p. 487.)


Regular Expression to find Regular Expression

For a function in the Oracle database application I work on, we had to identify rows where a certain column contained an Oracle regular expression. Oracle supports regular expressions since version 10. There is the REGEXP_LIKE condition, but also the REGEXP_* functions like REGEXP_SUBSTR.

For our problem we had to dig a little deeper into the documentation, because we would have to escape the regular expression characters in order to find if the haystack contained a regular expression.

I came up with the next code, which seems to serve very well.

select haystack
    ,sign(regexp_instr(haystack,'[]\$^*+?{}.,()|:=[-]',1,1,0,'i')) as is_regexp_yn
from testdata;

Strange thing here is we did not have to escape the special characters! The Oracle regular expression engine seems to understand what we want. Special though in this regular expression “[]\$^*+?{}.,()|:=[-]” are te location of the square close bracket “]” and the hyphen “-“. Better not fiddle too much with it the sequence or you’ll break it…

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy