The Oracle

What If Saturated Fat Is Not the Problem?

2008-10-07T05:43:00.000-07:00

An article in dLife, written by Richard Feinman, PhD:

Here’s an idea to chew on: The carbs in your diet tell your body what to do with the fat you eat, so it’s the type and amount of carbohydrates that matter when it comes to your weight and health.

Virtually every bit of health information today includes the advice to avoid saturated fat — the so-called evil stuff that lurks in animal foods like steak and eggs. The basis for this recommendation is that research has shown a correlation between saturated fat intake and total cholesterol and LDL (“bad cholesterol”). The problem with these studies is that the effects are not large, there is wide variation among individuals and, in most of these studies, the predicted benefit in incidence of cardiovascular disease did not materialize. In addition, we now know much more about risk factors for cardiovascular disease (CVD) beyond LDL. No assessment of CVD risk can be made without considering HDL (“good cholesterol”), triglycerides, and the size of the LDL particle. Plenty of research shows that these markers can worsen when people reduce their intake of saturated fat and that they can improve by reducing the intake of carbohydrates.

You don’t have to be a medical researcher to recognize that this is a politically charged issue. The thing that is missing for the public is an impartial evaluation of all the data on saturated fat. My personal opinion is that there is much contradictory data and a recent review of the situation suggests that there is not sufficient evidence to make any recommendations.

There is a sense that, in the absence of definitive evidence, lowering saturated fat will at least do no harm. This is not right. The problem for people with diabetes is what happens when saturated fat is replaced with carbohydrate, and research has repeatedly shown that this may actually be harmful. Consider that, according to the Centers for Disease Control and Prevention, during the onset of the current epidemic of obesity and diabetes, almost all of the increase in calories in the American diet has been due to carbohydrate. The percent of total fat and saturated fat in our diet decreased. In men, the absolute amount of saturated fat consumed decreased by 14 percent!

One of the most striking reasons to doubt the across-the-board proscriptions against saturated fat is the report from the large scale Framingham study in the Journal of the American Medical Association, titled “Inverse association of dietary fat with development of ischemic stroke in men.” You read that right: The more saturated fat in the diet, the lower the incidence of stroke.

Perhaps the most compelling research was published in a 2004 issue of the American Journal of Clinical Nutrition by researchers from the Harvard School of Public Health. Their study showed that, in postmenopausal women with heart disease, a higher saturated fat intake was associated with less narrowing of the coronary artery and a reduced progression of disease. Even with similar levels of LDL cholesterol, women with lower saturated fat intake had much higher rates of disease progression. Higher saturated fat intake was also associated with higher HDL (the “good” cholesterol) and lower triglycerides.

If saturated fat isn’t the problem, what is?
In this study, in which greater saturated fat intake was associated with less progression of coronary atherosclerosis, carbohydrate intake was associated with a greater progression. Carbohydrate, through its effect on insulin, is the key player. Insulin not only sweeps up glucose from the blood but it also plays air traffic controller, making the call as to whether that glucose is turned into fat or is used for energy. Most importantly, insulin determines what happens to dietary fat — whether it gets stored or oxidized for fuel. In fact, the fat profile in the blood (cholesterol and triglycerides) is not strongly tied to diet.

A recent study by Jeff Volek at the University of Connecticut compared low-carbohydrate and low-fat diets. Even though the low-carbohydrate diet had three times as much saturated fat as the low-fat diet, levels of unhealthy fats in the blood were lower in the low-carbohydrate group. How is that possible? That is what metabolism does.

What is the best diet?
We don’t know the ideal diet composition. We do know that saturated fat, unlike trans-fat, is a normal part of body chemistry and extreme avoidance is not justified by current scientific data. Removing some saturated fat to reduce calories is good, but adding back carbs appears to be deleterious. It appears that healthy, carbohydrate restriction will trump the effects of any kind of fat. For a person with diabetes, blood glucose must be the first consideration. If you have relatively tight blood sugar control, the amount of saturated fat you eat may be a non-issue. You can do what we did before the diabetes-obesity epidemic: regulate your intake by your taste and your natural appetite. No one ever did want to eat a pound of bacon.

Sources:
1. Food and Nutrition Board: Macronutrients. In: Dietary reference intake: National Academies Press; 2005, p.484.

2. JB German, CJ Dillard: Saturated fats: what dietary intake? Am J Clin Nutr 2004, 80:550-559.

3. MW Gillman, et al. : Inverse association of dietary fat with development of ischemic stroke in men. JAMA 1997, 278:2145-150.

4. D Mozaffarian, EB Rimm, DM Herrington: Dietary fats, carbohydrate, and progression of coronary atherosclerosis in postmenopausal women. Am J Clin Nutr 2004, 80:1175-1184.

5. JS Volek, et al. A hypocaloric, very low carbohydrate, ketogenic diet results in a greater reduction in the percent and absolute amount of plasma triglyceride saturated fatty acids compared to a low fat diet. NAASO, Boston, MA, October, 2006.

ADA Revises Nutrition Recommendation for those with Diabetes

2008-10-07T05:42:00.000-07:00

I'm still reading through the just released Nutrition Recommendations and Interventions for Diabetes - the 2008 position statement from the American Diabetes Association regarding dietary recommendations for those at risk for or diagnosed with diabetes.

While I finish reading the actual documents and write up what I think of the paper, here are links to what others are saying today:

Jimmy Moore - New 2008 ADA Recommendations Partially Acknowledge Low-Carb Diets

Adam Campbell - Apparently Hell Just Froze Over

Dr. Mary Vernon - HAS THE AMERICAN DIABETES ASSOCIATION SPARKED YET ANOTHER ATKINS REVOLUTION?

Low-Carb Ketogenic Diet - Greater Weight Loss and More...

2008-10-07T05:41:00.000-07:00

A neat little study was published in the January 2008 American Journal of Clinical Nutrition - Effects of a high-protein ketogenic diet on hunger, appetite, and weight loss in obese men feeding ad libitum.

In the study, researchers followed seventeen obese men, confined to a metabolic ward, for a month while they consumed either a low-carb ketogenic diet or a moderate carbohydrate diet on an ad libitum basis (eat as much as desired from foods allowed).

Over the course of the study, the protein intake of both dietary regiments was fixed to provide 30% of calories; carbohydrate was restricted to 4% of total calories in the men consuming the low-carb diet and 22% in the men consuming the moderate carbohydrate diet; and fat intake rounded out the calories in each diet with no specific limitation on fat consumption in either.

All meals were prepared and provided as requested by the men and they were allowed to eat whatever they wanted of the allowed foods in each meal with no restrictions other than on their carbohydrate options. The men on the low-carb diet consumed less calories each day on their own and reported higher feelings of satiety while on the diet.

On average, the difference in carbohydrate intake was great - the men on the low-carb diet consumed just 22g of carbohydrate each day, while those on the moderate carbohydrate diet consumed 170g each day. Both levels of intake were significant reductions from baseline, where the men averaged 396g of carbohydrate each day.

Weight loss was greater for the men following the low-carb diet, who averaged a weight loss of 6.34kg (13.95-pounds) compared to the moderate carb diet averaging a loss of 4.35kg (9.57-pounds). Calorie differences between the two groups do not fully explain the greater weight loss in the men consuming the low-carb diet since they ate about 1731-calories a day compared to the men consuming the moderate carb diet consuming about 1898-calories a day. This difference - about 167-calories a day - translates to a month long difference of 5016-calories, or 1.43-pounds....yet the difference between the two groups was 4.38-pounds greater weight loss in those on the low-carb diet.

Digging deeper into the published data we also find that the men on the low-carb diet experienced statistically significant improvements in blood glucose, insulin and HOMA-IR, as well as favorable improvements in their cholesterol levels with a reduction in total cholesterol and LDL, an increase in HDL and a significant reduction in triglycerides.

All of these favorable changes occured while the men consumed a dietary fat intake similar to that at baseline. Where at baseline they consumed an average of 126g of total fat with 43.8g of saturated fat, their dietary intake while following low-carb didn't change much - they averaged 129g of total fat on the low-carb diet and 46.3g of saturated fat. This basically highlights that modifying one's diet to be low-carb does not mean one suddenly increases dietary fat consumption significantly - in this trial, dietary fat was pretty much the same compared to baseline.

So, with this study, we have one more to add to the pile that supports carbohydrate restriction for satiety, ad libitum-spontaneous calorie reduction, weight loss, improvements to glucose, insulin and insulin resistance, along with favorable improvements (although not statistically different from the moderate carbohydrate diet) to lipids.

Low-GI Diet for 1-year Suggests Improvements for T2DM?

2008-10-07T05:40:00.000-07:00

As I was browsing through the January issue of the American Journal of Clinical Nutrition this weekend, a perspective written by John Miles caught my attention. In his article, A role for the glycemic index in preventing or treating diabetes, he wrote, "Elsewhere in this issue of the Journal, Wolever et al (7) report the results of the Canadian Trial of Carbohydrates in Diabetes (CCD). Patients with well-controlled type 2 diabetes who were treated with diet alone were randomly assigned to receive either a high-GI diet, a low-GI diet, or a low-carbohydrate, high-monounsaturated fat diet for 1 y.

The study was carefully conducted and of longer duration than many earlier trials. The investigators found no weight loss and a small increase in glycated hemoglobin (HbA1c) in all 3 groups. This increase in HbA1c is what one would expect with no intervention (8). The fact that glucose concentrations 2 h after an oral glucose challenge were significantly lower in persons who had followed the low-GI diet for 1 y than in those who followed the other 2 diets for 1 y suggests improvement in either insulin sensitivity or insulin secretion (or improvements in both)."

Sounds like a compelling study, so intrigued, I clicked open the study mentioned, The Canadian Trial of Carbohydrates in Diabetes (CCD), a 1-y controlled trial of low-glycemic-index dietary carbohydrate in type 2 diabetes: no effect on glycated hemoglobin but reduction in C-reactive protein, and read the abstract.

In it, researchers concluded "In subjects with T2DM managed by diet alone with optimal glycemic control, long-term HbA1c was not affected by altering the GI or the amount of dietary carbohydrate. Differences in total:HDL cholesterol among diets had disappeared by 6 mo. However, because of sustained reductions in postprandial glucose and CRP, a low-GI diet may be preferred for the dietary management of T2DM."

Here I was even more intrigued - a study trial comparing three different dietary approaches, and one low-carb for a year?

But I wondered, how was it that the low-carb diet didn't do as well as other studies would suggest it should have?

Wanting to know this, I opened the full-text to understand how it was possible that greater improvement was not found in the "low-carbohydrate" subjects and why HbA1c didn't remain stable or improve in the course of one year with either low-GI or low-carbohydrate diets. Previous study data published by others would suggest that HbA1c would at least remain stable with low-carb, no?

Well, it took no more than five minutes to fully see why things turned out as they did - the "low-carbohydrate" diet was not a low-carbohydrate diet afterall - at baseline the subjects assigned the low-carb diet ate an average of 210g of carbohydrate each day and during the low-carb diet consumed an average of 199g of carbohydrate each day.

Hello? In whose fantasy world is 199g of carbohydrate each day a low-carb diet?

Ah, but I digress...

While the researchers took pains to measure many risk factors, at the end of the year, the subjects in every group experienced progression of their diabetes risk factors - there simply was no improvement to laud in this trial, no matter how you twist the data.

What's absolutely disappointing in how the findings are presented is that the researchers honed in on two measures of improvement - CRP and post-prandial glucose - to the exclusion of significant declines in other measures that are critically important for those with type II diabetes.

Where do I even begin?

Weight remained fairly stable in all three groups, with only the low-GI group actually gaining some weight, despite no meaningful difference in calorie intake from baseline through one year on the low-GI diet.

Worse though is the lack of critical thought around the marked and significant increase in waist circumference in all three diet groups.

The high-GI group started with a waist circumference of 99.1cm - it increased over the year to 103.1cm (+1.6 inches) - this despite stable weight on the scale (84.4kg at baseline; 84.3kg after 1-year on the diet).
The low-GI group started with a waist circumference of 98.3cm - it increase over the course of the year to 104.9cm (+2.6 inches). They also experienced a weight gain, going from 81.1kg at baseline to 83.9kg at the end of the study (+6.2-pounds gained).
The supposed "low-carbohydrate" group - eating 199g carbohydrate on average - started with a waist circumference of 98.6cm - it increased over the study period to 103.1cm (+1.8-inches) - like the high-GI group, this increase was despite a stable scale weight...they started at 84.7kg and ended the year at 84.3kg.

If that wasn't bad enough - all three groups experienced increases in their HbA1c too.

Those consuming a high-GI diet saw a rise from 6.2 to 6.34; low-GI saw a rise from 6.2 to 6.34; and those on the supposed low-carb diet rose from 6.1 to 6.35.

Over time the researchers reported that this rise was statistically significant - and I'd say clinically significant too!

Now with just these risk measures, you'd think there was enough to maybe, just maybe, inspire the researchers to challenge the efficacy of any of the above diets for those with type II diabetes. Maybe even say that perhaps the level of carbohydrate - despite quality or glycemic index or load improvements - matters; that simply modifying the type of carbohydrate in the diet does NOTHING for glycemic control and if carbohydrate is consumed habitually at levels seen in this study, perhaps someone with type II diabetes should expect a continued progression of their disease?

But, ya know what? They didn't even consider that. And the above problems were not all they reported either.

Let's see what else was reported in the data:

Total cholesterol didn't do much in any of the groups; LDL didn't change significantly in any group....HDL however declined in the low-GI group, but rose in the high-GI and supposed low-carb groups.

Triglycerides fell slightly in the high-GI and supposed low-carb groups, but rose in the low-GI group.

Two more markers of potential health risks found to be problematic in the low-GI diet over a year - and the researchers even noted it in the full-text - "With the low-GI diet, mean triacylglycerol was 12% higher, HDL was 4% lower, and the ratio of apoB to apoA was 4% higher than with the low-CHO diet

But a 12% increase in triglycerides and a 4% drop in HDL didn't set off any alarm bells either.

Neither did the fact the low-GI group saw an increase in their fasting plasma glucose over the year, which was also noted and basically disregarded.

With regards to the main focus in the abstract, C-Reactive Protein, the researchers did find a significant difference between the low-GI diet and the high-GI diet, but also noted that between the low-GI diet and the supposed "low-carb" diet there was no significant difference.

Yet, they chose to focus on the low-GI diet as better for a type II diabetic, despite the fact it led to

weight gain
waist circumference increase
increase in HbA1c
increase in fasting plasma glucose
a marked rise in triglycerides
and a decline in HDL

The conclusion here speaks volumes when taken in context to the carbohydrate intake in each group, "In subjects with T2DM managed by diet alone with optimal glycemic control, long-term HbA1c was not affected by altering the GI or the amount of dietary carbohydrate."

Better stated might be, with no meaningful change in absolute carbohydrate consumption, even with improvement in quality of carbohydrate - it is likely a type II diabetic will experience progressive decline in glycemic control along with other declines in risk factors over a year.

The data is published right in the full-text - the glycemic index as a means to reverse or prevent diabetes is no solution.

In this study, those who followed the low-GI diet had the worst overall outcome - they gained weight, increased waist circumference, saw triglycerides rise while HDL fell, and experienced a decline in glycemic control as evidenced by their increased HbA1c.

Yet you wouldn't know it from the abstract which focuses instead on "sustained reductions in postprandial glucose and CRP" and then concludes that "a low-GI diet may be preferred for the dietary management of T2DM."

And then back to the article from John Miles, who said this study "suggests improvements" in those who followed the low-GI diet for one year. Who's he kidding?

ADA Says Low-Carb Okay for Weight Loss, So What?

2008-10-07T05:38:00.000-07:00

An article published today in Diabetes Health, ADA Now Supports Low-Carb Diets, reminded me that I have not yet posted my thoughts on the updated guidelines for Medical Nutrition Therapy (MNT) recently issued by the American Diabetes Association (ADA). This will probably be longer than usual, so bear with me!

On December 28, 2007, the ADA issued a press release to highlight the publication of and changes within their clinical practice recommendations, better known as the Standards of Care in Diabetes.

Each year the guidelines are updated and this year was no exception - as noted in the press release, "Until now, the ADA did not recommend low carbohydrate diets because of lack of sufficient scientific evidence supporting their safety and effectiveness. The 2008 Recommendations include a statement recognizing the increasing evidence that weight-loss plans that restrict carbohydrate or fat calorie intake are equally effective for reducing weight in the short term (up to one year). The "Standards of Medical Care in Diabetes--2008" document reviews the growing evidence for the effectiveness of either approach to weight loss. In addition, there is now evidence that the most important determinant of weight loss is not the composition of the diet, but whether the person can stick with it, and that some individuals are more likely to adhere to a low carbohydrate diet while others may find a low fat calorie-restricted diet easier to follow."

There are two main issues I'll look at in the above statement today:

1. that low-carbohydrate diets are as effective as low-fat calorie restricted diets for weight loss for up to one-year

2. that composition of diet is less important than whether a person can stick with the dietary approach for weight loss.

In order to fully understand exactly what the ADA is saying with regard to the first issue, we need to return to the August 2006 issue of Diabetes Care, where an updated consensus statement was published, Management of Hyperglycemia in Type 2 Diabetes: A Consensus Algorithm for the Initiation and Adjustment of Therapy: A consensus statement from the American Diabetes Association [ADA] and the European Association for the Study of Diabetes [EASD]

As I noted in my review of that consensus statement "Rather than question the dietary recommendations, or explore emerging data supportive of dietary interventions that are different from the recommendations, the statement instead concludes that "the limited long-term success of lifestyle programs to maintain glycemic goals in patients with type 2 diabetes suggests that a large majority of patients will require the addition of medications over the course of their diabetes."

The final sentence in the section discussing medications, which followed the section on lifestyle intervention, sets the stage for what is to come, "addition of medications is the rule, not the exception, if treatment goals are to be met over time."

In August 2006 the ADA, along with the EASD, threw up their hands and decided that dietary and lifestyle intervention was futile, therefore the only logical place to go was intensive pharmaceutical intervention at diagnosis.

The authors wrote, in the paper's conclusion, "We now understand that much of the morbidity associated with long-term complications can be substantially reduced with interventions that achieve glucose levels close to the nondiabetic range. Although new classes of medications, and numerous combinations, have been demonstrated to lower glycemia, current-day management has failed to achieve and maintain the glycemic levels most likely to provide optimal health care status for people with diabetes."

On this the ADA remains steadfast - pharmacological intervention is the first step with lifestyle intervention upon diagnosis. The freely available full-text of the Management of Hyperglycemia in Type 2 Diabetes: A Consensus Algorithm for the Initiation and Adjustment of Therapy clearly continues with the August 2006 consensus that lifestyle intervention for those diagnosed with diabetes will not work, therefore medication must be initiated upon diagnosis.

Yet we find the ADA falling all over itself to tout its position change for weight loss - that now a low-carbohydrate diet is considered as effective as a low-fat calorie restricted diet for weight loss? And somehow we're supposed to be jumping for joy that they made this change?

If we take the entire package of documents published in the Diabetes Care Supplement, we cannot reach any conclusion other than the ADA has made up its mind and is not going to review the evidence. They may concede that a low-carbohydrate diet can help with some loss of weight, but nothing else - and even that carries the caveat that one must be intensely monitored if they do decide to follow a low-carb diet.

But, going back to the first point - the concession that low-carbohydrate diets and low-fat calorie restricted diets are both effective for weight loss over the short-term.

Quite frankly this statement by the ADA is meaningless when we consider the full context of their position because they hold that "current-day management has failed to achieve and maintain the glycemic levels most likely to provide optimal health care status for people with diabetes."

While many lauding the change as a step in the right direction for the ADA, I'm not impressed, nor convinced - if anything, the ADA only confirmed what they've already said previously.

We only need to go back to the publication of a 22-month study, in which diabetic subjects were found to have significant health improvements following a low-carbohydrate diet, to read the ADA reaction in an article at WebMD - "While agreeing that carbohydrate restriction helps people with type 2 diabetes control their blood sugar, ADA spokesman Nathaniel G. Clark, MD, tells WebMD that the ADA does not recommend very low-carb diets because patients find them too restrictive. "We want to promote a diet that people can live with long-term," says Clark, who is vice president of clinical affairs and youth strategies for the ADA. "People who go on very low carbohydrate diets generally aren't able to stick with them for long periods of time."

Which brings us to issue two above - diet composition does not matter as much as a diet one can follow, a theme the ADA has been hot and heavy on for at least two years now.

Let's review the sentence in the ADA press release carefully, "In addition, there is now evidence that the most important determinant of weight loss is not the composition of the diet, but whether the person can stick with it, and that some individuals are more likely to adhere to a low carbohydrate diet while others may find a low fat calorie-restricted diet easier to follow."

Evidence? What evidence?

