Llayland’s Food, Oracle, and Haskell

August 14, 2008

A quick gripe

Filed under: Oracle — llayland @ 5:14 am

I really did not want the Oracle section of this blog to be my whining about all the nasty things Oracle does to me. It is just hard to find the time and motivation to write up anything positive when I run into three new (to me at least) bugs in three days. So, I’m going to gripe:

I hit the bug in metalink note 469587.1 where “Error PLS-00167 : keyword BULK is used in a wrong context” is raised with an incorrect interval literal. Of course, I had to come up with a unique twist of my own. Apparently this:

INTERVAL '.1' SECOND

is not valid and should be:

INTERVAL '0.1' SECOND

The other thing I ran into is that binds of objects tend to cause bugs. This applies to both explicit binds in dynamic sql and the implicit binds of pl/sql variables in sql statements. I’ll write up a good post sometime soon, but for now just note that the type information seems to get lost and that using the treat function around the bound variable to get the type information back seems to fix things.

August 8, 2008

Indian style vegetarian “tamales”

Filed under: Food — llayland @ 4:59 am

I haven’t made anything terribly exciting lately, so I thought I go back a few months to a dish I made that was a big hit at the office. I had been reading a couple of food blogs and two items stuck in my mind:

  • Cholkya vayli bhakri – A roti made on banana leaf
  • vaygana bajji – Smoked eggplant

The eggplants paticularly caught my eye because I had promissed some of my vegetarian coworkers that I would try to find something I could make them on my smoker. The roti appealled to me as it finally gave me a use for my apparently sterile banana trees. Unfortunately, I was extremely busy at the time and didn’t have any time to spare in the kitchen. Finally, I decided that I needed to give it a rest a do something I enjoy.  In spite of the fact that I was extremely tired, or maybe because of it, I came up with a great idea, “Why not combine the two recipes?” This saved my the effort of frying all the rotis while allowing much more flavor from the banana leaves and smoke into the dish.

I’ve rambled on enough; here is the recipe:

2 cups (less 4 tbps) all purpose flour (Maida)
4 tbsp corn starch
1 13.5oz can of cream of coconut
——————–
mix into a loose batter

good amount of black mustard seed
good amount of dried red pepper
1 black cardomon pod
1 clove garlic
2 tsp tomato curry
salt
———————————
mash into a paste

2 large eggplants
——————————
smoke low and slow (225 degrees) until softened, then blacken over direct flame
remove skin and some seeds
smoke cleaned eggplant to remove excess moisture
mash lightly with above paste, should be very chunky

cut squares of fresh banana leaf 4-5 inches
scoop batter onto each leaf. just scoop it in the middle and spread lightly- should be about 2/3 covered (round). place a dollop of eggplant mixture in the batter round, about 2/3 of the way across. pick up the near edge of the leaf and fold it over so near end of the round covers the eggplant mixture and overshoots the other other end a little
press down lightly and pull towards you to create a tube of filling.
fold that tube forward over the two edges (should be roughly together)
smoke low and slow for a couple of hours until the batter sets
increase temperature to 400 degrees for about 20 minutes to finish them off
A note on smoking:

If you don’t have a smoker, you can use a  grill  just make sure to keep the coals to one side and the food to the other so you get indirect heat. I’d recommend using larger chunks of wood, but chips will work as well. I believe I used  hickory, pecan, and cherry which gives a nice blended flavor.  That would have been overkill just for the tamales, so I had the other half of the smoker filled with mojo marinated chicken quarters.  Plain old oak and most other woods would have been fine as well.  I’d recommend against mesquite as it could be overpowering
2 cups (less 4 tbps) all purpose flour (Maida)
4 tbsp corn starch
1 13.5oz can of cream of coconut
——————–
mix into a loose batter

good amount of black mustard seed
good amount of dried red pepper
1 black cardomon pod
1 clove garlic
2 tsp tomato curry
salt
———————————
mash into a paste

2 large eggplants
——————————
smoke low and slow (225 degrees) until softened, then blacken over direct flame
remove skin and some seeds
smoke cleaned eggplant to remove excess moisture
mash lightly with above paste, should be very chunky

cut squares of fresh banana leaf 4-5 inches
scoop batter onto each leaf. just scoop it in the middle and spread lightly- should be about 2/3 covered (round). place a dollop of eggplant mixture in the batter round, about 2/3 of the way across. pick up the near edge of the leaf and fold it over so near end of the round covers the eggplant mixture and overshoots the other other end a little
press down lightly and pull towards you to create a tube of filling.
fold that tube forward over the two edges (should be roughly together)
smoke low and slow for a couple of hours until the batter sets
increase temperature to 400 degrees for about 20 minutes to finish them off

