Sunday, September 12, 2010

Some oracle sql pieces

1. XMLElement - set long to higher value to avoid xml string to be truncated (e.g. set long 32767).
Last week in my training, I was doing an exercise involved using XMLElement to generate xml file using XMLElement. My XML string result is always truncated to certain length and after a long search on the internet, it seems that Long parameter need to be set higher in the session.

2. Ora-01821: date format not recognized - check whether the O is set to zero
It took me an hour to find out that the date format was set to DD-M0N-YYYY. First glance seems nothing wrong but after careful examine, the M0N is actually MzeroN. If you have the same oracle error, better check on the O whether they are set to zero. The correct format should be DD-MON-YYYY.

3. Generate rows using "connect by level"
For all these years in sql programming, I have never tried to generate rows using SQL. After some search on the internet, "connect by level" was actually used to generate calender table. Yup, making a calender out of nothing. Example of using "connect by level" on creating 10 rows from 1 to 10:
Select rownum from dual connect by lever <=10