The Standards of Medical Care in Diabetes 2008 includes this sentence, "Although numerous studies have attempted to identify the optimal mix of macronutrients for meal plans of people with diabetes, it is unlikely that one such combination of macronutrients exists. The best mix of carbohydrate, protein, and fat appears to vary depending on individual circumstances. For those individuals seeking guidance on macronutrient distribution in healthy adults, the Dietary Reference Intakes (DRIs) may be helpful;" referencing the IOM documents published back in 2002.

The Nutrition Recommendations and Interventions for Diabetes: A Position Statement of the American Diabetes Association 2008 includes "Nutrition counseling should be sensitive to the personal needs, willingness to change, and ability to make changes of the individual with pre-diabetes or diabetes. (E)"

Note with that the letter "E" assigned to it, classing it as "expert opinion" - so again, where is the evidence? Each study referenced dates between 1997 and 2004 - so what exactly is the new evidence alluded to in the updated documents?

Oh, that's right, there is NONE...this is simply an opinion and has already been stated numerous times before.

I've said it before, "evidence versus sophistry; with just enough opinion thrown in to ensure glycemic control remains elusive..."

The ADA refuses to acknowledge that diabetics deserve clear statements about how to achieve normal blood sugars, and instead continues headlong on this path that they somehow deserve to eat like anyone else in the population and can mediate the effects of carbohydrate-rich food with medications.

:::sigh:::

So yeah, the ADA now says one can try a low-carbohydrate diet for weight loss, for the short-term (up to 1 year) and that some will somehow manage to follow such a diet. But let's not forget, if you do decide to follow a low-carbohydrate diet, you're also going to be subjected to much more intense monitoring than your low-fat calorie restricted peer and you're left with no advice other than the same-old same-old once your year is up.

Then what?

The failed ADA diet?

Lifelong medication with continued stepped-up pharmaceutical requirements with each passing year until you're dependent upon insulin injections?

The ADA, even with this new position that a low-carbohydrate diet may be used for up to one year for weight loss, still continues to fail in their mission - to prevent and cure diabetes and to improve the lives of all people affected by diabetes - because they refuse to actually review the hard data available; and instead continue in this sophistry that dietary recommendations need to be based upon what one wants to eat rather than what one should eat based upon metabolic, hormonal and physiological facts.

Animal products are ‘whole foods,' too

2008-10-07T05:37:00.000-07:00

Dana Carpender's latest - I couldn't have said it better myself!

Animal products are 'whole foods,' too
Dana Carpender

The nutritional buzz phrase is 'whole foods.' This is encouraging. I've been watching the nutrition scene long enough to remember when people who insisted that whole-grain bread was more nutritious than enriched bread were scorned as 'food faddists.'

But the admonitions to eat whole foods seem to apply only to grains, fruits and vegetables. Officialdom still recommends discarding large fractions of animal foods. Yet few see these fractionated animal foods as the refined, depleted foods they are.

Take dairy. Virtually all recommendations for dairy products include the qualifiers 'low-fat' or 'fat-free.' But that's not the way it comes out of the cow. Yes, whole milk has more calories than skim. It also has far more vitamin A, because it's carried in the butterfat. (Some skim milk is fortified with vitamin A —- the equivalent of adding a few vitamins back to nutritionally depleted white flour.) Because fat aids in calcium absorption, you'll get more calcium from whole milk. Whole milk from grass-fed cows supplies CLA, a fat that increases fat-burning and reduces heart disease and cancer risk, and omega-3 fats, which reduce inflammation, and heart disease and cancer risk. It is worth paying premium prices for such milk.

And eggs. Oh, poor eggs. There they are, just about the most perfect food in the world, and what do people do? They throw away the yolks. The part with almost all the vitamins, including A, E, K and the hard-to-come-by D, not to mention brain-enhancing choline and DHA. Eggs from pastured chickens also have yolks rich in omega-3. Better to throw away the whites, not that I'd recommend that, either. Just eat whole eggs, will you?

Then there's chicken. When did 'chicken' become synonymous with 'boneless, skinless chicken breast?' Chicken breast is a good food, but the whole chicken is better. Dark and white meats both have nutritional strengths. They are not identical in vitamin and mineral content. Chicken skin is a good source of vitamin A, again because it's fatty. I wrote recently about liver's nutritional bonanza, and hearts are nutrient-rich as well, making giblet gravy a great idea. Simmering the leftover chicken bones yields flavorsome broth rich in highly absorbable calcium and joint-building gelatin. (I save my steak bones, too, for beef broth.)

Our ancestors, ever mindful of where their next meal was coming from, relished every edible part of every animal they killed. Indeed, paleoanthropologists assert that hunter-gatherers ate the rich, fatty organ meats first, preferring them to muscle meats, and smashed bones to eat the marrow. As recently as a century ago, marrow was such a popular food that special spoons were made for scooping it out of bones. I love the stuff. I've been sucking the marrow out of lamb-chop bones since I was a tyke. A 1997 article in the journal Nature asserts that human brain capacity decreased at the dawn of agriculture 10,000 years ago, very likely because of a reduction in animal-fat consumption. Whole animal foods are part of our nutritional heritage.

My low-carbohydrate eating habits are often referred to as a 'fad.' Whatever. If it was good enough for my hunter-gatherer ancestors, it's good enough for me. Do you want to know what's really a fad? Removing the fat from milk and the yolks from eggs, and discarding three- quarters of the chicken, all organ meats and most bones. There's not a culture in the world where our narrow, refined, low-fat, flavorless versions of animal foods are part of the traditional diet.

Continuing reading for recipe included in original article!

Two-Fold Reduction in Triglycerides! How? Low-Carb!

2008-10-07T05:36:00.000-07:00

In a recent study - Metabolic Effects of Weight Loss on a Very-Low-Carbohydrate Diet Compared With an Isocaloric High-Carbohydrate Diet in Abdominally Obese Subjects, published in the Journal of the American College of Cardiology, researchers reported favorable results for obese adults randomly assigned a low-carbohydrate diet for 24-weeks (six months).

As reported in heartWire, "After six months, isocaloric energy-restricted very-low-carbohydrate, high-fat and high-carbohydrate, low-fat diets produced similar weight loss and substantial reductions in a number of cardiovascular disease risk markers," write Jeannie Tay (Flinders University, Adelaide, Australia) and colleagues in the January 1, 2008 issue of the Journal of the American College of Cardiology.

"Neither diet displayed adverse effects, suggesting diverse dietary patterns, including very-low-carbohydrate, high-fat diets, may be tailored to an individual's metabolic profile and dietary preference for weight management."

The investigators note that while the traditional diet reduced LDL-cholesterol levels, the low-carbohydrate, high-fat diet resulted in greater increases in HDL cholesterol and singificantly larger reductions in triacylglycerol levels (a two-fold greater reduction compared to the traditional low-fat diet).

In fact, if we go to the full-text, we find the researchers went so far as to write "consistent with other recent studies, the VLCHF (very-low-carb high-fat) diet produced greater reductions in TAG and increases in HDL-C than the HCLF (high-carbohydrate low-fat) diet. This suggests that the VLCHF diet as a weight loss strategy may confer the greatest clinical benefits in patients who present with hypertriglyceridemia, low HDL levels, abdominal adiposity, and insulin resistance"

Did you catch that - "greatest clinical benefits" - part?

In the paper we learn that subjects were randomly assigned to either of the moderately energy-restricted diet plans for 24 weeks. For those assigned to the very-low-carbohydrate, high-fat diet, 4% of total calories were obtained from carbohydrates, 35% from protein, and 61% from fat, including 20% of total calories from saturated fat. Subjects randomized to the high-carbohydrate, low-fat diet followed a more traditional macronutrient profile, with 46% of calories obtained from carbohydrates and 30% from fat, including <8% from saturated fat.

Now we all know the American Heart Association insists we must keep saturated fat at less than 7% of our calories because intakes higher than that will kill us (eye roll) - yet here we have stunning improvements with saturated fat intake at/above 20% daily for six months! If you haven't read it yet, Dr. Richard Feinman wrote a good article about saturated fat recently.

So, what gives? It seems the subjects in this study significantly reduced their carbohydrate, and that carbohydrate does matter. Also, this study confirms previous data published that found similar improvements in those restricting carbohydrate.

Slowly but surely more data is coming forward that validates carbohydrate restriction for not only weight loss, but health improvements!

Insulin Resistance and Cardiomyopathy

2008-10-07T05:35:00.000-07:00

An interesting abstract was published in the recent issue of the Journal of the American College of Cardiology - Insulin-Resistant Cardiomyopathy, Clinical Evidence, Mechanisms, and Treatment Options.

Increasing evidence points to insulin resistance as a primary etiologic factor in the development of nonischemic heart failure (HF). The myocardium normally responds to injury by altering substrate metabolism to increase energy efficiency. Insulin resistance prevents this adaptive response and can lead to further injury by contributing to lipotoxicity, sympathetic up-regulation, inflammation, oxidative stress, and fibrosis.

Animal models have repeatedly demonstrated the existence of an insulin-resistant cardiomyopathy, one that is characterized by inefficient energy metabolism and is reversible by improving energy use. Clinical studies in humans strongly support the link between insulin resistance and nonischemic HF.

Insulin resistance is highly prevalent in the nonischemic HF population, predates the development of HF, independently defines a worse prognosis, and predicts response to antiadrenergic therapy.

Potential options for treatment include metabolic-modulating agents and antidiabetic drugs. This article reviews the basic science evidence, animal experiments, and human clinical data supporting the existence of an "insulin-resistant cardiomyopathy" and proposes specific potential therapeutic approaches.

-------------------------

Protein Provides Satiety Through PYY

2008-10-07T05:31:00.000-07:00

In our strange world, we have researchers now promoting the idea that a pharmaceutical version of the gut hormone PYY may offer a solution to help individuals lose weight.

In the MSN article, Natural Gut Hormones May Provide a Treatment for Obesity, we learn that researchers are seeking to develop a pill to provide the satiety hormone PYY.

"The advantage of developing weight loss medications based on gut-derived satiety hormones is that they enhance a process that occurs naturally. It is expected, therefore, that side effects will be minimal," says Dr Sainsbury-Salis.

Folks, we're not PYY deficient; in fact, I'd argue we're not eating the foods that stimulate PYY to effectively sate appetite naturally.

As I noted in a previous blog post about research investigating PYY, "A high protein diet led to spontaneous calorie reduction as PYY increased. The phenomenon was consistent with both the animal model using mice and in human studies used to validate the mice model. Over a longer term, the higher protein diet stimulated weight loss and enhanced PYY synthesis and secretion in mice."

As I noted in that post, the study I wrote about included quite specific detail about how diet influences the release of PYY in humans - "The ready availability of carbohydrate-rich grains and cereals has been a recent development in human nutrition with the onset of organized agriculture. Many of the physiological systems that regulate food intake were probably established and may function better under lower-carbohydrate and higher-protein dietary conditions."

Those were not my words, but the words of the researchers!

And now we have researchers looking to design a pill to provide what we already have naturally - if we eat adequate protein and fat. But, let's not go there and discuss diet, let's just pop a pill and continue along with the supposed "healthy diet" that obviously is not sating out appetite!

The future of Oracle APEX - aka Oracle APEX 4.0

2008-10-07T05:29:00.000-07:00

In case you haven't read it on Marc Sewtz blog or in the newest edition of the German Oracle APEX community newsletter, on Friday July 25th at 14:00 (German time) there will be a WebCast about the features the Oracle APEX team is currently working on for Oracle APEX 4.0! The WebCast will also cover some tips & tricks for APEX 3.1.1.

Get the details about how to join the WebCast at the German Oracle APEX Community web site.

Overlapping labels in an Oracle APEX pie chart

2008-10-07T05:28:00.000-07:00

Ever had the problem of overlapping labels in a pie chart which you used in an Oracle Application Express (APEX) application? Have a look at Gary Myers excellent tip to avoid collisions by using analytic functions to sort the data. It's another great example of using the built-in power of the Oracle database.

Two new Oracle APEX whitepapers

2008-10-07T05:27:00.000-07:00

Ok, they are not brand new anymore, they have already been released last month. But in case you are not reading David Peake's blog or checking regularly the Oracle APEX website on OTN, the APEX team has published the following two new whitepapers:

NTLM Authentication (a PL/SQL only solution) and
Oracle APEX with RAC (Real Application Cluster)

Oracle APEX team needs your help!

2008-10-07T05:26:00.002-07:00

It's your chance to help the Oracle APEX team to spreading the word about Oracle APEX. They want to convince the Oracle eBusiness Suite/Oracle Applications team to formally legitimize the use of Oracle Application Express with the Oracle eBusiness Suite/Applications. I think that would be another huge step to get Oracle APEX into more companies if it can be officially used to write custom applications in that environment.

But they need your help! Check out David Peake's blog posting for more details.

What's next on the Oracle APEX roadmap? Oracle APEX 3.2!

2008-10-07T05:26:00.001-07:00

Just read on David Peake's blog that he has updated the Statement of Direction for Oracle Application Express (APEX).

What has changed?

They are planing to release Oracle APEX 3.2, which will be Oracle APEX 3.1 + The Oracle Forms Migration Tool. If you are a Forms guru, they are looking for beta testers! Get the details at David's blog.

I think the Forms Migration Tool will be another major step for Oracle APEX to get the Forms developers into the APEX boat. These two tools have a lot of similarities (declarative, PL/SQL, ...) and the same kind of productivity to develop database driven applications. Forms developers can re-use their existing skills and companies protect there investment by using the same business logic (if written in database packages) as for there Forms applications. Compared to learning or migrating an application into a new language (eg. Java/.Net) this can be a huge time safer.

Oracle APEX 3.1.2 is out!

2008-10-07T05:25:00.000-07:00

The Oracle APEX team has released a new patchset for Oracle Application Express (APEX). According to the readme file the new version 3.1.2 contains 28 new bug fixes (Note: the list also contains the bugs from the previous patchset).

The full release can be downloaded from the OTN download side.
The patchset with just the changed files can be found on Metalink with the patch number 7313609.

Number of displayed Records of Oracle APEX Popup LOV

2008-10-07T05:24:00.000-07:00

Recently I answered a question about where to change the number of displayed records of a Popup LOV in Oracle APEX and I thought it could be of general interest.

The behavior of Popup LOVs can be changed through the Popup Lov template. You can find it at
Shared Components\Templates\Popup LOV.

In the Pagination section you will find the property Display which defines how many records are displayed at once.

The Popup LOV template also contains other interesting settings like the icon which is used for the popup lov field or the text for the search button, the width and height which is used to display the popup lov window and much more. Just have a look!

DOAG Regionaltreffen in München zum Thema Oracle APEX

2008-10-07T05:23:00.000-07:00

Just a quick announcement about an Oracle APEX meeting of the German Oracle Usergroup in Munich. The rest of the posting will be in German.

Termin: Mittwoch, 17.09.2008, 17:00 - 19:00 Uhr

Geplante Vorträge:

1. Professionelle APEX Entwicklung - Projektbeispiele und Lessons Learned
Referent: Dietmar Aust, OPAL Consulting

2. Möglichkeiten mit Oracle TEXT (Volltextsuche) und APEX
Referent: Carsten Czarski, ORACLE Deutschland GmbH

Ab ca. 19:00 Uhr wird der Meinungsaustausch dann im Löwenbräukeller am Stiglmeierplatz fortgesetzt.

Weitere Details und die Anmeldung auf den Seiten der DOAG.

Übrigens, die Teilname ist kostenlos.

Pro Oracle Application Express finally shipping?

2008-10-07T05:22:00.000-07:00

Is John Scott's long awaited book Pro Oracle Application Express finally shipping?

It looks like!

There are reports on the OTN APEX forum that they have received a mail from Amazon that they have shipped the book. I also checked the order page of Amazon and it shows that the book is on stock.

You will not guess where I'm! ;-)

2008-10-07T05:20:00.000-07:00

On the other side it's not very hard to guess. I'm there where most of the Oracle blogging and Oracle APEX community currently is. I'm at Oracle Open World in San Francisco.

I'm not sure how often I will blog, because I have a tight time schedule. But if you want to get hold of me, I will try to visit most of the Oracle APEX sessions.

On Tuesday, between 14:30 and 15:30 I'm doing my own presentation titled The Power of the Oracle APEX Repository (Session Id: 300210). It's currently booked out, but you might get a chance to still get in.

Enough for now, I have to listen to Tom Kyte's "Efficient Schema Design" now.

Bracketing frenzy ....oracle

2008-08-12T23:42:00.000-07:00

I just found myself decyphering this:

SELECT ...
FROM report_results rpt
WHERE NOT ( (substr(rpt.report_type,2,1) in ('1','2','3','4','5')) and
         (rpt.sig_type = 'ISDNMA') and
         ((rpt.group_ctn != rpt.ctn_prefix||rpt.ctn_suffix) and
          (length(rpt.group_ctn) != length(rpt.ctn_prefix||rpt.ctn_suffix)) and
          ((length(rpt.group_ctn) != (length(rpt.ctn_prefix||rpt.ctn_suffix)-1)
          ))));

Which turns out to mean this:

SELECT ...
FROM   report_results rpt
WHERE  NOT (     SUBSTR(rpt.report_type,2,1) IN ('1','2','3','4','5')
           AND  rpt.sig_type = 'ISDNMA'
           AND  rpt.group_ctn != rpt.ctn_prefix||rpt.ctn_suffix
           AND  LENGTH(rpt.group_ctn) != LENGTH(rpt.ctn_prefix||rpt.ctn_suffix)
           AND  LENGTH(rpt.group_ctn) != LENGTH(rpt.ctn_prefix||rpt.ctn_suffix)-1 );

No wonder there are no brackets left in the shops.

EAV nightmare ....oracle

2008-08-12T23:41:00.000-07:00

My charitable Christmas mood only goes so far. I'm looking through a spec which reads like a "database design nightmare!" theme advent calendar. Each page reveals a potential disaster more frightening than the one before. This is my favourite new year hang over inducing cocktail of entity attribute values and generic application design all wrapped up in a gloriously mal-specified mess.

Table: Parameters

id  VARCHAR2(50) PRIMARY KEY  --The application requesting the value
identifier NUMBER       PRIMARY KEY  --The name of the parameter
type  CHAR(1) --‘I’, ‘S’ or ‘B’ is type of value for the parameter
string_val VARCHAR2(50) --‘Y’ or ‘N’ or NULL if BOOLEAN or the string
integer_val NUMBER       -- The integer value

note: The id holds a value comprising the IP address, type of
application and the instance at that IP.

Who needs modular code? ....oracle

2008-08-12T04:12:00.002-07:00

Wouldn’t it be nice if when people wrote some useful code, they tried to make it suitably modular and reusable? This is what I have to contend with at the moment. We have a large, complex system written in Oracle Forms that we are now partially re-writing in HTMLDB (hurrah!) One function I want to replicate is the ability to change your own Oracle password; the current Forms application has a form to do this that looks like this:

Old Password: [ ] New Password: [ ] Confirm New Password: [ ]

So that should be a moment’s work to redo, right? Wrong. There is a package of procedures for user maintenance that contains the following 2 procedures that are relevant:

PROCEDURE change_password_validate
(p_username         IN VARCHAR2,
p_old_password     IN VARCHAR2,
p_new_password     IN VARCHAR2,
p_confirm_password IN VARCHAR2,
p_profile_name     IN VARCHAR2,
p_mode             IN VARCHAR2 DEFAULT 'N',
p_mask             IN  VARCHAR2) ;

PROCEDURE change_password_process
(p_username IN VARCHAR2,
p_new_password IN VARCHAR2,
p_confirm_password IN VARCHAR2,
p_mask           IN VARCHAR2,
p_profile_name IN VARCHAR2,
p_encrypt_pw IN VARCHAR2,
p_admin_mode IN VARCHAR2 DEFAULT NULL) ;

Some design flaws are immediately evident:

Validation is totally separate from processing. If I choose to, I can skip the validate routine altogether and call the process routine to change the password to anything I like, regardless of whether I get the old password right or confirm it correctly. (Actually, this foolish separation of validation from processing is a company standard!)
I get to choose whether the password is to be stored (in our own application’s USERS table) in encrypted form or not. HTF do I know whether it should be encrypted or not?
I need to supply something called p_mask, which I think may be something to do with the encryption process, or maybe the validation process – none of this is documented of course, or at least nobody knows where any such documentation may be found. I have tried passing the word ‘mask’ and it seems to work, except that all subsequent attempt to change the password then fail on the validation of the “old” password – perhaps because it has been encrypted in an unexpected manner.
I also need to supply something called p_profile_name, which I do happen to know is a user attribute something like a role, stored in the USERS table. Well excuse me, but if I’m passing in the username as a parameter, why should I have to go look up the USERS record and obtain the profile_name value just to pass it into this lazy procedure?
I don’t fully understand p_mode and p_admin_mode either, but at least they have defaults which I assume (for now) I can live with.