August 7, 2008

Default precision fractional for timestamp datatype is 6. Huh?

Filed under: Oracle — llayland @ 6:09 am

I’ve always read that the default precision for timestamps was 6.

From “Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-03

TIMESTAMP Datatype

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values. Specify the TIMESTAMP datatype as follows:

TIMESTAMP [(fractional_seconds_precision)]

where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value can be a number in the range 0 to 9. The default is 6.

I found a fun little case where the default precision is 0. It was the end of a long day when I did something similar to this:

SQL> create or replace type timestamp_tab as table of timestamp;
2 /

Type created.

SQL>
SQL> create or replace function get_ts return timestamp_tab
2 is
3 retval timestamp_tab;
4 begin
5 select
6 to_timestamp('20080102','YYYYMMDD')
7 + interval '0.1' second * (rownum - 2)
8 bulk collect into retval
9 from dual
10 connect by level <= 3;
11
12 return retval;
13 end get_ts;
14 /

Function created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

Strange, I’ve lost my fractional seconds. Maybe I need to explicitly set the precision in my type:


SQL> create or replace type timestamp_tab as table of timestamp(4);
2 /Type created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

No dice. Let’s go back to basics to make sure I’m not just braindead at this point:


SQL> begin
2 dbms_output.put_line(
3 to_timestamp('20080102','YYYYMMDD') + interval '0.1' second * -1
4 );
5 end;
6 /
2008-01-01 23:59:59.900000000PL/SQL procedure successfully completed.

SQL>
SQL> begin
2 for rec in (
3 select to_timestamp('20080102','YYYYMMDD')
4 + interval '0.1' second * -1 ts
5 from dual
6 ) loop
7 dbms_output.put_line(rec.ts);
8 end loop;
9 end;
10 /
2008-01-01 23:59:59.900000000

PL/SQL procedure successfully completed.

So, I do normally get six digits by default.  Maybe it is a bulk collect issue?


SQL> create or replace function get_ts2 return timestamp_tab
2 is
3 retval timestamp_tab := timestamp_tab();
4 begin
5 retval.extend(3);
6
7 for rec in (
8 select rownum n,
9 to_timestamp('20080102','YYYYMMDD')
10 + interval '0.1' second * (rownum - 2) ts
11 from dual
12 connect by level <= 3
13 ) loop
14 retval(rec.n) := rec.ts;
15 end loop;
16 return retval;
17 end get_ts2;
18 /Function created.

SQL>
SQL> begin
2 for rec in (select * from table(get_ts2)) loop
3 dbms_output.put_line(rec.column_value);
4 dbms_output.put_line(to_char(rec.column_value, 'FF'));
5 end loop;
6 end ;
7 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000

PL/SQL procedure successfully completed.

Nope.  Maybe an explicit cursor?


SQL>
SQL> declare
2 cursor ts_cur is
3 select * from table(get_ts);
4 begin
5 for rec in ts_cur loop
6 dbms_output.put_line(rec.column_value);
7 dbms_output.put_line(to_char(rec.column_value, 'FF'));
8 end loop;
9 end ;
10 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000PL/SQL procedure successfully completed.

Nope.  Hmm, I vaguely remember that you can declare the return type of a cursor but I’ve never had to do it before.


SQL>
SQL> declare
2 type ts_rec is record(ts timestamp);
3 cursor ts_cur return ts_rec is
4 select column_value from table(get_ts);
5 begin
6 for rec in ts_cur loop
7 dbms_output.put_line(rec.ts);
8 dbms_output.put_line(to_char(rec.ts, 'FF'));
9 end loop;
10 end ;
11 /
2008-01-01 23:59:59.900000
900000000
2008-01-02 00:00:00.000000
000000000
2008-01-02 00:00:00.100000
100000000PL/SQL procedure successfully completed.

Aha, so the default precision for a timestamp when returned from a table function into a cursor is 0. Just for grins:


SQL>
SQL> declare
2 type ts_rec is record(ts timestamp(0));
3 cursor ts_cur return ts_rec is
4 select column_value from table(get_ts);
5 begin
6 for rec in ts_cur loop
7 dbms_output.put_line(rec.ts);
8 dbms_output.put_line(to_char(rec.ts, 'FF'));
9 end loop;
10 end ;
11 /
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000
2008-01-02 00:00:00.
000000000PL/SQL procedure successfully completed.

So, problem solved. Hopefully this will save someone out there some time and head scratching.

Create a free website or blog at WordPress.com.