So instead of being a 5 minute job, this is probably going to occupy about a day of my time: locating source code (the packages are wrapped in the database), studying source code to see what it is doing. And if all that fails, trying to find someone from the team that wrote the code and ask them to tell me what I should be doing.

Bring out your WTFs ....oracle

2008-08-12T04:12:00.001-07:00

Always on the lookout for blog material requiring minimum editorial effort, we welcome your WTFs. Amusing and instructive examples of mind-boggling Oracle-related madness (ideally short ones) can now be sent to us at our new e-mail address: OracleWTF@bigfoot.com. I would also like to welcome our two new WTF contributors, Tony Andrews and Scott Swank.

Grow Your Own Concurrency Problem ....oracle

2008-08-12T04:11:00.001-07:00

What's that? the sound of ORA-00001: approaching...

...
FUNCTION key_not_in_table(pkey IN INT) RETURN BOOLEAN
IS
 countkey INT;
BEGIN
 SELECT count(key) INTO countkey
 FROM key_values WHERE key = pkey;

 IF countkey > 0 THEN
  RETURN FALSE;
 END IF;
 RETURN TRUE;

END key_not_in_table;

PROCEDURE insert_or_update(pkey IN INT,
  pval IN INT)
IS
BEGIN
 IF key_not_in_table(pkey) THEN
  INSERT INTO key_values
  VALUES (key, value, 0);
 ELSE
  UPDATE key_values
  SET value =  pval
  WHERE key = pkey;
 END IF;
END insert_or_update;

Universal SQL Performance Improver Discovered ....oracle

2008-08-12T04:10:00.001-07:00

In an AskTom thread this week, the poster wrote:

"...I have been told before by several people, and I have implemented myself on several SQLs that adding the clause "AND 1=1" literally to any SQL statement helps improve the performance of the SQL statement dramatically."

And we've all been wasting our time looking for a FAST=TRUE parameter.

Bring out your WTFs ....oracle

2008-08-12T04:08:00.000-07:00

Always on the lookout for blog material requiring minimal editorial effort, we welcome your WTF submissions. Amusing and instructive examples of mind-boggling Oracle-related madness (ideally short ones) can now be sent to us at our new e-mail address: oraclewtf@bigfoot.com. Please remember to include "OracleWTF" in your Subject line.

I would also like to welcome our two new contributors, Tony Andrews and Scott Swank.

It's One More, Innit? ....oracle

2008-08-08T08:08:00.000-07:00

Thanks to Scott Lynch for submitting an example of how a J2EE application developer just might not trust the database to do its job.

Over to Scott...

From a big bucks retail management system (now owned by a big bucks DBMS vendor).

1. Get NextVal from the sequence.

2. Assign the value, an integer, to a string.

3. Check to see if the string they just created exists.

4. Cast the integer that has been cast to a string, to a BigDecimal.

5. Add 1 to it (because they're obviously smarter than some silly old sequence).

I just love step 3.

Sheer brilliance on that one. And it's repeated for almost every table in this particular little slice of the application.

------------------------------------------------------------------------------------------------

public long getNextId() throws java.sql.SQLException{
  if (conn == null)
  {
     throw new java.sql.SQLException("Connection not set");
  }
  long nextIdLong = 0;
  try
  {
     //Create a statement
     tStmt = conn.createStatement();

     //Create a query string to get all the fields from the table. The
     //presentation layer will decide which field to display
     String query = "SELECT some_seq.nextval FROM dual";

     //The complete query is executed
     rs = tStmt.executeQuery(query);
     rs.next();
     String nextIdString = rs.getString(1);

     if (nextIdString != null) {
        nextIdLong = ((new BigDecimal(nextIdString)).add(new BigDecimal(1))).longValue();
     }

     tStmt.close();

  } catch (SQLException e)
  {
     throw new java.sql.SQLException(e.toString());
  }
  return nextIdLong;
}

Two days before the day after tomorrow ....oracle

2008-08-08T08:07:00.000-07:00

Clearly a South Park fan worked here once:

cat oracle_GetThisworkingDay

DATE=`date +%Y%m%d%H%M`
CUTOFF=$2

#!/bin/ksh
# oracle_GetThisworkingDay
# Script to retrieve the current working day (YYYYMMDD) from
# the working_calendar table in the Oracle database.
oracle_GetPreviousWorkingDay `oracle_GetNextWorkingDay $DATE $CUTOFF`

I'll spare you the contents of these scripts. Suffice to say they call the following procedures:

   FUNCTION previous_day (
     p_date                              DATE DEFAULT SYSDATE
  )
     RETURN VARCHAR2
  IS
     v_result                      VARCHAR2 (10);
  BEGIN
     SELECT dt
       INTO v_result
       FROM working_calendar
      WHERE dt = (SELECT MAX (dt)
                    FROM working_calendar
                   WHERE dt < p_date );
   RETURN TO_CHAR(v_result,'YYYYMMDD');
 END;

( nice use of SQL there ) and of course...

   FUNCTION next_day (
     p_now                               DATE DEFAULT CURRENT_DATE
  )
     RETURN DATE
  IS
     RESULT                        DATE;
  BEGIN
     SELECT MIN (dt)
       INTO RESULT
       FROM working_calendar
      WHERE dt >= p_now + 1;

     RETURN RESULT;
  END;

When is a BLOB not a blob? ....oracle

2008-08-08T08:06:00.000-07:00

When it's a Bee-Lob, apparently. If think you know how to pronounce some of the more common Oracle-related words, you have to check Eddie Awad's post, "Char or Car", and the follow-up comments...

awads.net/wp/2006/01/18/char-or-car

INTEGER Type is Platform-Independent shock ....oracle

2008-08-08T08:04:00.000-07:00

We are grateful to oracleplsqlprogramming.com for their December 2005 Tip of the Month: Insights into PL/SQL Integers, in which we learn this:

INTEGER - defined in the STANDARD package as a subtype of NUMBER, this datatype is implemented in a completely platform-independent fashion, which means that anything you do with NUMBER or INTEGER variables should work the same regardless of the hardware on which the database is installed.

And thank goodness for that, is what we say. Sometimes you just don't need platform-dependent results from your PL/SQL integer calculation depending on the hardware on which the database is installed.

I Object, Your Honour... ....oracle

2008-08-08T08:02:00.000-07:00

Erm, excuse me for interrupting, but what exactly is this?

There are 2 ways to construct an ANYDATA. The CONVERT* calls enable construction of the ANYDATA in its entirety with a single call. They serve as explicit CAST functions from any type in the Oracle ORDBMS to ANYDATA.

(Found here in the 10.1 documentation).

Counting Sheep ....oracle

2008-08-06T22:35:00.000-07:00

I'd not seen this Oracle Forums thread before, though it started in 2002 and now has 198 replies. Somebody once asked for some PL/SQL coding standards, someone else offered to email some, and then for ever after gets bombarded with requests from other people saying "Please send same to me at another-idiot-sheep@nobrain.com" Every now and then someone kindly posts a URL to some PL/SQL standards on the web, or points out that these people are just getting their email addresses onto a lot of spam mailing lists, but on and on they go asking for a copy to be sent direct to them. It's surprisingly funny.

EAV Returns: The Concrete Elephant approach ....oracle

2008-08-06T22:34:00.000-07:00

Anyone who has read Tales Of The Oak Table, not to mention Tony Andrews' blog or any of the countless articles and discussions on the subject on AskTom and elsewhere, will know two things about the fabled "Entity-Attribute-Value" approach to database design, in which you model all "things" in one table with a "thing ID" and a "thing type", plus a second table holding one row per "attribute", and thus create an application that can model any conceivable type of thing, ever:

It seems like a clever idea at first.
It isn't.

But wait. A poster on OTN forums ("SIMPLE Database Design Problem") has solved the major problems inherent in the original Entity-Attribute-Value approach, by simply denormalising away the Attribute-Value part.

Now the ENTITIES table will have all the columns for every entity type. Maybe a lot of them will be null because "INVOICE" rows will use mainly different columns to "TROPICAL_DISEASE" rows, but disk space is cheap, and look at the simplification we have achieved by not having to babysit all those old-fashioned tables. And it's not a generic design any more, is it? It's concrete.

The table would look something like this:

ENTITYID ENTITYTYPE NAME      PRICE DIET  COLOUR ANNUAL_TURNOVER
-------- ---------- --------- ----- ----- ------ ---------------
1        PERSON     William
2        FRUIT      Banana                Yellow
3        COMPANY    Megacorp                     100000000
4        ANIMAL     Fruitbat        Fruit
5        SNACK      Snickers  0.4

accompanied by a generic RELATIONS table like this:

ENTITY1 ENTITY2 RELATIONSHIP
------- ------- ------------
3       1       EMPLOYS
1       2       EATS
1       5       EATS

Want to list the snacks eaten by Megacorp employees? Simple:

SELECT emp.entityid, emp.name, snack.name, snack.price
FROM   entities emp
      JOIN relations emprel
      ON  emprel.entity2 = emp.entityid
      AND emprel.relationship = 'EMPLOYS'

      JOIN entities com
      ON com.entityid = emprel.entity1
      AND com.entitytype = 'COMPANY'

      JOIN relations snrel
      ON  snrel.entity1 = emp.entityid
      AND snrel.relationship = 'EATS'

      JOIN entities snack
      ON  snack.entityid = snrel.entity2
      AND snack.entitytype = 'SNACK'

WHERE  emp.entitytype = 'PERSON'
AND    com.name = 'Megacorp';

Want to make FRUITBAT an employee of SNICKERS?

INSERT INTO relations VALUES (5, 4, 'EMPLOYS');

The thread becomes increasingly surreal as more and more posters suggest likely issues, from performance (he's prototyped it and the slowdown is insignificant) to complexity (the code will be generated dynamically from an object library) and the limited number of columns per table in Oracle (he might go with MySQL) while Erdem remains cheerfully confident that it will work (it won't).

My thanks to 3360 for sharing this. Send your WTFs to us at OracleWTF@bigfoot.com.

The Phantom's Gonna Git Ya ....oracle

2008-08-06T22:33:00.000-07:00

I know I'm asking for trouble here by offering an AskTom page for a WTF, but I couldn't resist. If there was ever a time you wanted your spelling to be spot on, it would be when posting a link to a spell-checker...

Wanna Date? ....oracle

2008-08-06T22:32:00.000-07:00

Dates are known to be exceedingly difficult and avoiding them at all costs is something of a skill. This function, in the spirit of Never do in SQL what you can do in PL/SQL, calculates a date range before calling another procedure that also avoided using dates for its input date parameters. So despite its absence, it is at least partially responsible for this mess.

I stripped the code down to its date handling which studiously avoids using date calculations wherever possible, and uses string handling instead, leaving in the comments because they are also the documentation.

create or replace function start_date (
p_range         in      varchar2,
p_in_date       in      varchar2    -- DD-MON-YYYY format String
)
return varchar2
as
l_out_date varchar2(11);
month varchar2(10) := to_char(to_date(p_in_date,'DD-MON-YYYY'),'MON');
year varchar2(10)  := to_char(to_date(p_in_date,'DD-MON-YYYY'),'YYYY');
v_cnt_yr        number;
v_end_date      date    := to_date(p_in_date,'DD-MON-YYYY');
v_start_date    varchar2(11);
begin
if p_range = 'QTD' THEN

 if month in ('JAN','FEB','MAR') then
    -- if given month = march and date is 31
    -- then data for jan,feb and march.
    l_out_date := '01-JAN-'||year;
 elsif month in ('APR','MAY','JUN')  then
    l_out_date := '01-APR-'||year;
 elsif month in ('JUL','AUG','SEP')  then
    l_out_date := '01-JUL-'||year;
 elsif month in ('OCT','NOV','DEC')  then
    l_out_date := '01-OCT-'||year;
 end if;

elsif p_range = 'YTD' then

 -- beginning of the year.
  l_out_date := '01-JAN-'||year;

elsif p_range = 'M' or p_range='MTD' then

 -- beginning of month
  l_out_date := '01-'||month||'-'||year;

elsif p_range like 'B%' then
  v_cnt_yr     := substr(ltrim(rtrim(p_range)),2);
  -- We take the start date as the first day after trailing
  -- back the required no. of months
  l_out_date   := to_char(last_day(add_months(
                     last_day(v_end_date), -v_cnt_yr)) + 1,
                     'DD-MON-YYYY');
end if;
return l_out_date;
end;

After reading it I thought, "So what does this do that TRUNC doesn't?" Apparently not a lot when you need to get the month, quarter or year to date. If you try this at home remember to format the return of START_DATE for readability since it usefully returns a 4000 character string.

SQL> exec :d := '17-JUN-2006'

PL/SQL procedure successfully completed.

SQL> select start_date('QTD',:d) start_date,
2    trunc(to_date(:d),'Q') from dual;

START_DATE  TRUNC(TO_DA
----------- -----------
01-APR-2006 01-APR-2006

SQL> select start_date('YTD',:d) start_date,
2    trunc(to_date(:d),'Y') from dual;

START_DATE  TRUNC(TO_DA
----------- -----------
01-JAN-2006 01-JAN-2006

SQL> select start_date('MTD',:d) start_date,
2    trunc(to_date(:d),'MM') from dual;

START_DATE  TRUNC(TO_DA
----------- -----------
01-JUN-2006 01-JUN-2006

But what about the mysterious 'B%' format mask? This calculates the first day of the month, where 'Bn' is n-1 months ago, tricky eh? In SQL we are forced to call two functions instead of having START_DATE call ADD_MONTHS for us with two bonus LAST_DAYS thrown in for good measure. The n-1 bit could even be a bug but who knows?

SQL> select start_date('B12',:d) start_date,
2    trunc(add_months(to_date(:d),-11),'MM') from dual;

START_DATE  TRUNC(ADD_M
----------- -----------
01-JUL-2005 01-JUL-2005

The convenience obviously outweighs the problem of having to deal with an undocumented date function that accepts and returns strings. Also with this function I have the luxury of substituting 'M' for 'MTD', but not 'Y' for 'YTD' or 'Q' for 'QTD' though I suspect these could be improvements for versions 2.0 and 3.0.

Stop Press: Oracle Granted License To Extend February ....oracle

2008-08-06T22:31:00.000-07:00

Yes, it's official. Oracle has been granted permission to extend February by 3 days. Shame no-one told the developers responsible for INTERVAL arithmetic.

SQL> SELECT DATE '2006-01-31' + INTERVAL '1' MONTH
 2  FROM   dual;
SELECT DATE '2006-01-31' + INTERVAL '1' MONTH
                          *
ERROR at line 1:
ORA-01839: date not valid for month specified

Hey, we do the WTFs... ....oracle

2008-08-06T22:30:00.000-07:00

I found this beauty on Connor McDonald's web site. It's perfect fodder for Oracle WTF and Connor is happy for us to include it here.

Tip: add DISTINCT to every query ....oracle

2008-08-06T22:29:00.000-07:00

We all know that SQL can be a harsh mistress, and relational theorists such as Chris Date have long argued that the language is fundamentally flawed and that vendors have been misapplying relational theory from the start.

Now a Perl developer on perlmonks.org has been reading Date's book and finds that it explains all of his frustrations with databases. One tip for addressing their shortcomings is to add the handy DISTINCT keyword to every single query, because stupid old SQL doesn't automatically apply the degree of uniqueness you might have in mind:

In fact, one of the founders of relational theory, C.J. Date, recommends that every SQL SELECT statement be written as SELECT DISTINCT ... Unfortunately, many folks get upset with this for a variety of reasons. First, DBMS do a horrible job of optimizing DISTINCT queries. There are many cases where the DISTINCT can be safely ignored but in practice, using DISTINCT with large sets of data and complicated queries can have an abysmal impact on performance. I think this can be put into the "premature optimization" category, though. Until we know that DISTINCT is causing an impact, isn't it better to improve the chance of getting correct results and worry about performance afterwards?

I had to read that a couple of times to realise that adding a DISTINCT to every single query in the hope that it might mask some unknown deficiency in your model, your query, or the SQL language itself is not the "premature optimization" being referred to here - he means the idea that doing so might affect performance. After all, it might not, right?

Read the rest of the discussion at perlmonks.org: "Why SQL Sucks (with a little Perl to fix it)". It also appears on Database Debunkings, "On the sins of SQL".

Hey, we do the WTFs Part II... ....oracle

2008-08-06T22:28:00.002-07:00

At this rate, we might need to make Connor an honorary member. Here's another gem from his web site that he's picked up on his travels. PL/SQL doesn't get much "better" than this...

Umm, I forgot my password ....oracle

2008-08-06T22:28:00.001-07:00

Probem solved, with the following convenient password reset procedure found in a large production database, with EXECUTE granted to PUBLIC and a handy public synonym:

CREATE OR REPLACE PROCEDURE reset_user_password(p_username IN VARCHAR2)
AS
BEGIN
  execute immediate 'ALTER USER '||upper(p_username)||' IDENTIFIED BY '||upper(p_username);
END;
/

Many thanks to Robert De Laat for this submission.

Error-Prone Error Handling ....oracle

2008-08-06T22:26:00.000-07:00

A colleague found this handy utility on the internet. The idea is that whenever you get an Oracle error, the errant SQL statement will be written away to a table along with the message for you to read and enjoy at your leisure. Which would be fine I suppose, if it didn't introduce a whole lot of errors of its own.

create table caught_errors (
 dt        date,              
 username  varchar2( 30), -- value from ora_login_user
 msg       varchar2(512),
 stmt      varchar2(512)
);

create or replace trigger catch_errors
  after servererror on database
declare
  sql_text ora_name_list_t;
  msg_     varchar2(2000) := null;
  stmt_    varchar2(2000) := null;
begin

 for depth in 1 .. ora_server_error_depth loop
   msg_ := msg_ || ora_server_error_msg(depth);
 end loop;

 for i in 1 .. ora_sql_txt(sql_text) loop
    stmt_ := stmt_ || sql_text(i);
 end loop;

 insert into
   caught_errors (dt     , username      ,msg ,stmt )
          values (sysdate, ora_login_user,msg_,stmt_);
end;
/

Note that whenever a SQL error occurs on the database this trigger will fire and:

1) try to stuff the entire SQL statement that failed into a varchar2(2000), regardless of how big it actually is

2) if that doesn't blow up, then tries to insert the same value into a varchar2(255) column

In SQL Plus, this leads to errors like this (using an invalid table name in a large SELECT statement):

ERROR at line 30:
ORA-00604: error occurred at recursive SQL level 1
ORA-01401: inserted value too large for column
ORA-06512: at line 12
ORA-00942: table or view does not exist

... which is clearly more informative than:

ERROR at line 30:
ORA-00942: table or view does not exist

Create Your Own DUAL Table ....oracle

2008-08-06T22:25:00.002-07:00

If you want to retrieve a sequence value into a PL/SQL variable, you have to SELECT FROM DUAL. (Or use RETURNING INTO of course, but never mind that now.)

According to some, this is not only an inconvenient restriction, but also prone to failure if SYS.DUAL contains more than one row.

Help is at hand in the form of the utility below, which solves both problems at once by installing a table, a public synonym, a trigger and a function. Now your application will never again be unable to retrieve sequence values directly into PL/SQL variables on days when DUAL contains more than one row. So that's one less thing to worry about.

CREATE OR REPLACE PROCEDURE replace_onerow (
table_name_in IN VARCHAR2
)
IS
BEGIN
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name_in;
EXCEPTION
   WHEN OTHERS THEN NULL;
END;

EXECUTE IMMEDIATE 'CREATE TABLE '
                  || table_name_in
                  || ' (dummy VARCHAR2(1))';

EXECUTE IMMEDIATE
   'CREATE OR REPLACE TRIGGER onerow_' || table_name_in ||
   '  BEFORE INSERT
      ON ' || table_name_in || '
   DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_count PLS_INTEGER;
   BEGIN
      SELECT COUNT (*)
      INTO   l_count
      FROM   ' || table_name_in || ';

      IF l_count = 1
      THEN
         raise_application_error
         ( -20000
         , ''The ' || table_name_in || ' table can only have one row.'' );
      END IF;
   END;';

EXECUTE IMMEDIATE 'BEGIN INSERT INTO '
                  || table_name_in
                  || ' VALUES (''X''); COMMIT; END;';

EXECUTE IMMEDIATE 'GRANT SELECT ON '
                  || table_name_in
                  || ' TO PUBLIC';

EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM '
                  || table_name_in
                  || ' FOR '
                  || table_name_in;

EXECUTE IMMEDIATE
   'CREATE OR REPLACE FUNCTION next_pky (seq_in IN VARCHAR2)
       RETURN PLS_INTEGER AUTHID CURRENT_USER
    IS
       retval PLS_INTEGER;
    BEGIN
       EXECUTE IMMEDIATE ''SELECT '' || seq_in
                     || ''.NEXTVAL FROM ' || table_name_in ||
                     '|| ''INTO retval;
       RETURN retval;
    END next_pky;';

END replace_onerow;

What is it with dates? ....oracle

2008-08-06T22:25:00.001-07:00

What exactly is it with dates that so many Oracle developers struggle with? Why do they go to such lengths to avoid using the DATE type? Why, if DATE validation or arithmetic is required, would they use CHARs or NUMBERs? Abuses of DATEs seems to be a recurring theme on Oracle WTF. Indeed, here's another good example kindly provided by Graham Oakes.

Over to Graham...

This is a cracker, the easy way to check the supplied date in a string (after all who actually wants to use date types) is a valid date.

IF to_number(substr(v_valuedate,3,2)) NOT BETWEEN 1 AND 12
THEN
   v_rowstatustype := -190;
ELSE
   -- check 31 day months
   IF substr(v_valuedate,3,2) IN ('01','03','05','07','08','10','12')
   THEN
       IF to_number(substr(v_valuedate,1,2)) > 31
       THEN
           v_rowstatustype := -200;
       END IF;

   -- check 30 day months
   ELSIF substr(v_valuedate,3,2) IN ('04','06','09','11')
   THEN
       IF to_number(substr(v_valuedate,1,2)) > 30
       THEN
           v_rowstatustype := -200;
       END IF;

   -- check leap year feb
   ELSIF substr(v_valuedate,3,2) = '02'
         AND MOD(to_number(substr(v_valuedate,5,4)),4) = 0
   THEN
       IF to_number(substr(v_valuedate,1,2)) > 29
       THEN
           v_rowstatustype := -200;
       END IF;

   -- check non-leap year feb
   ELSIF substr(v_valuedate,3,2) = '02'
         AND MOD(to_number(substr(v_valuedate,5,4)),4) != 0
   THEN
       IF to_number(substr(v_valuedate,1,2)) > 28
       THEN
           v_rowstatustype := -200;
       END IF;
   END IF;
END IF;

Umm, I forgot my password, Part 2 ....oracle

2008-08-06T22:24:00.000-07:00

In a thread on OTN forums, a poster asked how he could recover a user's password. Naturally he was told that it can't be done because the password itself is not stored, only a hash based on the username and password combination.

After some interesting discussion of password hashing, brute force and rainbow table attacks and the like, a poster makes the following rather novel suggestion:

if you apply the password verify function, yes it is possible to get the password of a user.

Etape 1: edit the utlpwdmg.sql script and add the line which is in bold (insert into...)

-- Check if the password is same as the username

IF NLS_LOWER(password) = NLS_LOWER(username) THEN raise_application_error(-20001, 'Password same as or similar to user'); END IF;

insert into mytable values ('username','password');
-- Check for the minimum length of the password
Etape 2: run this script as sys

Etape 3: grant the profile to user whom u want to get the password.

u will be able to get the new password by consulting the table mytable (u must create this table)

This had me puzzled at first, and I had to check what $ORACLE_HOME/rdbms/admin/utlpwdmg.sql did. In fact it creates a default password verification function called "verify_function" ("verify_password" might have made a better name, but that's obfuscation for you), and then assigns it to the default profile using ALTER PROFILE DEFAULT ... PASSWORD_VERIFY_FUNCTION verify_function;

This means that any attempt to change the password for a user with the default profile (see ALTER USER examples in the documentation), will automatically execute verify_function(username, password, old_password). The idea is to apply some rules to prevent easily guessed passwords such as your username, but Mouhamadou's ingenious addition is his extra line,

insert into mytable values (username,password);

Now any attempt to change the password for a user with the default profile that successfully passes this extra security step will result in the new password being logged in mytable in clear text.

As we like to say on Oracle WTF, problem solved.

Many thanks to Andrew P. Clarke for submitting this.

Simplest row generator meets maximum inefficiency ....oracle

2008-08-06T22:23:00.001-07:00

Generating a set of values that are not stored in a table is a reasonably common problem. I recently came across this solution for a set of irregular interval values.

 OPEN cur_rows FOR
 'SELECT 3  FROM dual UNION '||
 'SELECT 6  FROM dual UNION '||
 'SELECT 9  FROM dual UNION '||
 'SELECT 12  FROM dual UNION '||
 'SELECT 24  FROM dual UNION '||
 'SELECT 36  FROM dual';
LOOP
  FETCH cur_rows INTO v_num_value;
  EXIT WHEN cur_rows%NOTFOUND;

I didn't know which to admire the most. The clever use of dynamic SQL or the sort distinct to get rid of any duplicate literals that may occur from accidentally typing the same line twice. I think the latter wins for being also relationally pure apparently.

If only there was a SUM function ....oracle

2008-08-06T22:22:00.000-07:00

...then we wouldn't have to write code like this, which, as Graham Oakes can confirm, takes ages:

FOR r IN ( SELECT tid FROM t_brel WHERE bqid = iqid )
LOOP
   SELECT q.lamount, q.famount
   INTO   v_lamount, v_famt
   FROM   t_aq   atq
        , t_q    q
   WHERE  atq.tid = r.tid
   AND    q.qid = atq.qid
   AND    qtype = 10;

   v_ltotal := v_ltotal + v_lamount;
   v_ftotal := v_ftotal + v_famt;
END LOOP;

UPDATE t_q
SET    lamount = v_ltotal
    , famount = v_ftotal
WHERE  qid = iqid;

We can but dream.

While we're on the subject, we received this from a correspondent who wishes to remain anonymous:

FOR r IN
(
   SELECT /*+ FIRST_ROWS */
          *
   FROM   pay_details
   WHERE  acct_fk = p_accountpk
)
LOOP
   DELETE pay_details
   WHERE  primarykey = r.primarykey;

   COMMIT;
END LOOP;

I particularly like the FIRST_ROWS hint. "What, it's slow? Better make sure it uses that index..."

The Decibel Method ....oracle

2008-08-06T22:21:00.000-07:00

A poster on the hardcore comp.databases.oracle.server newsgroup had some tables in the production database that he felt were redundant, and asked, not unreasonably I felt, whether there was a way to tell from his Developer 6i application whether they were in fact in use:

hello

we are using oracle 9i production database and d2k 6i applications. is there any way by which i can find the tables/ columns that are not in used by applications. So that i can move them out from our production database.

can anyone throw some light how to do it.

After the obligatory initial responses along the lines of "it can't be done" and "you are an idiot for even thinking about it" (you take your life in your hands when you post on cdos), one respondent begins,

For tables, the solution is called "auditing". You can audit desired objects.

Reasonable enough. Give it a year, then if nobody has used the tables, they are probably not so important. Then he continues:

For columns, you should be using so called decibel method: if you suspect that column C1 in table TAB is not used, you can always execute the following commands:
update TAB set C1=NULL;
commit;
If the reaction to that is a loud scream, accompanied by swearwords and a genuine cornucopia of various expletives, you've made a mistake, the column was used. It's time for the "I'm sorry, I didn't know that this column was still being used" routine. You can rest assured that this swearing sucker is gonna be busy for a while.

If not, you can proceed and drop the column. The previous update has an added benefit of making "drop column" operation faster. It will also expose weak points in all those lousy applications that use "select *" and expect the table to populate all of their variables.

An alternative to the decibel method is fine-grain auditing, described in the books by D. Knox. It's much more tedious and requires much larger knowledge then the decibel method, which is also a lot of fun.

Problem solved I think.

Our thanks to Herod T for the plug.

ORA-06553: PLS-906: Compilation is not possible ....oracle

2008-08-06T22:20:00.000-07:00

Or in this case posted on the OTN Forums, is it even desirable. It's nice to see there is the ability for the database to simply say, "I'm sorry but this code is too stupid to run".

The Clue Was In The Name ....oracle

2008-08-06T22:19:00.000-07:00

email from tester:

This bug is blocking test: bg9876-a, I have elevated it to priority 1.

reply:

The bug you've raised says "please provide a version of write_transaction() stored procedure, that doesn't write anything to the database", can you explain where you're going with this?

email from tester:

It is a question of practicality for us, we have used this method in every release, we need to test high throughput rates without a Performance Oracle server.

A Thneed's a Fine-Something-That-All-People-Need! ....oracle

2008-08-06T22:18:00.000-07:00

Now maybe I'm misusing this whole forum with this posting, and if so I apologise profusely, but...

www.janus-software.com/fb_fyracle.html

Are you worried about "under-licencing" too...?

Not only does open source Firebird-Fyracle have a catchy name and almost match Oracle on the 6 well known "gold standard key database evaluation mapping criteria", but it is "just as idiosyncratic", AND you can run "Compiere v2.5.3c" ( almost )!

Who could possibly argue with the rigorously documented "proof of the pudding" that this product is "faster than Oracle on the same hardware"

SP2-0552: Bind variable not declared ....oracle

2008-08-06T22:17:00.000-07:00

Please help. Why does SQL*Plus keep giving me the error

SP2-0552: Bind variable "INTARRAY" not declared.

Thanks in advance.

DECLARE
   BatchSize : constant := 50;

   subtype IndexRanges is INTEGER range 1 .. BatchSize;
   type IntArrays is array( IndexRanges) of INTEGER;
   IntArray : IntArrays;

   EXEC SQL DECLARE network_cursor CURSOR FOR
       select pal.provider_id from hold_provider_address_link pal;

   EXEC SQL OPEN network_cursor;

-- establish a local block, with an exception to
-- handle the "no data found" condition
begin
   EXEC SQL WHENEVER NOT FOUND raise NO_MORE_DATA;
   FETCH_LOOP:
   loop -- fetch the data, 20 rows at a time
       EXEC SQL FETCH network_cursor
       INTO :IntArray;

       for I in 1..20 loop
           -- process batches of 20 rows
           ...
       end loop;

       commit;
   end loop FETCH_LOOP;

exception
   -- the exception NO_MORE_DATA is raised when there is
   -- no more data to FETCH
   when NO_MORE_DATA =>
       PUT("No more data to fetch. N of rows fetched was ");
       PUT(ORACLE.SQLROWS);
       NEW_LINE;

       -- turn off the error handling
       EXEC SQL WHENEVER NOT FOUND CONTINUE;
end;
/

I mentioned this in a comment the other day so apologies if you've seen it before, but I felt it really deserved its own post.

(Hint: SP errors are from SQL*Plus, and INTARRAY is a bind variable from SQL*Plus's point of view because it begins with ":")

By the way I don't mean to laugh too much at the poor guy who posted this problem on a forum, as from his other posts he seems to have had a crappy application dumped on him without much support from anyone at his company. I did ask him what language it was written in but he hasn't replied. Suggestions, anyone? (My guess is Pro*Ada, which in my opinion we don't see enough of these days.)

Oracle World Goes Sensible shock ....oracle

2008-08-06T22:16:00.002-07:00

You may be wondering WTF happened to all the WTFs, and so are we. It seems the Oracle world has been going through a rather depressing sensible season recently, in which nobody posts Pro*Ada code and wonders why it won't run at the SQL*Plus prompt, or suggests adding "AND 1=1" to any query to make it go faster. Even Mr Feuerstein seems to have deserted us.

Perhaps Mike Ault's foray into international economics cheered some of us up. He proposed an ingenious regulatory system requiring (I think) US grain, medicine and other key exporters to adjust their prices in response to international oil market fluctuations, on the grounds that certain oil producers are not taking America seriously enough. Now that'll teach Johnny Foreigner a lesson. "It is time for America to get tough", he adds. Oh dear.

On a more conventional note, a Mr Sahil Malik complained at great length on cdos about how hard it was to install 9i Personal Edition on his PC. At one point he fumed:

Larry Ellison IMHO has only one business idea - "Defeat bill gates and trap every programmer in matrix like pods powering oracle databases". WHAT THE HECK !! Time he matured up a bit.

I agree. Larry Ellison, if you're listening, you need to mature up a bit and forget the whole Matrix pod idea. We all know that ends badly.

Over on the OTN SQL Developer Forum, we were intrigued when one frustrated poster asked:

I cannot find clustered option for the indexes or PKs that I create in SQL Developer.

Where is the CLUSTERED check box in user interface?

Where indeed? Helpfully, Sharon from the SQL Developer team promised to get one added ASAP:

I have added an enhancement request to get this added to the interface in a future release.

We look forward to seeing what that does.

Code generation nonpareil ....oracle

2008-08-06T22:16:00.001-07:00

We've all engaged in code generation of one sort or another. For certain problems it's just the right, or perhaps the only tool. But then your content management vendor starts to throw this sort of thing at your database...

OPEN p_cursor FOR
'SELECT content_id, content_status
   FROM content
  WHERE content_type_id = 319
    AND content_id IN (
     51055 , 45531 , 42208 , 42911 , 46494 , 52898 , 44262 , 44312 , 47474 , 42792 ,
     45956 , 45109 , 53432 , 14936 , 29040 , 28779 , 53015 , 48366 , 53739 , 48565 ,
     47188 , 46573 , 43038 , 53534 , 51999 , 49731 , 52847 , 43883 , 41522 , 50804 ,
     49975 , 45729 , 53260 , 47658 , 41325 , 49454 , 41374 , 45328 , 51612 , 54347 ,
     50092 , 48147 , 42416 , 42570 , 49533 , 41948 , 51740 , 52973 , 42648 , 44867 ,
     48289 , 45943 , 49556 , 54550 , 46801 , 43628 , 40569 , 41576 , 46752 , 44982 ,
     42309 , 45146 , 47198 , 44993 , 47768 , 47060 , 46889 , 45651 , 47045 , 45830 ,
     41248 , 54370 , 43741 , 44183 , 28451 , 45094 , 54332 , 47030 , 42060 , 41293 ,
     48287 , 48012 , 47740 , 45688 , 43639 , 48484 , 47583 , 45304 , 51478 , 42633 ,
     40558 , 43793 , 41587 , 49407 , 28803 , 43272 , 46464 , 45602 , 43866 , 44521 ,
     41200 , 48044 , 46927 , 29186 , 45774 , 43722 , 45128 , 43398 , 47397 , 41670 ,
     51888 , 47534 , 29237 , 42486 , 53811 , 44704 , 46618 , 48994 , 44848 , 44573 ,
     52956 , 44487 , 42435 , 48164 , 43451 , 52031 , 51300 , 52595 , 53141 , 44032 ,
     50904 , 41477 , 42161 , 44622 , 52695 , 43838 , 44562 , 45373 , 44882 , 47247 ,
     42367 , 50921 , 46265 , 41933 , 48960 , 43143 , 43345 , 28412 , 48868 , 49005 ,
     43533 , 46953 , 52420 , 44017 , 49817 , 41490 , 41395 , 43027 , 47703 , 29064 ,
     41689 , 50564 , 44112 , 28957 , 29544 , 45223 , 43252 , 49699 , 47356 , 43565 ,
     53604 , 45478 , 29422 , 53041 , 45697 , 44054 , 42469 , 44035 , 48750 , 46667 ,
     50060 , 46698 , 48306 , 45849 , 45563 , 42452 , 45143 , 46110 , 47800 , 43076 ,
     41280 , 45862 , 41657 , 42114 , 47523 , 51841 , 45988 , 48023 , 47307 , 43203 ,
     53464 , 44202 , 47984 , 46218 , 45255 , 51412 , 52409 , 47442 , 45932 , 28269 ,
     43823 , 46835 , 47785 , 50968 , 46050 , 41916 , 47219 , 51772 , 41980 , 43377 ,
     43681 , 50007 , 43072 , 47150 , 45900 , 53376 , 54338 , 45360 , 49086 , 46520 ,
     47626 , 29529 , 50321 , 51982 , 53660 , 51644 , 42028 , 41901 , 53269 , 47995 ,
     43754 , 49163 , 46069 , 51213 , 53566 , 52304 , 49588 , 51134 , 45167 , 46447 ,
     42108 , 45570 , 29439 , 46091 , 44160 , 52227 , 47324 , 42256 , 51262 , 28854 ,
     43220 , 41363 , 45272 , 42097 , 46790 , 51102 , 50338 , 44149 , 49849 , 49375 ,
     41814 , 44738 , 54211 , 52712 , 54364 , 50283 , 54442 , 46863 , 48687 , 45178 ,
     28464 , 44084 , 47615 , 44504 , 50895 , 46231 , 45723 , 47753 , 47230 , 29600 ,
     43430 , 28764 , 41619 , 44217 , 43649 , 47502 , 41509 , 47425 , 42873 , 51814 ,
     41438 , 44925 , 15364 , 41539 , 50336 , 42777 , 42127 , 44967 , 46942 , 43578 ,
     46644 , 48887 , 52748 , 46904 , 42290 , 44000 , 45446 , 45967 , 46539 , 44361 ,
     52990 , 45341 , 42326 , 47952 , 49746 , 29512 , 45806 , 50252 , 48121 , 46981 ,
     29162 , 41702 , 48700 , 50692 , 49039 , 44689 , 51181 , 42988 , 41737 , 41782 ,
     54283 , 44796 , 42667 , 48793 , 42597 , 49195 , 46018 , 48070 , 42975 , 49264 ,
     43675 , 29478 , 51000 , 43287 , 49052 , 51589 , 42506 , 44301 , 44649 , 44908 ,
     48072 , 50857 , 46020 , 44067 , 42013 , 42091 , 43464 , 48578 , 54015 , 41412 ,
     51361 , 52509 , 47124 , 51316 , 44143 , 29724 , 52714 , 44638 , 46142 , 41459 ,
     42586 , 43919 , 48945 , 44346 , 49390 , 44822 , 46631 , 43107 , 43591 , 42144 ,
     29755 , 43662 , 47540 , 43808 , 45390 , 42223 , 42371 , 42551 , 47918 , 47337 ,

For brevity's sake I'll skip the next 11,000 elements of the IN list.

If in doubt, test, test and test again ....oracle

2008-08-06T22:15:00.001-07:00

Thanks to Rob Baillie for the following example...

I was glancing through some legacy code today, and came across this.

It's funny how barnacles can accumulate when code changes over time.

            if r2.status_id = 3 then
                v_gp := r2.rate;
           elsif r2.status_id in (11, 12) then
               if r2.type_id = 3 then
                   v_gp := r2.rate;
               else
                   v_gp := r2.rate;
               end if;
           else
               v_gp := r2.rate;
           end if;

Skip over the record being called r2 and work out what it actually does...

Dynstatic SQL... ....oracle

2008-08-06T22:14:00.000-07:00

Here's Connor again...

Note that the following code has been "anonymised" to protect the guilty.

procedure P is
begin
  ...
  ...
  execute immediate 'drop  table T';
  execute immediate 'create table T as select * from ......';
  ...
  ...
  ...
  for i in ( select * from T ) loop
       ...
       ...
  end loop;
end;

Ah, a mix of dynamic and static references... Now how precisely did that compile? Nope, I'm not sure either.

Why Machines Will Never Take Over The World ....oracle

2008-08-06T22:13:00.000-07:00

It is a recurring theme in science fiction that as computers increase in power and sophistication, they may one day reach a point where they decide they can do better without us, and condemn us to lives suspended in racked pods with our brains plugged into a vast virtual world, or alternatively send increasingly resourceful and indestructable robots to hunt us down in our bunkers, while above ground survivors fight a desperate war for survival amidst the wreckage of civilisation.

If you find this vision of the future alarming, take heart in this SQL query, which was generated by a machine. Not, perhaps, a Cyberdyne Systems T-800 or the Matrix Mainframe, but something called OLAP API. We don't know what that is either, but we can tell you that if any robot descended from it ever attempts to enslave humanity using a virtual world and an unfeasible pod system, rest assured that you will have time to get out of its way.

That Reminds Me... ....oracle

2008-08-06T22:12:00.002-07:00

Sometimes the solutions people come up with to a given problem are breathtaking in their combination of ingenuity and insanity. Take this view, which exists purely to transform a boring "reminder number" like 3, 4, 5, ... into the English text "Third Reminder", "Fourth Reminder", "Fifth Reminder", ...

CREATE OR REPLACE VIEW reminders_view AS
SELECT reminder_id
, reminder_seq
, ( SELECT
     DECODE (reminder_seq,
      1, 'No reminder',
      2, 'Reminded',
      3, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
      4, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
      5, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   6, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   7, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   8, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   9, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   10, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   11, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   12, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   13, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   14, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   15, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   16, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   17, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   18, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   19, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   20, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   21, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   22, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   23, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   24, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   25, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   26, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   27, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   28, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   29, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
   30, INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder',
      'Reminded') FROM DUAL) reminder_type
FROM reminders;

What a beauty! It works as follows:

1) Use a DECODE to determine what the number is. Note that 2 is treated as a special case, even though it ends up the same as all the "other" numbers above 30.

2) For numbers between 3 and 30:

2.1) Convert to a string

2.2) Concatenate with '-MAY-2004'

2.3) Convert to a date

2.4) Convert back to a string using the 'ddspth' format mask

2.5) Convert to Init Caps

2.6) Append the word ' Reminder'

3) Enclose the whole lot in a redundant "(SELECT ... FROM DUAL)" scalar subquery.

Obviously, this saved the developer from the tiresome task of typing 'Third', 'Fourth', 'Fifth' etc. And presumably he/she isn't aware of the CASE expression that could have reduced it to:

CREATE OR REPLACE VIEW reminders_view AS
SELECT reminder_id
, reminder_seq
, CASE WHEN reminder_seq = 1
          THEN 'No reminder'
      WHEN reminder_seq BETWEEN 3 AND 30
          THEN INITCAP(TO_CHAR(TO_DATE(TO_CHAR(reminder_seq)||'-MAY-2004','DD-MON-YYYY'),'ddspth'))||' Reminder'
      ELSE 'Reminded'
      END reminder_type
FROM reminders;

(And why stop at 30 when May has 31 days?!)

Well, it gave me a much needed laugh on a Friday afternoon, anyway.

All names have been changed to protect the guilty.

Pause for thought ....oracle

2008-08-06T22:12:00.001-07:00

Requirement:

Can I let a procedure wait for a specific time (10 seconds) before inserting values into a table?

One solution (according to a poster on OTN - don't try this at home):

declare
 dStart DATE := SYSDATE;
 nDiff  NUMBER;
begin
 dbms_output.put_line('dStart: '||TO_CHAR(dStart, 'dd.mm.yyyy hh24:mi:ss'));
 LOOP
   nDiff := (SYSDATE - dStart)*86400;
   EXIT WHEN nDiff >= 10;
 END LOOP;
 dbms_output.put_line('nDiff: '||nDiff);
 dbms_output.put_line('END: '||TO_CHAR(SYSDATE, 'dd.mm.yyyy hh24:mi:ss'));
end;
/

Or if you prefer,

create or replace function timeout
   return number
is
begin
   return to_number(to_char(sysdate,'SSSSS'));
end timeout;
/

Function created.

create or replace procedure tcal( t in number ) as
   a  number:=1;
   st number:=to_number(to_char(sysdate,'SSSSS'));
   x varchar2(12);
   y varchar2(12);
begin
   while(a<99999999) loop
       dbms_output.enable(100000); ---> Just to make loop busy...
       a := a + 1;
       exit when (timeout - st)>=t;
   end loop;

   x:=to_char(trunc(sysdate)+st/(24*60*60),'HH:MI:SS AM');

   dbms_output.put_line('       Started: '||x);

   y:=to_char(trunc(sysdate)+timeout/(24*60*60),'HH:MI:SS AM');

   dbms_output.put_line('       Ended:   '||y);
   dbms_output.put_line(timeout-st||' seconds reached...');
end ;
/

Procedure created.

Now that is all well and good, you may say, after all the cpu isn't doing anything else at the moment and these days they rarely catch fire, but couldn't we just use the supplied procedure DBMS_LOCK.SLEEP? Well apparently it is impractical. Those with a quiet afternoon to spare might like to follow the reasons why, at forums.oracle.com/forums/thread.jspa?threadID=402345.

The fine line between clever and stupid ....oracle

2008-08-06T22:10:00.002-07:00

Stojka Mongo began to be concerrned when he came across table AT_ILOAD_DAYS that looked like this:

CREATE TABLE at_iload_days
( daystring      VARCHAR2(8)
, batch_created  VARCHAR2(1)  DEFAULT 'N' );

where "daystring" contained values like "20030901".

Then he found this convenient procedure to "fill" it:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
   -- Procedure to fill days from most recent entry in at_iload_days up to current sysdate */
   d_date_string    VARCHAR2(8) DEFAULT NULL;
   d_maxdate_string VARCHAR2(8) DEFAULT NULL;
BEGIN
   -- sysdate will be max date to generate:
   d_maxdate_string := TO_CHAR(SYSDATE, 'yyyymmdd');

   -- Fetch most recent datestring from at_iload_days + 1 day into d_date_string
   -- (default to commercial launch date)
   EXECUTE IMMEDIATE
   'select to_char((to_date(nvl(max(daystring),''20030505''),''yyyymmdd'')+1),''yyyymmdd'') from at_iload_days'
   INTO d_date_string;

   -- We shouldn't load data that will exist in the future, should we?
   IF d_date_string <= d_maxdate_string THEN
       EXECUTE IMMEDIATE
       'insert into at_iload_days (daystring,batch_created) values (' ||
       '''' ||d_date_string || '''' || ',''N'')';

       EXECUTE IMMEDIATE 'COMMIT';
   END IF;
END pr_generate_iload_days;

Our favourite line:

EXECUTE IMMEDIATE 'COMMIT';

It's hard to tell but I think that procedure can be rewritten as:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
BEGIN
   INSERT INTO at_iload_days (daystring)
   SELECT TO_CHAR
          ( TO_DATE(NVL(MAX(daystring),'20030505'),'yyyymmdd')+1
          , 'yyyymmdd' )
   FROM   at_iload_days
   HAVING NVL(MAX(daystring),'20030505') <= TO_CHAR(SYSDATE, 'yyyymmdd')
END pr_generate_iload_days;

although if DAYSTRING had actually been a date, it would have just been:

CREATE OR REPLACE PROCEDURE pr_generate_iload_days
AS
BEGIN
   INSERT INTO at_iload_days(day)
   SELECT NVL(MAX(day), DATE '2003-05-05') +1
   FROM   at_iload_days
   HAVING NVL(MAX(day), DATE '2003-05-05') <= SYSDATE;
END pr_generate_iload_days;

Thanks 3360 for the title for this post.

Show Me The Money ....oracle

2008-08-06T22:10:00.001-07:00

Money, money, money. It's a rich man's world. It can't buy you love, but as we are constantly reminded it can buy you cheap Levitra and pre-approved loans, and those are just as good and will probably boost this site's stats. It makes the world go round. It's great - but how to format it?

Well, here is one way. The editing history tells the whole story.

/*=============================================================================
Procedure:    FN_FORMAT_DOLLARS
Description:  Formats a number as dollars (2 decimals)

MODIFICATION HISTORY:
Person        Date        Comments
---------     ------      -------------------------------------------
XX            01/24/03    Created
SF            08/05/03    Rewrote the function as a TO_CHAR statement
==============================================================================*/

FUNCTION fn_format_dollars (p_dollars IN NUMBER)
   RETURN VARCHAR2
IS
   -- v_dollars   VARCHAR2 (50);
BEGIN

   /*
   v_dollars := TO_CHAR (p_dollars);
   IF INSTR (v_dollars, '.') = 0
   THEN
       v_dollars :=  v_dollars  || '.';
   END IF;

   -- too many decimal places...
   WHILE   LENGTH (v_dollars) - INSTR (v_dollars, '.') > 2
   LOOP
       v_dollars := SUBSTR (v_dollars, 1, LENGTH (v_dollars) - 1);
   END LOOP;

   -- not enough decimal places...
   WHILE   LENGTH (v_dollars) - INSTR (v_dollars, '.') < 2
   LOOP
       v_dollars := v_dollars || '0';
    END LOOP;
   */

   RETURN TO_CHAR(p_dollars, 'FM999999999990.00');

END fn_format_dollars;

Many thanks to rd for sending this in.

Competition ....oracle

2008-08-06T22:09:00.001-07:00

Well, not really a competition because we haven't got any prizes to give away, but the code below (thanks Padders) has a real "How Many WTFs Can You Spot?" feel about it. I make it four.

PROCEDURE log_error
   ( p_source  IN  VARCHAR2
   , p_result  IN  VARCHAR2)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   p_primarykey errorlogging.primarykey%TYPE;
BEGIN
   SELECT errorlogging_seq.NEXTVAL
   INTO   p_primarykey
   FROM   dual;

   INSERT /*+ APPEND */ INTO errorlogging NOLOGGING
   ( primarykey
   , source
   , result
   , timestamp
   , wherewasi
   , processed_count
   , process_id )
   VALUES
   ( p_primarykey
   , p_source
   , SUBSTR(p_result, 1, 1000)
   , SYSDATE
   , NULL
   , NULL
   , 1 );

   COMMIT;
EXCEPTION
   WHEN OTHERS THEN NULL;
END;

Production-ready code ....oracle

2008-08-06T22:08:00.000-07:00

I'm not sure that there's much more to add to this forums.oracle.com thread.

A Year of WTFs ....oracle

2008-08-06T22:07:00.001-07:00

Each year, the Oracle WTF organisation celebrates its anniversary by inviting a fellow Oracle professional to share an evening of fine Eastern European lager and disappointing bar snacks in a pub where you can't hear each other properly, and this year it was the turn of former newbie (but now of course senior expert) DBA Lisa Dobson.

Actually that's not quite true - I realised today it was just over a year ago that we started the blog, and the other evening some of us met up with Lisa who happened to be in town, and if I'd thought of this a bit earlier I could probably have pretended it was all planned. Anyway it was nice meeting Lisa, and we enjoyed the story about the colleague who accidentally deleted the entire production server, as luck would have it a week after the server room air conditioning took out the only other server by dripping a surprisingly large amount of water into it. That rather outdid my story about the day we turned up for work at a client's site and found nobody could log in, because as it happened someone had stolen the servers during the night. Or the other one, if I'd remembered to tell it, about the high street retail chain whose backup system involved a PC and a timer plug. (I shouldn't laugh - it actually worked quite well.) It was also reassuring to hear we are not the only ones who don't understand a single bloody word of those "Oracle" blogs about installing a Java framework in JDeveloper to implement service-oriented BPEL with a right-click and some XML. Umm, neat.

It seems we're also not the only ones to have been shocked and saddened by Doug Burns' recent senseless killing spree. This monster must be stopped. (...is probably what Doug said as he reached for his copy of "Expert One-On-One Oracle").

Anyway, one year, 63 posts, and some lessons learned:

Post one of Tom Kyte's pet hates like WHEN OTHERS THEN NULL (or WHEN OTHERS THEN DBMS_OUTPUT, which isn't much better quite frankly) for a mention on his blog and a surefire thousand hits. We need one about IM Speak now b/c im sure u will C loadsa hits ;-)
Post something involving security for a mention on Pete Finnigan's site. We also get a steady stream of visitors googling for "forgot system password +oracle", "how to get password of a user in oracle" and so on, and being directed to Umm, I Forgot My Password, part 2. (In future just put it on a Post-It under your keyboard like everyone else.)
Titles involving common Oracle error codes seem to do well. A lot of visitors come here from a Google search for SP2-0552 (guys, you have a variable with a colon in front of it somewhere). We need some posts about ORA-0600 or TNSNAMES.
A lot of people seem to be searching for an explanation of joins and end up at our Joins Explained, heaven help them, where a Mr Sanders Kaufman explains things like "Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables." I'm glad we got that cleared up.
Google Analytics Rock. So (while we're at it) do Statcounter, Feedburner and CoComment.

We were recently offered some sponsorship by price comparison website Shopzilla (or rather, they emailed me a week ago and I remembered it in the pub). The consensus among those present was that we should not accept it, although personally I think all that would change if we could get The Daily WTF's Beanbag Girl.

We were also kindly offered a spot on Pythian Group's Logfile Of The Vanities or whatever it's called (or rather, they emailed me a month ago and I seem to have deleted the message, sorry guys, meant to get back to you) but I didn't take them up on it, partly because there didn't seem much point in writing a piece telling people about Oracle blogs they already read (surely?) via blogs.oracle.com, and also because it's explicitly for DBAs, and with the possible exception of Padders we are all developers (even though these days "developer" is often assumed to mean someone who is into aspect-oriented JDeveloper BPEL plug-ins). Our main thing is SQL, PL/SQL and data modelling, and AFAIK none of those have much to do with the DBA role. In fact, thinking about it I'm not sure why they asked. And is that Pie-thian as in Pythagoras and Python, or Pith-ian as in Gryffindor and Slytherin, anyway?

Just, Why? ....oracle

2008-08-06T22:06:00.000-07:00

I'm currently debugging a procedure of the form illustrated below, lovingly recreated using the ubiquitous scott.emp table. I'm also taking suggestions on what might've been going through the head of the person who wrote it.

RENAME emp TO emp_table
/
CREATE OR REPLACE VIEW emp
AS
  SELECT *
    FROM emp_table
/
CREATE OR REPLACE TRIGGER mod_emp
  INSTEAD OF UPDATE
  ON emp
  FOR EACH ROW
DECLARE
  new_emp                       emp_table%ROWTYPE;
  old_emp                       emp_table%ROWTYPE;
BEGIN
  new_emp.empno       := :NEW.empno;
  new_emp.ename       := :NEW.ename;
  new_emp.job         := :NEW.job;
  new_emp.mgr         := :NEW.mgr;
  new_emp.hiredate    := :NEW.hiredate;
  new_emp.sal         := :NEW.sal;
  new_emp.comm        := :NEW.comm;
  new_emp.deptno      := :NEW.deptno;
  old_emp.empno       := :OLD.empno;
  old_emp.ename       := :OLD.ename;
  old_emp.job         := :OLD.job;
  old_emp.mgr         := :OLD.mgr;
  old_emp.hiredate    := :OLD.hiredate;
  old_emp.sal         := :OLD.sal;
  old_emp.comm        := :OLD.comm;
  old_emp.deptno      := :OLD.deptno;
  update_emp (old_emp, new_emp);
END;
/

CREATE OR REPLACE PROCEDURE update_emp (
  old_emp                             emp_table%ROWTYPE
 ,new_emp                             emp_table%ROWTYPE
)
IS
BEGIN
  IF old_emp.empno != new_emp.empno
  THEN
     RETURN;   --can't update primary key attribute
  END IF;

  IF     old_emp.empno IS NOT NULL
     AND new_emp.empno IS NULL
  THEN
     DELETE FROM emp_table
           WHERE empno = old_emp.empno;

     RETURN;
  END IF;

  IF    (    old_emp.ename IS NULL
         AND new_emp.ename IS NOT NULL)
     OR (    old_emp.ename IS NOT NULL
         AND new_emp.ename != old_emp.ename)
  THEN
     UPDATE emp_table
        SET ename = new_emp.ename
      WHERE empno = old_emp.empno;
  END IF;
  -- ..
  -- ..
  -- ..
  IF    (    old_emp.deptno IS NULL
         AND new_emp.deptno IS NOT NULL)
     OR (    old_emp.deptno IS NOT NULL
         AND new_emp.deptno != old_emp.deptno)
  THEN
     UPDATE emp_table
        SET deptno = new_emp.deptno
      WHERE empno = old_emp.empno;
  END IF;
END;
/

Useful books ....oracle

2008-08-06T22:05:00.000-07:00

An enormous spider appears. It's clearly coming for you. Or not. What do you do?

Staying calm, place a glass or similar containment device over the creature.

Now gently slide a card under the glass, taking care not to trap any legs. There should be eight after you have completed this manoeuvre.

Transfer to a suitable platform such as a book, and carry to a window or other suitable release location.

No Oracle experts were harmed in the making of this blog.

Reintroduce the creature into the wild, where it can begin a new life free to roam among its own kind. Or just wander back in of course, but spiders are not known for their homing instinct. Or are they? To be honest we haven't checked. This one said something about "Scotland" and "revenge".

Finally, always sterilise your equipment.

Meanwhile in Edinburgh, police were called to a flat in the Stockbridge area following reports of a violent assault...

Death By Furniture ....oracle

2008-08-06T22:04:00.000-07:00

According to www.identifiers.org, there are two classes of relational database: "Code Class" and "Identifier Class".

We hadn't heard of those either, but it's all made clear in this presentation (pdf, 1.2MB), in which the limitations of the conventional approach and a novel approach to schema design are explained without the aid of Powerpoint, in a series of pictures like this one:

Still confused? Never mind, you can follow the debate on this OTN thread, which George begins by asking for a simple clarification regarding the capabilities of the Oracle RDBMS. From the answers to this, he should be able to determine whether Oracle is old-hat Code Class or funky new Identifier Class:

I have an interest in establishing how the Oracle System Catalogues cope with particular changes. I have never used Oracle, but I have carried out the same test on another RDBMS. If I had access to Oracle I would have carried out this test myself.

The test goes as follows.

Create a new database.

Create a new simple table, with just a few columns.

Create a form for the table, and add a few rows.

Rename the table or a column – if you can’t, then the RDBMS is Code Class.

If you can rename the table or a column, then do so, and invoke the form that you used before changing the table or column name. If it doesn’t work, the RDBMS is Code Class; if it does then the RDBMS is Identifier Class.

In an Identifier Class RDBMS changes of column or relation/table name will not interfere with the operation of any form already in place based on that table.

I'll be very grateful if anybody can give me a definitive answer on this, either through already having explored the issue or by running the test.

It turns out that "Code Class" covers all existing RDBMS products ever conceived, and "Identifier Class" is an improved model invented by George himself, in which some theoretical 4GL development tool yet to be designed allows you to change table and column names without breaking existing code or having to define a view, and surrogate keys are, well, pretty much the same except that they are now called attribute independent relative position independent identifiers. Perhaps one day Oracle will advance to this point, especially now that they've fixed DBMS_OUTPUT and must be wondering what to do next (perhaps after getting a product to work on Apple Mac and fixing the OTN "change password" facility). We can but dream.

Pause For Thought part 2 ....oracle

2008-08-06T22:03:00.000-07:00

In an AskTom thread about native compilation, a poster mentions that he is seeing some big CPU waits on his system and wonders whether native compilation would help. After some discussion of tracing and profiling and his 32 Tuxedo connections, he reports back that from talking to the programmer (possibly this guy), he might have narrowed the problem down to a procedure called DELAY():

I just happened to talk to the programmer and what i understand is that that specific function when called so many times , it is internally calling a procedure with passing a value n, where n is like 4,5,6 etv. That procedure is called delay and its job is to create a delay in the logic flow. So the delay procedure accepsts values like 10 as seconds and then it
FOR di IN 1..sed LOOP
  NULL;
END LOOP;
A random thought, could this when run so many times accumulate huge cpu?

Tom thinks maybe it might.

Thanks Tom for sending this in.

Code Re-use ....oracle

2008-08-06T22:02:00.000-07:00

This code was anonymised by the sender and some of the original logic may be a bit harder to make sense of as a result. Instead of EMP records, you should probably imagine it checking billion-dollar financial deals or nuclear reactor core temperature readings. Anyway, I think I've figured out what it's supposed to do.

You pass in an EMP record containing first name, last name, email address and so on, and it calls OTHER_PROC(email_address, the_emp_record_as_passed_in) - but only if the email address is not null, and it is unique for employees with that name. For some reason. But how to check? Simple:

Use a cursor to load all the rows for that name into an array. (Apparently there were 130+ columns in the original table.)
Check array.COUNT to see how many rows there are.
If there is only one row, use it, taking care to use an NVL expression because we definitely don't want a NULL email address.
If there is more than one row, open the same cursor again, and this time loop through it comparing each row's email address with the previous one. If it's the same, set lv_email_same = 1, otherwise set it to 0. That way, at the end of the loop we'll know whether they were all the same or not, right?

PROCEDURE unleash_havoc (p_emp_rec emp%rowtype)
IS
  lv_email_same  NUMBER(1) :=0; -- 0: no, 1: yes
  lv_email_null  NUMBER(1) :=0; -- 0: no, 1: yes
  lv_email       emp.email%TYPE := NULL;
  ln_row         NUMBER;

  TYPE emp_tab IS TABLE OF emp%ROWTYPE;
  lt_emp_data  emp_tab;

  CURSOR c_emp (p_last_name VARCHAR2, p_first_name VARCHAR2) IS
     SELECT *
     FROM   emp e
     WHERE  e.last_name = p_last_name
     AND    e.first_name = p_first_name;

BEGIN
  OPEN c_emp (p_emp_rec.last_name, p_emp_rec.first_name);
  FETCH c_emp BULK COLLECT INTO lt_emp_data;
  CLOSE c_emp;

  IF lt_emp_data.COUNT = 1 THEN
      ln_row := lt_emp_data.FIRST;
      lv_email  := NVL(lt_emp_data(ln_row).email,NULL);
      other_proc(lv_email,p_emp_rec);

  ELSIF lt_emp_data.COUNT > 1 THEN

      FOR r IN c_emp (p_emp_rec.last_name, p_emp_rec.first_name) LOOP
          IF NVL(r.email,'X') = NVL(lv_email,'X') THEN
              lv_email := r.email;
              lv_email_same := 1;
          ELSE
              lv_email := r.email;
              lv_email_same := 0;
          END IF;

          IF r.email IS NULL THEN
              lv_email_null := 1;
          ELSE
              lv_email_null := 0;
          END IF;
      END LOOP;

      IF  lv_email_same = 1
      AND lv_email_null = 0
      THEN
          lv_email  := NVL(lt_emp_data(ln_row).email,NULL);
          other_proc(lv_email,p_emp_rec);
      ELSE
          ...
      END IF;
  END IF;
END;

Short loop ....oracle

2008-08-06T22:01:00.000-07:00

It's good to see a GOTO every now and then. However, the sender of this one (thanks!) was most impressed by the loop that goes from 1 to v_totalcntr, and indeed the fact that there is a loop and a v_totalctr variable at all, with the variable carefully set from the cursor's %ROWCOUNT, when it can only ever have one value:

DECLARE
 CURSOR cur_pricing IS
     SELECT col1, col2
     FROM   sometable;

 var_pricing cur_pricing%ROWTYPE;

BEGIN
 OPEN cur_pricing;
 FETCH cur_pricing INTO var_pricing;

 IF cur_pricing%NOTFOUND THEN
     GOTO continue;
 END IF;

 v_totalcntr := cur_pricing%ROWCOUNT;

 FOR r IN 1..v_totalcntr
 LOOP
     -- loads of stuff here
     -- but no fetch from cur_pricing
     -- not even for the one time this loop will execute :-)
 END LOOP;

 <>
 NULL;
END;

Of course you could just fetch the value you want and process it, but where would be the fun in that?

PS The person who sent this in emailed me with a point I must admit hadn't occurred to me:

Saw you posted this one - thanks. But did you pick up on what they probably thought they were doing? I think whoever wrote it thought that %rowcount would have the TOTAL number of rows that the cursor would return - so they thought they would be looping around ALL the records in the cursor. The fact that they also forgot to fetch again in the loop just adds to the problem of course.

Performance Tuning and The Big O ....oracle

2008-08-06T22:00:00.000-07:00

Oracle Performance Tuning is a big subject, as anyone will appreciate who has read any of a number of books that set out to help you to understand what problem you are trying to solve, what factors may affect the performance you are seeing, what strategies are available to the query optimizer, and so on.

While Cary Millsap's Optimizing Oracle Performance focusses on finding, tracing and prioritising specific problems in the face of vague reports that the system seems a bit slow this week, and Jonathan Lewis' Cost-Based Oracle Fundamentals takes us on a tour of the CBO to help answer such questions as Why isn't my EXISTS query using an index? (and why isn't it faster than the IN version?) a poster on OraFAQ has an approach we've not seen before:

Hi,

The following is a problem I need help with and I am willing to pay for help if necessary. Any info would be greatly appreciated.

Two tables in a database:

Table1 contains a list of phone numbers
Table2 contains a list of phone numbers as well

I would like to create a Table3, in which Table3 contains all numbers from Table1 that is not in Table2. I am looking for the shortest runtime possible, keeping in mind that you can use whatever method(s) you deem necessary.

Table1 contains 30 Million rows,
Table2 contains 2000 rows.

given a regular SQL expression, it will yield Big O(m*n)

Where m = rowcount of Table1
and n = rowcount of Table2

Generate for me, a method in which, runtime will yield Big O (m log2 n).

I don't need code, I want to hear your logic. Table1 is customers, Table2 is a list of prepaid phone numbers. Table3 is list of people to bill.

As usual no database version is given. The first suggestion, as you might expect, is the quite reasonable:

select phone_no from table1
minus
select phone_no from table2;

accompanied by a comment that it doesn't seem like a great piece of schema design to have two tables in the first place. However, the reply comes back:

The guy who wrote this problem just told me that this answer is incorrect.

His response to me was:

it's not too simplistic, but it is incorrect. This will still yield a big O(m*n).

Give it one more try, you are thinking too much in terms of DB.

Ask yourself, what are the only structures that would yield BigO(nlog2n)? Answer that, and you will get your answer.

Any ideas?

Resident mathematician Ross Leishman tried to explain to me what Big O (m log2n) means, and I can confirm that it is not after all a When Harry Met Sally reference as most of us would probably assume. Apparently the version with an EXISTS subquery was what was wanted, which seemed odd to me on a number of levels, not least that an IN subquery would probably produce the same plan, especially in 10g where the new HASH JOIN RIGHT ANTI allows the database to build its hash table from the 2,000-row table2 rather than the 30 million-row table1. But of course we don't know the database version, do we? Or whether the columns are nullable, unique or indexed, or how values are distributed, or really anything about the actual environment that would help in solving a real-world performance problem. I can see where I'm going wrong though. I am thinking too much in terms of DB.

423,551 Invalid Indexes ....oracle

2008-08-06T21:59:00.000-07:00

A friend received an alarming email from a commendably proactive DBA who was concerned that he had discovered a potentially serious performance issue on the production system:

The following result shows there are many invalid indexes. For example, in the first row we see that there are nearly 423551 invalid indexes for just one row of a table. So if we rebuilt the index it would improve the performance.

Wow, that sure is a lot of invalid indexes for just one row of a table. For the sake of anonymity, I've run his query against the SCOTT/TIGER schema, where fortunately there are only 14 invalid indexes (or perhaps 10, if that's how you count them). Perhaps you can see what the problem might be:

SELECT DISTINCT
      t.table_name
    , i.index_name
    , t.num_rows table_rows
    , i.num_rows index_rows
FROM   dba_tables t
    , dba_indexes i
WHERE  t.table_name = 'DEPT'
AND    t.owner = 'SCOTT'
AND    i.owner = t.owner
AND    i.index_type = 'NORMAL';

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
DEPT                           PK_EMP                                  4         14
DEPT                           EMP_DEPT_IX                             4         14
DEPT                           PK_DEPT                                 4          4
DEPT                           PK_SALGRADE                             4          5

4 rows selected.

There is worse to come. It's not just DEPT that has invalid indexes. He goes on:

The following result shows there are many invalid indexes, so if we rebuild the index, it would improve the performance. At the same time from the first and last rows, indexes are not being used, so we need to look into it.

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
SALGRADE                       PK_DEPT                                 5          4
SALGRADE                       PK_EMP                                  5         14
SALGRADE                       EMP_DEPT_IX                             5         14
SALGRADE                       PK_SALGRADE                             5          5

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
EMP                            PK_DEPT                                14          4
EMP                            PK_EMP                                 14         14
EMP                            EMP_DEPT_IX                            14         14
EMP                            PK_SALGRADE                            14          5

I just can't help wondering what he thinks an index is.

Quote For The Day ....oracle

2008-08-06T21:58:00.000-07:00

"PL/SQL development can be a tedious and time-consuming job – often monopolizing the valuable time and efforts of Oracle developers." - SQL Navigator page, Quest Software

Spare a thought today for PL/SQL developers, having their precious time tediously monopolised by, umm, PL/SQL development.

DATE comparisons: the scenic route ....oracle

2008-08-06T21:57:00.000-07:00

Need to calculate the number of minutes between two dates? Yes, the dull way is to subtract one from the other and multiply by 1440. But why do that, when you can simply convert each date into Julian format by converting it into a string and then back to a date, and converting the resulting date into a Julian string, and (in a separate step) applying TO_NUMBER to the result to get an integer, so that you can simply subtract one from the other and multiply by 1440.

Except that rounds to the nearest day, so an additional step is to calculate the minutes since midnight for each of the two dates, which you can do by simply converting each date into an 'HH:MI AM' string, then back to a date, then back to an 'SSSSS' string, converting it to a number, dividing by 60 and adding it to the result of the first calculation. I think.

Anyway, consider the following library of handy date functions our Oracle WTF Easter gift to you, the online development community.

CREATE PACKAGE dates_pkg
AS
   FUNCTION julian_date
       ( date_to_convert DATE )
       RETURN NUMBER;

   FUNCTION minutes_since_midnight
       ( timevalue DATE )
       RETURN NUMBER;

   FUNCTION minutes_elapsed
       ( lowdate DATE
       , highdate DATE )
       RETURN NUMBER;

END dates_pkg;
/

CREATE PACKAGE BODY dates_pkg
AS
   FUNCTION julian_date
       ( date_to_convert DATE)
       RETURN NUMBER
   IS
       varch_value VARCHAR (10);
       num_value NUMBER (20);
   BEGIN
       SELECT TO_CHAR
              ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
              , 'J')
       INTO   varch_value
       FROM   dual;

       SELECT TO_NUMBER (varch_value)
       INTO   num_value
       FROM   dual;

       RETURN (num_value);
   END julian_date;


   FUNCTION minutes_since_midnight (
       timevalue DATE)
       RETURN NUMBER
   IS
       secs_elapsed NUMBER (20);
       mins_elapsed NUMBER (20);
   BEGIN
       SELECT TO_NUMBER
              ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
              , 'SSSSS') )
       INTO   secs_elapsed
       FROM   dual;

       SELECT (secs_elapsed / 60)
       INTO   mins_elapsed
       FROM   dual;

       RETURN (mins_elapsed);
   END minutes_since_midnight;


   FUNCTION minutes_elapsed
       ( lowdate DATE
       , highdate DATE )
       RETURN NUMBER
   IS
       final_number NUMBER (20);
       low_julian NUMBER (20);
       high_julian NUMBER (20);
       num_days NUMBER (20);
       num_minutes NUMBER (20);
       temp_mins NUMBER (20);
       min_low NUMBER (20);
       min_high NUMBER (20);
   BEGIN
       SELECT julian_date (lowdate)
       INTO   low_julian
       FROM   dual;

       SELECT julian_date (highdate)
       INTO   high_julian
       FROM   dual;

       SELECT (high_julian - low_julian)
       INTO   num_days
       FROM   dual;

       SELECT (num_days * 1440)
       INTO   num_minutes
       FROM   dual;

       SELECT minutes_since_midnight (lowdate)
       INTO   min_low
       FROM   dual;

       SELECT minutes_since_midnight (highdate)
       INTO   min_high
       FROM   dual;

       SELECT (min_high - min_low)
       INTO   temp_mins
       FROM   dual;

       SELECT (num_minutes + temp_mins)
       INTO   final_number
       FROM   dual;

       RETURN (final_number);

   END minutes_elapsed;
END dates_pkg;

Just for fun, let's test it:

CREATE TABLE wtf_test (start_date NOT NULL, end_date NOT NULL) AS
SELECT DATE '2006-12-25' + DBMS_RANDOM.VALUE(1,365)
    , DATE '2007-12-25' + DBMS_RANDOM.VALUE(1,365)
FROM   dual CONNECT BY LEVEL <= 1000;

-- ...several runs here to allow for caching etc, last set of results shown...

SQL> set timing on autotrace traceonly stat

SQL> SELECT dates_pkg.minutes_elapsed(start_date,end_date) FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:03.96

Statistics
----------------------------------------------------------
 16000  recursive calls
     0  db block gets
    74  consistent gets
     0  physical reads
     0  redo size
  9330  bytes sent via SQL*Net to client
   809  bytes received via SQL*Net from client
    68  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
  1000  rows processed

SQL> SELECT (end_date - start_date) * 1440 FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:00.16

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
    74  consistent gets
     0  physical reads
     0  redo size
 25485  bytes sent via SQL*Net to client
   809  bytes received via SQL*Net from client
    68  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
  1000  rows processed

So the handy package version takes 25 times as long as the 1-line SQL version.

And in the interests of fairness, in case you're thinking perhaps that is just the normal overhead of calling PL/SQL functions in SQL, let's try our own function:

CREATE FUNCTION minutes_elapsed
   ( lowdate DATE
   , highdate DATE )
   RETURN NUMBER
AS
BEGIN
   RETURN (highdate - lowdate) * 1440;
END minutes_elapsed;
/

SQL> SELECT minutes_elapsed(start_date,end_date) FROM wtf_test;

1000 rows selected.

Elapsed: 00:00:00.26

Statistics
----------------------------------------------------------
    21  recursive calls
     0  db block gets
   107  consistent gets
     0  physical reads
     0  redo size
 25496  bytes sent via SQL*Net to client
   809  bytes received via SQL*Net from client
    68  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
  1000  rows processed

Still 15 times faster.

Many thanks to Padders for sharing this one.

One liner ....oracle

2008-08-06T21:56:00.000-07:00

I was untangling a query when I came across eight variations of this code where only the literals 'a' to 'e' changed. Two of these were nested within an additional NVL so that the second would execute if the first returned null.

nvl(decode(2, 1, 'a', 2, 'b', 3, 'c', 4, 'd', 5, 'e', ' '), 'na')

Welcome back ....oracle

2008-08-06T21:55:00.000-07:00

Our guest administrator "Splogger" has now left the building, along with his page of helpful links to items on Amazon.com and a range of gentlemen's health products.

Suspiciously, a couple of days before he arrived we were taken off air by Blogger's spambots, presumably alerted by the amount of irrelevant, repetitive, and nonsensical text and links to Viagra sites they found here. From what I read, it seems possible that the Blogger automated suspension to prevent blog spam might have actually left the account vulnerable to blog spammers. As ironies go, that is up there with rain on your wedding day and good advice that you just didn't take.

What is this "testing" thing? ....oracle

2008-08-06T21:54:00.000-07:00

Recently asked on the OTN PL/SQL forum:

I am reading a book: Learning Oracle PL/SQL by Bill Pribyl and Steven Feuerstein.
I am a newbie of PL/SQL and I got no other programming XPs. but in the very first of this book they are introducing something called: testing, to make some test programms /utilites.
I found that is very hard to make, is it neccessary?

Apparently, after you've typed the final semicolon, you can either sit back and open a beer with the satisfaction of a job well done, or you can check whether the thing works. And apparently it's hard, so, do we really have to?

Well, another poster was recently arguing that PL/SQL is not suited to unit testing anyway, or refactoring either for that matter, because it isn't object oriented. I think that will be my excuse from now on. You want me to test stuff, go make PL/SQL more objecty.

Frameworkia ....oracle

2008-08-06T21:53:00.000-07:00

We thought long and hard about possible titles for this new PL/SQL development standard proposed on OTN, but we couldn't improve on the one it came with.

I want share a new IDEA to create a new standard PL/SQL developing:
Function ( Standard Buffer) return number
   variable Number;
   variable1 Varchar2;
begin
   variable := get from Standard Buffer;
   variable1 := get from Standard Buffer;

   { make your business }

   put in standard buffer your results
end;
Give me feedback if you are interested at the new STANDARD called "FRAMEWORKIA".

A lot possibilities are ready.

Do you see the genius of it?

Er, no.

Sensing that there were people who still needed convincing, user601181 posted some sample code developed using the new Frameworkia:

CREATE OR REPLACE FUNCTION iacore
   ( eobufferia IN OUT typeeodata )
   RETURN NUMBER
IS
   CURSOR cur_getroutingcondition
       ( idc_workflow IN VARCHAR2
       , idc_operation_node IN VARCHAR2 ) IS
       SELECT *
       FROM   wf_condition
       WHERE  id_workflow = idc_workflow
       AND    id_operation_node = idc_operation_node;

   rec_getroutingcondition cur_getroutingcondition%ROWTYPE;

   CURSOR cur_dobufferiaassign
       ( idc_workflow IN VARCHAR2
       , idc_operation_node IN VARCHAR2 ) IS
       SELECT *
       FROM   wf_assignement
       WHERE  id_workflow = idc_workflow
       AND    id_operation_node = idc_operation_node;

   rec_dobufferiaassign cur_dobufferiaassign%ROWTYPE;

   next_node         NUMBER;
   next_node_ck      NUMBER;
   stop_node         NUMBER;
   operation         VARCHAR2(256);
   operation_call    VARCHAR2(256);
   type_node         VARCHAR2(32);
   workflow          VARCHAR2(32);
   line              VARCHAR2(256);
   status_wf_v       VARCHAR2(3);
   pid_chain_node    NUMBER;
   ia_tid            VARCHAR2(64);
   ia_tid_micro      VARCHAR2(64);
   ret_code_default  NUMBER;
   ret_code          NUMBER;
   retval1           NUMBER;
   statementexc      VARCHAR2(256);
   schema_function   VARCHAR2(32);
   package_function  VARCHAR2(32);
   dblink_function   VARCHAR2(32);
   first_node_flag   VARCHAR2(2) := 'NO';
   id_debug_source   NUMBER;
   mapin_keyp        VARCHAR2(1024);

   headerbufferia typebufferia;
   assignbufferia typebufferia;
   checkbufferia  typebufferia;

   rec_wfnode               wf_node%ROWTYPE;
   rec_wffunctionsourcecode wf_function_source_code%ROWTYPE;
   rec_wflogger             wf_logger%ROWTYPE;
   rec_wfbusiness           wf_business%ROWTYPE;
   rec_wffieldmapping       wf_fieldmapping%ROWTYPE;
BEGIN
   headerbufferia := eobufferia(1);

   workflow := frameworkia.getvalue(headerbufferia,'ID_WORKFLOW');

   ---- DETERMINO QUALE NODO INVOCARE
   pid_chain_node := frameworkia.getvalue(headerbufferia,'WF_NODE_ID');

   ----- SE IL NODO E' NULL ALLORA E' IL PRIMO NODO
   IF pid_chain_node IS NULL
   THEN
       -------DETERMINO HANDLER E FILENAME PER IL LOGGER
       SELECT *
       INTO   rec_wflogger
       FROM   wf_logger
       WHERE  id_workflow = workflow;
       -- rec_WfLogger.ID_WORKFLOW
       -- rec_WfLogger.ID_DEBUG_LEVEL
       -- rec_WfLogger.ID_DIRHANDLER
       -- rec_WfLogger.ID_FILENAME

       --------INSERISCO NELL'HEADER
       frameworkia.setvalue
       ( headerbufferia
       , 'ID_DEBUG_WF'
       , rec_wflogger.id_debug_level );

       frameworkia.setvalue
       ( headerbufferia
       , 'ID_DIRHANDLER'
       , rec_wflogger.id_dirhandler );

       frameworkia.setvalue
       ( headerbufferia
       , 'ID_FILENAME'
       , rec_wflogger.id_filename );

       frameworkia.setvalue
       ( headerbufferia
       , 'CHARACTER_EVIDENCE'
       , '§§§§§§§§§§§§§§§§§§§§' );

       -------DETERMINO L'ID NODE
       SELECT wf_node_id
       INTO   pid_chain_node
       FROM   wf_node
       WHERE  id_workflow = workflow
       AND    wf_first_node = 'YES';

       SELECT *
       INTO   rec_wfnode
       FROM   wf_node
       WHERE  id_workflow = workflow
       AND    wf_first_node = 'YES';

       frameworkia.setvalue
       ( headerbufferia
       , 'WF_NODE_ID'
       , rec_wfnode.wf_node_id );

       SELECT b.status
       INTO   status_wf_v
       FROM   wf_node a
            , wf_name b
       WHERE  a.id_workflow = workflow
       AND    a.wf_node_id = rec_wfnode.wf_node_id
       AND    a.id_workflow = b.id_workflow;

       IF status_wf_v = 'OFF'
       THEN
           RETURN -1;
       END IF;

       ia_tid := frameworkia.getvalue(headerbufferia,'IA_TID');
       ret_code_default := 0;
       ret_code         := 0;

       frameworkia.setvalue
       ( headerbufferia
       , 'RET_CODE_DEFAULT'
       , ret_code_default );

       frameworkia.setvalue
       ( headerbufferia
       , 'RET_CODE'
       , ret_code);

       IF ia_tid IS NULL
       THEN
           ia_tid := 'TIA' || dbms_random.STRING('U',1 * 1 + 6) ||
                     TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF6');

           frameworkia.setvalue
           ( headerbufferia
           , 'IA_TID'
           , ia_tid );
       END IF;

That's just the first hundred lines, and I've formatted it. The complete iacore function was well over 600 lines long.

I for one welcome the new standard.

WREAK_APPLICATION_HAVOC ....oracle

2008-08-06T21:52:00.000-07:00

Tom Kyte recently blogged about the senseless and yet strangely common practice of coding something like this:

WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20001,'Following Error Occured:' || SQLERRM);

which fairly obviously achieves nothing except take a standard message like this:

ORA-06501: PL/SQL: program error
ORA-06512: at line 6

and pointlessly scramble it into this:

ORA-20001: Following Error Occured:ORA-06501: PL/SQL: program error
ORA-06512: at line 11

which adds some meaningless text, hides the original line number, and miss-spells "occurred". Not bad for a day's work.

It turned out that some people had been doing this for years because they were simply too stupid to realise that they didn't have to.

Anyway you know all this because you read Tom Kyte's blog. But have a look at this helpful page of advice from Tech On The Net, under "Oracle/PLSQL: SQLERRM Function"

You could use the SQLERRM function to raise an error as follows:

EXCEPTION
  WHEN OTHERS THEN
     raise_application_error
     (-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you could log the error to a table as follows:

EXCEPTION
  WHEN OTHERS THEN
     err_code := SQLCODE;
     err_msg := substr(SQLERRM, 1, 200);

     INSERT INTO audit_table (error_number, error_message)
     VALUES (err_code, err_msg);
END;

Now that is even better. The first example adds some meaningless text, hides the original line number, and duplicates the error code (unless it's a NO_DATA_FOUND exception, but let's not go there), to produce something like this:

ORA-20001: An error was encountered - -6501 -ERROR- ORA-06501: PL/SQL: program error
ORA-06512: at line 11

The error logging example pointlessly captures SQLCODE (nobody will ever use it), throws away all but the first 200 characters of the error stack, logs nothing about what happened, and fails to re-raise the exception so if you don't check the log you won't know anything went wrong until your customers start asking where their stuff is.

Wouldn't it be great if there were, say, a Boolean third parameter to RAISE_APPLICATION_ERROR that would make it retain the existing error stack, freeing up the message line for you to put something intelligent and helpful, like, I don't know,

BEGIN
   RAISE program_error;
EXCEPTION
   WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR
       ( -20001
       , 'Biscuits cannot be ordered on a ' || TO_CHAR(SYSDATE,'fmDay') ||
         ' without a hot beverage'
       , TRUE);
END;

to produce something like this:

ORA-20001: Biscuits cannot be ordered on a Sunday without a hot beverage
ORA-06512: at line 5
ORA-06501: PL/SQL: program error

We can but dream.

Fine tuning ....oracle

2008-08-06T21:51:00.000-07:00

Mike is doing some work on an application that started life as SQL Server. Now that it has been converted to Oracle, there are one or two bits that could still do with a little fine tuning.

One particular procedure seemed to take rather a lot of time, and several developers had tried to get better performance out of it without much success. Here it is:

CREATE OR REPLACE PROCEDURE getupdatedrunids
   ( p_customer_id  IN VARCHAR2 DEFAULT NULL
   , pc_results_out IN OUT SYS_REFCURSOR )
AS
   v_fetch_status     INTEGER := 0;
   v_sql_status       INTEGER;
   v_fetch_status1    INTEGER := 0;
   v_sql_status1      INTEGER;
   v_event_id         VARCHAR2(50);
   v_runid            NUMBER(10, 0);
   v_count_run_conns  INTEGER := 0;
   v_temp_runs        INTEGER;

BEGIN
   DELETE FROM temp_runs;

   DELETE FROM temp_run_connections;

   INSERT INTO temp_runs
        ( run_id )
   SELECT DISTINCT i.run_id
   FROM   event_status i
        , run_status    b
   WHERE  i.run_id = b.run_id
   AND    i.event_id IN
          ( SELECT DISTINCT i.event_id
            FROM   event_status i
                 , run_status   b
            WHERE  i.run_id = b.run_id
            AND    b.customer_id = p_customer_id
            GROUP  BY i.event_id
            HAVING COUNT(i.run_id) > 1)
            AND    b.customer_id = p_customer_id;

   BEGIN
       v_temp_runs := 0;
       SELECT COUNT(*)
       INTO   v_temp_runs
       FROM   dual
       WHERE  EXISTS
              ( SELECT *
                FROM   temp_runs );
   END;

   IF v_temp_runs > 0 THEN
  
       DECLARE
           CURSOR c_runs IS
               SELECT DISTINCT run_id
               FROM   temp_runs;
       BEGIN
           OPEN c_runs;
           FETCH c_runs INTO v_runid;

           IF c_runs%NOTFOUND
           THEN
               v_sql_status1   := 2;
               v_fetch_status1 := -1;
           ELSE
               v_sql_status1   := 0;
               v_fetch_status1 := 0;
           END IF;
      
           WHILE v_fetch_status1 = 0
           LOOP
               BEGIN
                   DECLARE
                       CURSOR cust_incidents_cs IS
                      
                           SELECT DISTINCT i.event_id
                           FROM   event_status i
                           WHERE  i.run_id = v_runid
                           AND    i.rejected = 0;
                   BEGIN
                       OPEN cust_incidents_cs;
                  
                       FETCH cust_incidents_cs INTO v_event_id;

                       IF cust_incidents_cs%NOTFOUND
                       THEN
                           v_sql_status   := 2;
                           v_fetch_status := -1;
                       ELSE
                           v_sql_status   := 0;
                           v_fetch_status := 0;
                       END IF;

                       <>
                       WHILE v_fetch_status = 0
                       LOOP
                           BEGIN
                               INSERT INTO temp_run_connections
                                    ( run_id
                                    , connectedids)
                               SELECT DISTINCT v_runid
                                    , i.run_id AS connectedids
                               FROM   event_status i
                               WHERE  i.run_id < v_runid
                               AND    i.event_id = v_event_id
                               AND    i.rejected = 0
                               AND    i.run_id IN
                                      ( SELECT DISTINCT run_id
                                        FROM   temp_runs );

                               <>
                               FETCH cust_incidents_cs INTO v_event_id;

                               IF cust_incidents_cs%NOTFOUND
                               THEN
                                   v_sql_status   := 2;
                                   v_fetch_status := -1;
                               ELSE
                                   v_sql_status   := 0;
                                   v_fetch_status := 0;
                               END IF;
                           END;
                       END LOOP;

                       CLOSE cust_incidents_cs;
                   END;
              
                   FETCH c_runs INTO v_runid;

                   IF c_runs%NOTFOUND
                   THEN
                       v_sql_status1   := 2;
                       v_fetch_status1 := -1;
                   ELSE
                       v_sql_status1   := 0;
                       v_fetch_status1 := 0;
                   END IF;
               END;
           END LOOP;
           CLOSE c_runs;
       END;
   END IF;

   SELECT COUNT(*)
   INTO   v_count_run_conns
   FROM   temp_run_connections;

   IF v_count_run_conns > 0
   THEN
       OPEN pc_results_out FOR
           SELECT DISTINCT run_id, connectedids
           FROM   temp_run_connections;
   END IF;

END getupdatedrunids;

His final version was much faster. See if you can spot the difference:

CREATE OR REPLACE PROCEDURE getupdatedrunids
   ( p_customer_id  IN VARCHAR2 DEFAULT NULL
   , pc_results_out IN OUT SYS_REFCURSOR )
AS
BEGIN
   OPEN pc_results_out FOR
       SELECT DISTINCT e2.run_id, ic.run_id AS connectedids
       FROM   run_status r1
            , run_status r2
            , event_status e1
            , event_status e2
       WHERE  r1.customer_id = p_customer_id
       AND    r2.customer_id = r1.customer_id
       AND    e1.run_id = r2.run_id
       AND    e1.rejected = 0
       AND    e2.run_id = r1.run_id
       AND    e2.event_id = e1.event_id
       AND    e2.run_id > e1.run_id
       AND    e2.rejected = 0
       ORDER BY 1, 2;

END getupdatedrunids;

Auxiliary Constructs Appeal ....oracle

2008-08-06T21:50:00.000-07:00

Will somebody give this guy some auxiliary constructs? He just needs to know what's the auxiliary constructs, and examples in the auxiliary constructs. So if you have any auxiliary constructs you don't need, now's the time to dig deep. The appeal starts here.

TGI g_friday ....oracle

2008-08-06T21:48:00.000-07:00

Found in a package body:

g_friday CONSTANT VARCHAR2(6) := 'Friday';

...then a couple of hundred lines later:

if to_char(business_date,'fmDay') = g_friday then
   ...end-of-week processing...
else
   ...regular processing...
end if;

Now that's flexible. If end-of-week processing is ever moved to the weekend, all you have to do is set g_friday := 'Saturday'.

BI for the Masses - is here! (BI SE1)

2008-07-14T05:50:00.000-07:00

Ever thought what you could get for $5,000? Five thousand dollars? Apart from a year's worth of mortgage payments on my apartment (and that would also change depending on the currency rate fluctuations - mind you my loan is rupee denominated, I am just getting a bit carried away with this analogy), I can think of Oracle Business Intelligence. Yes. Oracle Business Intelligence Suite Standard Edition One is now available for download from OTN. Let's just call it BI SE1 and forget for a minute all the branding and marketing people who may get very, very angry with me for messing up with the branding of the product (there are others also who I may piss off, but अब किसे याद रखूँ और किसे भूलूं, आख़िर दुनिया भर का ठेका तो नहीँ लिया है मैने|)।

OK, so BI SE1 is available for download from OTN। I already said that, didn't I? Right. Where can you get it? From the OTN download page, where else? Here.
And just what exactly does this BI SE1 package contain? The kitchen sink, almost.

Oracle Database Standard Edition One (version 10.2)
Oracle Warehouse Builder (core ETL only) (version 10.2)
Oracle Business Intelligence Answers (version 10.1.3.2.1)
Oracle Business Intelligence Dashboard
Oracle Business Intelligence Publisher
Oracle Business Intelligence Server
Oracle Business Intelligence Server Administrator

Each of these products is best-in-class, mind you. The Oracle Database, of course. Oracle Warehouse Builder is recognized as a leader among ETL tools. And BI Suite Enterprise Edition is a big reason why so many analysts, including Gartner, have recognized Oracle as a leader in BI Platforms. BI Publisher is a world class, performant, scalable, and mighty easy to use enterprise class reporting product. So there!

And what did I mean by the $5000 figure? I was lying, to be truthful. BI SE1 is licensed at $1000 per user, with a minimum of five users. So that means that the actual pricing for the entire kitchen sink is $1000!! But of course you would want to share the wonders of this product with more than just one user, right? Of course you would!

To download the product, go to this link

There are two tiny files that need to be downloaded. How tiny are we talking? Oh, just about 2.9GB (3,073,149,672 bytes to be precise). That shall take some time.

While you wait for these tiny files to download, you may want to check the doc library for this suite. That would be this link.

As you would see, apart from the regular doc on the Oracle Database, Oracle Warehouse Builder, and Oracle BI Publisher, there is one doc introducing BI SE1, and a bunch of tutorials.

Click the 'Getting Started' tab, and there are at least two other links of interest:

Business Intelligence Standard Edition One Install Guide (HTML, PDF)
Business Intelligence Standard Edition One Tutorial (HTML, PDF). The tutorial is a 226 page document that covers all topics you would expect, like how to setup the tutorial data mart, the BI repository, how to analyze data, publish reports, create Dashboards)

Never enough space - dwitiya

2008-07-14T04:53:00.000-07:00

I really, really need to do some cleaning up... (no, not personal hygiene, but that too).
See my earlier post for some context (There is never too much email space)

Similar sounding names can be confusing

2008-07-14T04:51:00.000-07:00

As I remarked in my earlier post, I am no branding expert (gold medal from IIM Bangalore notwithstanding - heh heh... I had to say that), so I cannot comment on why the names for the three editions of Oracle Business Intelligence were chosen the way they are:

Oracle BI Standard Edition One
BI Standard Edition (SE)
BI Suite Enterprise Edition (EE)

Actually, to be fair, I can comment with some insight into these names... These naming conventions are also in use by the Oracle Database and the Oracle Application Server.
The Oracle Database 10g is available in these editions: Standard Edition, Standard Edition One, Edition, and also the free Express Edition.
The Oracle Application Server 10g is available in the following editions (link): Enterprise Edition, Standard Edition, Standard Edition One, and Java Edition.

Therefore, goes the thinking, BI should also follow the same convention. Which it does. The BI SE1 bundle is a subset of the BI EE bundle (it is missing Delivers for example), but with some additional products packaged (like the Oracle Database and Oracle Warehouse Builder), and with a lower price entry point. We don't have an 'Enterprise Edition One' bundle in either the Oracle Database or the Oracle Application Server, so to have a BI EE1 name may have confused people, though my **personal** preference would have been to name it so. Yeah, who cares about my opinion, personal or otherwise?

BI Standard Edition is unchanged: Discoverer is an integral part of BI SE, and will continue to do so.

Hope that clarifies...

And while on the subject of BI SE1, here is what the installer screen looks like: a native OUI screen, with some tweaks...

I am trying to find a machine where I can install all of this... I have BI SE 10.1.2 installed on one, BI 10.1.3.3.0 installed on the same machine and also on my work laptop, so I am pretty much looking at installing this on a VMWare image. If I get to it soon enough I shall do a post, else there are always the tutorials, OBEs, and the doc.

Updated Discoverer Statement of Direction

2008-07-14T04:50:00.000-07:00

Mike Durran, product manager for Discoverer and BI SE, has posted an updated statement of direction for Discoverer on OTN. Get the PDF here.

Latest Discoverer Statement of Direction

2008-07-14T04:48:00.000-07:00

The latest Discoverer Statement of Direction is now available from OTN:

http://www.oracle.com/technology/products/discoverer/index.html

or a direct link to the document:

http://www.oracle.com/technology/products/discoverer/pdf/discoverer_sod_2007.pdf

There will also be a session at the ODTUG conference next week in Daytona Beach covering the Discoverer roadmap and plans to integrate with Oracle BI Enterprise Edition.

Hope to see you there !

Interesting issue when downloading BI SE1

2008-07-14T04:47:00.000-07:00

When downloading BISE1 I faced an interesting problem. After some initial puzzlement I figured out it has something to do with IE (Internet Explorer).

There are two files you have to download for BISE1 from OTN:

http://download.oracle.com/otn/nt/ias/101321/biseone_windows_x86_101321_dvd.z01
and
http://download.oracle.com/otn/nt/ias/101321/biseone_windows_x86_101321_dvd.zip

These two files have the same name, except for the extension. The first one is "z01" and the second "zip".

In Firefox, the file extensions are preserved the way they are, so that the two files are distinct when downloaded. No problem here - see the screenshot below.

But when trying to download it using IE, the extension is changed to .zip

IE for some reason decides that z01 should really map to zip, so that downloading both files results in one being overwritten.
So the solution, workaround, is to download them using the "Save As" option, manually specify the extensions to avoid this problem...

Wonder why this is happening why the way it is - this happened to me on two separate machines, and at least one other person from the field faced the same issue.

CRN Article on BI SE1

2008-07-14T04:46:00.000-07:00

CRN has this article on the Oracle Business Intelligence Suite Standard Edition One launched earlier this month (also see my earlier posts - this and this).

What no GUI? How to Build Analytic Workspaces using scripts

2008-07-14T04:41:00.000-07:00

What no GUI? How to Build Analytic Workspaces using scripts

Recently on the OLAP OTN forum there have been a number of questions regarding the use of scripting to control the design, build and maintenance procedures for analytic workspace. There are many reasons for using scripting rather than using Analytic Workspace Manager to build and maintain an AW. Many DBAs prefer to use scripts because they can be scheduled and/or quickly and easily moved from one platform to another without having to install any GUI components. Most likely scenario is that for the production environment a direct connection from a GUI client tool is not allowed. Obviously if you are using Warehouse Builder this is not a problem as OWB will automatically generate deployment and maintenance scripts that do not require execution via the GUI. However, when you are just using AWM things can get a little complicated.

As there is so much information to cover I am going to break this into a series of postings. The purpose of this one is to provide a brief summary of some of the ways to create and define objects within an AW from the command line.

Part 1 : How to create a new AW

It is possible to create an AW from the command line by using the AW_ATTACH procedure. The AW_ATTACH procedure attaches an existing analytic workspace to your SQL session so that you can access its contents. The analytic workspace remains attached until you explicitly detach it, or you end your session. AW_ATTACH can also be used to create a new analytic workspace, but the AW_CREATE procedure is provided specifically for that purpose.

DBMS_AW.AW_ATTACH (
awname IN VARCHAR2,
forwrite IN BOOLEAN DEFAULT FALSE,
createaw IN BOOLEAN DEFAULT FALSE,
attargs IN VARCHAR2 DEFAULT NULL,
tablespace IN VARCHAR2 DEFAULT NULL);

Notice using the scripting approach it is possible to set the default tablespace for the AW, a feature that is missing from the GUI. For example, the next SQL call creates the AW GLOBAL_PROGRAMS as the last user-owned analytic workspace in tablespace USERS:

SQL>EXECUTE DBMS_AW.AW_ATTACH('global_programs', true, true, 'last', ‘USERS’);

Alternatively you can go a step further and use the AW_CREATE command, which provides more control in some areas and less control in others. The AW_CREATE procedure creates a new, empty analytic workspace.

DBMS_AW.AW_CREATE (
awname IN VARCHAR2 ,
tablespace IN VARCHAR2 DEFAULT NULL ,
partnum IN NUMBER DEFAULT 8);

For example, the next command creates an analytic workspace named GLOBAL_AW. The resulting table AW$GLOBAL_AW will have two partitions and will be stored in the GLOBAL tablespace.

SQL>EXECUTE DBMS_AW.AW_CREATE('global_aw', 'global', 2);

With the AW_CREATE command it is possible to control the number of partitions, which is a very useful feature. But you do lose some control over where the AW is created in the attach list. This is only important if you have multiple AWs attached during the creation process. So why do we have two different procedures to create AWs? I have no idea but I am sure it seemed like a good idea at the time.

Now we have an empty AW, the next step is to start adding objects, possibly some data as well, to the AW. There are two options:

Import objects and data from an existing EIF file
Import objects based on existing XML documents

Part 2a - Importing an existing EIF file

If you already have an EIF file from an existing AW, the next step is import that file into your new AW. Ideally, the EIF file will contain all the required standard form metadata. If this is not the case and the EIF file is from an older version of Oracle OLAP, or Oracle Express, then you will need to have a different approach. Another blog entry will deal with the upgrade process.

To run the import process you need to place the EIF file in a directory on the file system then make a directory within the database to point to that filestore. We can use OLAP DML commands wrapped within the DBMS_AW.EXECUTE procedure to import the EIF file from the command line. Personally I prefer to make two passes through the EIF file, firstly to create the objects and then to load the data. This goes back to the good old days of Express and ensured efficient storage within the database files. Not sure if this still applies to 10g OLAP schemas, but as old habits die hard, here is the two pass process:

SQL>execute dbms_aw.execute ('aw attach name_of_analytic_workspace');

SQL>execute dbms_aw.execute ('cda scripts');

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' dfns');

SQL>execute dbms_aw.execute ('update');

SQL>execute dbms_aw.execute ('commit');

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data');

SQL>execute dbms_aw.execute ('update');

SQL>execute dbms_aw.execute ('commit');

The assumption here is the EIF file being used contains objects defined in standard form. If this is not the case then you will need to run a conversion process to generate standard form metadata.

Some warning signs need to set here : The import process above is going to load data into the analytic workspace with no commit point until the very end. Therefore, you need to make sure you have plenty of storage space within your TEMP tablespace, otherwise it is likely the import will fail with one of a number of error messages relating to memory and/or temp space issues. It is possible to control the update process by using the UPDATE keyword. For example:

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data UPDATE');

Will force an update after each object has been loaded. This is fine until you hit a very large cube when you may need slight more detailed control over the update process. The EIFUPDBYTES option lets you define the volume of data to read between updates. The following command will update after each block of 500,000 bytes has been loaded.

SQL>execute dbms_aw.execute ('EIFUPDBYTES = 500000’

SQL>execute dbms_aw.execute ('import all from eif file ''global.eif'' data UPDATE');

Obviously, by executing continual updates during the import the whole process will take much longer to complete than a single read with a final commit point at the end. But in the end “you pays your money and takes your choice” as they say.

One major problem with EIF imports is that most people create a single EIF file, which creates problems if you need to recover from a failed import process. Why? Because you have to start right at the beginning and import the whole thing all over again. Or you would think! The EIF file is in fact quite smart in terms of its contents. It is possible to get a list from the file of the actual contents so in reality it is possible to recover from a failed import process by checking what was imported and now resident in the AW and what is left to import from the EIF file. Requires a bit of OLAP DML skill…something for another blog posting perhaps?

Part 2b - Importing an existing XML definition

Each of the create procedures described above generates a completely blank AW. There is no standard form metadata contained within the resulting AW. Before you can import an XML template into the AW, standard form metadata needs to be added. The only way to do this seemed to be to use AWM (or right your own Java procedure to call the AWXML API). After a bit of digging I did discover the following an undocumented features called create_db_stdform. This is referenced in older versions of the OLAP documentation set and the syntax is as follows:

call create_db_stdform('my_aw_name','rw')

There is more information on Mark Rittman’s excellent blog website, where one of his posts covers migrating from previous versions of Express to 9i OLAP. The full article is on Mark’s website at:

http://www.rittmanmead.com/2003/10/30/creating-database-standard-form-analytic-workspaces/

To execute this command we can use the normal process as follows:

SQL>execute dbms_aw.execute ('call create_db_stdform(''global_aw'' ''rw'');

Now the AW will appear in AWM under the Model View and it is now possible to start creating dimensions and cubes. However, the purpose of this blog is to explain how not to use the GUI!
The next stage is to use existing XML template documents to define the objects within the AW. As far as I can tell there are no command line tools to create an AW XML definition for an existing object in an existing AW. The only way to do this is to use the Analytic Workspace Manager and the right mouse click menu options, or use the Generate feature in Warehouse Builder (Unless someone knows how to do this from the command line?).

So, assuming you have all the required XML files to create the dimensions and cubes for your new AW, the process for importing these files is very simple. There are to procedures you can use:

EXECUTEFILE

creates all or part of a standard form analytic workspace from an XML document stored in a text file.

EXECUTE

creates all or part of a standard form analytic workspace from an XML document stored in a CLOB.

The EXECUTEFILE function is the easier to use of the two functions. Assuming you have generated an XML definition of your AW you will also need to define a directory within the database that points to the location of the XML file(s). In the example below the directory SCRIPTS was created and the definition for AW is contained within the file GLOBAL.XML and the whole command is wrapped by the DBMS_OUTPUT.PUT_LINE function so that the "Success" message returned by EXECUTEFILE is displayed:

SQL>execute dbms_output.put_line(dbms_aw_xml.executefile('SCRIPTS', 'GLOBAL.XML'));Success

The EXECUTE approach is slight more complicated but achieves exactly the same result. So why do we have two ways of achieving the same thing? Who knows, as I said before I am sure there is a very good reason?

--Use DBMS_LOB package to create a clob

DECLARE

clb CLOB;

infile BFILE;

dname varchar2(500);

BEGIN

-- Create a temporary clob

DBMS_LOB.CREATETEMPORARY(clb, TRUE,10);

-- Create a BFILE use BFILENAME function

-- Use file GLOBAL.XML in the SCRIPTS directory object.

infile := BFILENAME('SCRIPTS', 'GLOBAL.XML');

-- Open the BFILE

DBMS_LOB.fileopen(infile, dbms_lob.file_readonly);

-- Load temporary clob from the BFILE

DBMS_LOB.LOADFROMFILE(clb,infile,DBMS_LOB.LOBMAXSIZE, 1, 1);

-- Close the BFILE

DBMS_LOB.fileclose(infile);

-- Create the GLOBAL analytic workspace

DBMS_OUTPUT.PUT_LINE(DBMS_AW_XML.execute(clb));

DBMS_AW.AW_UPDATE;

COMMIT;

-- Free the Temporary Clob

DBMS_LOB.FREETEMPORARY(clb);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

and there it is. A new standard form analytic workspace created and populated with base objects (dimensions and cubes) without using a GUI. In the next blog posting I will explain how to maintain cubes and dimensions without using the GUI.

Hyperion products now available for download

2008-07-14T04:37:00.002-07:00

Firstly, if you want to try out the Hyperion BI products, you can do so by going to http://edelivery.oracle.com/ and select "Hyperion Performance Management and BI" from the "Product Pack" dropdown. This then results in the following products that are available to download:

Hyperion Performance Management and BI (9.3.x)
Hyperion Performance Management and BI (9.2.x)
Hyperion Enterprise Release (6.4.1)
Hyperion Business Modeling (4.0.5)
Hyperion Application Link (9.x) Media Pack

You can download individual product components where available, a license key, and also the product documentation - which is a good place to begin.

Secondly, to view a brief description of what these products are, at least at the 'platform' level, go to this page on OTN: Oracle Business Intelligence Platform Home. You shall find the following four Hyperion products listed as part of "Oracle Business Intelligence Enterprise Edition Plus"

Interactive Reporting : Intuitive and highly interactive ad-hoc reporting
SQR Production Reporting : High volume, presentation-quality formatted report generation
Hyperion Financial Reporting : Formatted, book-quality financial and management reporting
Hyperion Web Analysis : Web-based online analytical processing (OLAP) analysis, presentation, and reporting

Thirdly, the Hyperion products are now also listed on the Oracle price-list. The BI Suite EE has been renamed to BI Suite EE Plus (yeah... three cheers for branding).

Wow... three factoids in one post. I must be on a roll here...

How to limit the disk cache used by the BI Server

2008-07-14T04:37:00.001-07:00

This is from a question asked on our internal mailing lists... (yes, I cannot seem to find the time to write new, original posts)

"Is there any way to limit the amount of disk space taken up by data stored on the BI Server for aggregations, calcuations, etc?"

The shor answer is "yes".
NQSConfig.ini (under C:\OracleBI\server\Config) has a section named [ CACHE ] for this purpose.

It reads something like this:

###########################################################
#
# Query Result Cache Section
#
###########################################################

[ CACHE ]

ENABLE = YES;
// A comma separated list of pair(s)
// e.g. DATA_STORAGE_PATHS = "d:\OracleBIData\nQSCache" 500 MB;
DATA_STORAGE_PATHS = "C:\OracleBIData\cache" 500 MB;
MAX_ROWS_PER_CACHE_ENTRY = 100000; // 0 is unlimited size
MAX_CACHE_ENTRY_SIZE = 1 MB;
MAX_CACHE_ENTRIES = 1000;
POPULATE_AGGREGATE_ROLLUP_HITS = NO;
USE_ADVANCED_HIT_DETECTION = NO;

For example, this is how my cache folder looks like:

And to get more information on what these settings mean and do, you can see pages 229-240 of the "Oracle® Business Intelligence Server Administration Guide, Version 10.1.3.2, December 2006, (Part No B31770-01)" for a detailed explanation of what the different settings mean, and a brief exposition of the caching strategies.

Thanks to Krishnan Viswanathan from BI product management for providing the answer (yes... I am rendering credit unto others - wouldn't be nice on my part to steal and scoot - चोरी और सीना जोरी "chori aur seena jori" is not something I subscribe to).

Petrol and OLAP

2008-07-14T04:35:00.000-07:00

According to this NYT article (Big Rise Seen in Demand for Energy - New York Times), "It started with a simple question by Samuel W. Bodman, the energy secretary: What does the future hold for supplies of oil and natural gas?".

In order to answer this question, the National Petroleum Council developed an analytic data warehouse using Oracle relational and OLAP technology. Implemented by Vlamis Consulting, the NPC report states:

The data warehouse was designed to be the main analytical tool for the Task Groups, accepting all data collected from the survey questionnaire and other data sources. As the survey data were multi-dimensional, Oracle OLAP database technology was used and the collection was organized using 7 dimensions:
1. Time (year)
2. Geography (country or geographic region)
3. Energy type (e.g., Oil, Gas, Coal, Nuclear, Renewable)
4. Energy sector (e.g., Commercial, Residential)
5. Case type (e.g., Business as usual, Alternative energy policy)
6. Units (applicable unit of measure)
7. Source (e.g., Public, Proprietary)
The statistics contained in the report posed interesting technical challenges, including non-additive data, skip-level hierarchies, non-standard aggregation rules, and more - all of which Oracle OLAP is designed to manage. Discoverer OLAP was used to analyze the data.

You can find more on the Vlamis blog here (Vlamis Software » Blog Archive » Vlamis Used Oracle OLAP For National Petroleum Council Study)

Thanks to Marty Gubar from OLAP Product Management for passing this on.

Reply to a comment - Hyperion licenses

2008-07-14T04:34:00.000-07:00

This is a post in response to a comment to a post (Hyperion products now available for download)
I have reproduced the entire comment here for convenience:

Anonymous said...

I noticed that on Oracle technology network, a developer can download the software with Development License.
******
Is there a place where a developer can download the Hyperion products with "Development License" to evaluate/learn the products or even prepare for certification?
*******
The reason I am asking is that the 30-day trial period on e-delivery website is barely enough for something as complex as Hyperion products. It takes a while to install/configure them!! Most existing Hyperion customers have only a few products licensed and in the past, Hyperion made it very difficult for developers of those customers to get a trial license (you had to go through sales team to get trial software who wouldn't help you unless they saw immediate money). If there was a development license for Hyperion products, I am sure many of the developers of existing customers would try out some of the remaining products that they don't currently have license for and recommend them to the business users. It would be fine if the development license terms restricted use by the individual developer for non-production use Only (there are paid Hyperion developer tools such as Dashboard Architect, studio, etc. that must still be paid for if used in production environment and any development done with Development License can not be installed in production unless a paid license has also been acquired).

Note: This individual developer "development license" would be different from the "paid development environment" license which is where the development team does the development before installing it to the "paid production environment".
(hopefully it is not too confusing)

The short answer to this question is that once software is available from OTN for download, there **should** not be any license requirement.

Oracle software - all of it - comes without any timed licenses, or trial licenses that lock you out of the product or restrict you to limited functionality after x number of days. If you take the case of Siebel Business Analytics, which became Oracle Business Intelligence Suite Enterprise Edition (with some products like Oracle BI Publisher joining this suite, which in turn came from the Oracle Applications side of the company and went by the very exciting name of XML Publisher), it also had a license key that was required to install and use the different products therein, like Answers, Dashboards, Disconnected, etc... It took some time to take out the license key requirements from the product, and when Oracle released Oracle BI EE 10.1.3.2 (aka code name of 'Maui'), it had no license key restrictions. BI EE 10.1.3.2.1 does not have a license key, nor will 10.1.3.3.0 (due very soon), or other releases.

I totally agree that a 30 day license is probably not enough to install, use, and learn the product family - I would prefer 90 days, and Hyperion does have many products that do not exactly fall in the 'Notepad' variety of easy-to-install-and-learn products (Planning, budgeting, financial performance management, etc...).

At this point I don't really have an answer - what you could try is to download and install the products with the 30 day license, and after the 30 day period try and obtain a new 30 day license file and see if that works.

Note that please do not take this as 'legal' advice from Oracle. This is my personal 2 cents (or चार आने char anne in my lingo).

11g OLAP – Hot Pluggable Performance Booster…

2008-07-14T04:32:00.000-07:00

Over the last few weeks there have been a lot of headlines, articles and blog posts regarding the 11g release of the database. From the point of BI, this release does have a lot to offer. Most importantly (from my point of view) the multi-dimensional model will finally move beyond the current use cases and evolve into a hot a pluggable performance booster. Here is a direct quote from Charles Rozwat, Oracle executive VP for server technologies:

“Oracle's new OLAP features take business intelligence 'from a specialized niche into a much broader market.' “

"As data changes in the base data tables, those changes are automatically refreshed to the materialized views stored in the cubes. The bottom line is more automation and a better ability to handle large amounts of data and complex queries with less of an impact on performance."

Andy Mendelson has also been widely quoted regarding his comments on 11g OLAP:

"The big breakthrough here is that we're able to use the OLAP cubes as a transparent performance accelerator inside the database. The users are still happily using their SQL applications, and they won't even know they're using OLAP. We're moving OLAP from a specialized market to a much broader market among all our SQL users."

The end result is a hot pluggable peformance layer than will help improve response times for all SQL reporting products (Discoverer, BI EE, Business Objects, Cognos etc).

I am glad to see that Googling the term “11g OLAP” provides a number of links analysing 11g OLAP . The first link, at the moment anyway, is to Intelligent Enterprise and their posting provides a non-technical overview of the launch. From this site you jump directly to the second link: the 11g OLAP datasheet on OTN. The datasheet can be downloaded from the OLAP home page on OTN, or you can access it directly via this link here.

The real analysis starts with the next two links from Vlamis and Mark Rittman. Rather than repeat everything here I going to link everyone over to these two postings as together they provide the best analysis of what you can expect from 11g OLAP:

The Vlamis blog, provides a lot more detail on the launch and 11g Analytic Workspace Manager. Chris and Dan have been heavily involved with the beta program and their 11g OLAP posting contains links to the actual 11g launch webcast and links to a powerpoint presentation with screenshots of key 11g Analytic Workspace Manager features. You can get to their web site via this link which will take you directly to the posting on 11g OLAP: http://www.vlamis.com/blog.php/?p=27

Mark's posting looks more at the SQL access layer and the new SQL CUBE_TABLE function as well as reviewing more general features of 11g OLAP. You can access Mark's review at:

http://www.rittmanmead.com/2007/05/07/oracle-olap-11g-news-and-the-vlamis-blog/

For those of you wanting to try 11g OLAP the Linux version will be out in August so watch out for the announcement on OTN.

Go vote!

2008-07-14T04:31:00.000-07:00

I have added a poll page element on the Oracle BI Blog template. This is a new page element that Blogger provided, and I wanted to sort of kick the tyres around. So, please go to the blog and vote.

Edits: corrected a typo. And added a screenshot - within minutes of posting this post and adding the poll page element my blog showed up with an error message for the poll! It's up and working now; anyway, signing off for the weekend.

Russian BI Blog

2008-07-14T04:29:00.000-07:00

Doing some clickstream analysis on the BI Blog, I saw some hits coming this way from a site http://oraclebi.ru/

Check the blog out; and in case you don't know Russian, you can try the Google translated site here. It lists Mark Rittman's blog and mine among others.

Post Edit: I got an email from the blog author, Andrey Pivovarov, who is a sales consultant with Oracle, based out of Moscow.

Oracle Extends Its Lead in the DW Platform Tools Market

2008-07-14T04:25:00.000-07:00

More excellent news for the database team, following hot on the heels of the extremely positive coverage of the 11g database, a new analyst report from IDC has identified Oracle as the leader, by a very big margin, in data warehouse platform tools market. The full story is on the Oracle.com website and can be found here.

This market segment comprises both DW generation and DW management tools. According to IDC, Oracle has over 40% of the market which translated into $1.8 billion in revenue for 2006. So which products were included as part of the platform in this survey? The same group of products that featured heavily in the 11g press releases via comments from Andy Mendelsohn and Charles Rozwat:

Warehouse Builder
Partitioning
OLAP
Data Mining

all of these products are key to growing Oracle's dominance of the data warehouse market, and from the information released as part of the 11g presentations (see previous posting) these products are adding significant new features that are going to make building and managing an Oracle data warehouse even easier.

Interestingly, IDC sees Oracle as being in an excellent position to exploit new areas such as spatial analytics. In fact, Warehouse Builder 10gR2 can already "spatially enable" an existing schema so there is nothing stopping customers from using spatial features today. The front page of OTN has a link to an article on integrating spatial with Google map:

http://www.oracle.com/technology/pub/articles/lokitz-spatial-geoserver.html

The only problem now is : where to send everyone to get more information? Personally, I always use the Yahoo websites for this type of information so here is the link :

http://biz.yahoo.com/prnews/070806/aqm088.html?.v=14

Alternatively try typing "Oracle Named Worldwide Market Share Leader in Data Warehouse Platform Tools" into Google and you should get over 150,00 hits.

The BI - EPM Blog

2008-07-14T04:23:00.000-07:00

You may have seen the blog comment, if you haven't, here's the really good piece of news: there is a new BI - EPM blog available now at http://blogs.oracle.com/frankbuytendijk/. Here's an introduction from the blog:

Frank Buytendijk, Vice President of Corporate Strategy for Oracle|Hyperion and previously a Gartner Research Vice President, is one of world's most recognized thought leaders on business intelligence (BI) and enterprise performance management (EPM). As a researcher, presenter, and business strategy consultant for Oracle|Hyperion's customers, Buytendijk's style is best described as provocative, out-of-the-box, and future-oriented, while adding a human touch. Based in The Netherlands, Buytendijk brings 20 years of experience in BI & EPM.

You can subscribe to the blog feed here.

What no GUI? How to Load AW XML Files

2008-07-14T04:21:00.000-07:00

This part of a series of postings prompted by many questions I received about how to manage an analytic workspace without using a GUI (AWM or OWB) . In this post I want to provide some more information on using XML templates. Most of this information came from Marty Gubar, OLAP Product Manager, who is an expert in using AW XML.

Personally, I am still not sure how you would create an XML file for an AW without using Analytic Workspace Manager, however, based on recent posts on the OLAP forum and from questions posted to our internal help mail list, it is obvious there are people actually designing and building AWs without ever using AWM. So I going to make the assumption that you have somehow managed to created your XML file that describes a dimension or a cube, or any other supported object, and question now is how do you load that file into the AW?

The first thing you need to do is to find this extremely useful jar file, called ImportXML.jar file. For the moment you will have to email me (keith.laker@oracle.com) to get a copy of this file, but hopefully the product management team will get it hosted on the OLAP OTN home page very shortly. Once you have this jar file you can create a script file as follows:

java.exe -classpath .\ImportXML.jar samples.awxml.ImportXML %1 %2 %3 %4 %5 %6 %7 %8 %9

The script takes the following arguments

id/pwd ID and Password
-con [host:port:sid] Connect string (e.g. localhost:1521:orcl)
-aw [TargetAW] AW that is the target for the XML input (required when XML does not include a complete AW)
- file [file] Name of XML file

Examples:
1. Importing an object (e.g. a calculation) into the GLOBAL analytic workspace:

import scott/tiger -con localhost:1521:orcl -aw global -file my_model.xml

2. Importing an entire analytic workspace:

import scott/tiger -con localhost:1521:orcl -file global_aw.xml

The above script can be used with the XML template files generated by Analytic Workspace Manager as well. Alternatively you can manually create your own (as some of you are quite clearly doing!). If you are manually creating these files, or just want to modify an existing file to make some changes hopefully the following overviews will help:

Structure of an XML File: Each XML file is broken down into four basic blocks, which relate to the type of work being performed.

Step 1 is to attach the required AW via an “Attach Id” tag.
Step 2 is to process the object identified within the file
Step 3 is to commit all changes
Step 4 is detach the AW

There is a quick an easy way to understand what is required and how these files are processed. If you use Warehouse Builder, it is possible to use Control Center to generate deployment scripts, which you can then review. For example I used OWB to generate a a CREATE dimension and a DROP dimension script so I could understand the steps involved and review the code it generated. There are a number basic tags that you need to use

Attach
Commit
Delete
Create
Delete
Alter
ActiveObject
Id

Hopefully, the following sections will help to explain how to use these various tags:

Attaching an AW
The process for attaching is very simple. There are two ways to do this, you either:

pass a parameter into your script file via the command line using -AW aw_name
code the AW and schema name into the XML file

I think the first is probably the best idea because it makes it easier to move XML and import files into different database instances. But if you want to hard code this information into your XML file then you need provide the following code:

(I am hoping you can read this code!). The process is quite straightforward. The first four lines are the normal XML header information, after that comes the following:

Attach tag with an Id tag that contains an the word Action with a number, like this "Action1" . This controls the order of processing and in an normal XML file you will see this repeated with numbers increasing sequentially through the file
ActiveObject tag starts the block to identify what the file is going to process which is determined by the next line
In this case the attach needs to process an AW so an AW tag is used to identify the specific AW to be attached
The last three rows contain the termination tags for rows 1,2 and 3.

It is possible to define an attach mode within this block as well, allowing the AW to only be attached in read-only mode which allows AWs to be shared across multiple projects and allows an AW to be attached by multiple processes at the same time.

Committing Changes within an AW
The process for committing is the same as the attach process except the tag Commit is used. Because during a session we could in theory have mulitple AWs attached the commit block still requires an ActiveObject block to identify the AW to be updated. This allows to not update other AWs as required.

Detaching an AW
The process for detaching is also the same as the attach process except the tag Detach is used. Because during a session we could in theory have mulitple AWs attached the detach block still requires an ActiveObject block to identify the AW to detach.

Building a CREATE Script
Create scripts are the output from Analytic Workspace Manager. After the main header block you will find a Create tag with an associated Id that defines the action reference for the create statement. If you are using the Attach tag as well, then the number associated with "Action" for the Create block must be greater than that used in the Attach block. It all sounds very complicated but when you see the code it is very simple.

The Create block identifies the create process in full. For a dimension that full process is:

Dimension name
Dimension labels
Dimension attributes
Dimension levels
Dimension hierarchies
Dimension mappings

A lot of information needs to be provided in the XML file, which is why I prefer to use Analytic Workspace Manager to generate these!

Building a DELETE Script
Performing a Delete operation in Analytic Workspace Manager is very simple - highlight the object and hit the delete key. Assuming there are no supporting objects that prevent the delete process from running the object will be deleted - for example you cannot delete a dimension while it is still associated with a cube. However, outside of Analytic Workspace Manager the XML file does provide a Delete tag to allow for command line deletion of objects. The amount of information you need to supply to delete an object is minimal. For a dimension it is simply the lines containing the Dimension tag and the end-Dimension tag. The delete process will remove all the objects with dimension automatically.

Building an ALTER Script
It is possible to update certain parts of an AW using this method. To be honest, I have not tried everything. According to the Java documentation this method allows you to update any of the text attributes, such as:

Long descriptive label
Short descriptive label
Plural descriptive label

So you could change a dimensions description from "Products" to "Company Products" for example. For more heavy duty alterations, such as modifying a hierarchy by changing the levels within the hierarchy I think you would need to delete the hierarchy first and then rebuild it. It may depend on the types of changes you want to make, however, the API documentation seems to indicate this is a heavily restricted tag. But enjoy!

Oracle Database 11g on Linux now available

2008-07-14T04:19:00.000-07:00

For those keeping track, Oracle Database 11g Release 1 (11.1.0.6.0) - Standard Edition, Standard Edition One, and Enterprise Edition - became avialable for download from OTN. You can download it from this link - a 1.7GB single zip file. Also see Keith's previous post - 11g OLAP – Hot Pluggable Performance Booster… and Oracle Extends Its Lead in the DW Platform Tools M...