Comment On Orable Generated Code

I honestly used to have so much respect for all Oracle tools.  I really hope this is a 3rd party application developing PL/SQL code.

why do in one line what you can do in 10?

This seems a bit overkill on checks to me.

The real wtf is this post.

Anyone care to explain the difference between the following 3 items?

add(FIREDATE DATE(, ))

add(FIREDATE DATE())

add(FIREDATE DATE)

Is there a difference?

This is too bad, this has to be generated!!!



(oh wait...) :)

Sweet, first reply. Nothing else much to say.

apparently they are having some staffing issues if they decided to add a column to their employee table to keep track of when their employees are fired

argh... by the time I posted I was no longer first.

you snooze, you lose --  try something shorter next time .... like 'first!'

Fucktard

forget the goggles, this gives me a migraine just looking at it. All those extra lines of code to generate this hunk of junk must take up the extra 5gb needed to install the "Orable" bloatware.

even their light footprint is still 2gb!!! "Oracle is the Devil"

Anonymous:

Anyone care to explain the difference between the following 3 items?

add(FIREDATE DATE(, ))

add(FIREDATE DATE())

add(FIREDATE DATE)

Is there a difference?

My guess is that for the DATE type, it should be just "DATE".  But for other types, such as VARCHAR2, it should be VARCHAR2(60) where the number is the width of the column.  NUMBER requires two parameters, e.g. NUMBER(8,2) is 8 digits wide with 2 digits after the decimal point (IIRC).

It came out as DATE(, ) instead of NUMBER(8, 2) because the type was DATE instead of NUMBER, and the fields where 8 and 2 would have been were empty strings.  It doesn't do any harm since the IF statement checks for $dataType == 'NUMBER' and that is false when $dataType is not NUMBER.

jackass:

I honestly used to have so much respect for all Oracle tools.  I really hope this is a 3rd party application developing PL/SQL code.

well you mustn'y have worked with oracle very long have you!

At least oracle software is bug free and free of security vulnerabilities.

That code is pretty good, but I laughed harder at this:

Anonymous:

At least oracle software is bug free and free of security vulnerabilities.

I'm not a real PL/SQL guru, so stop me if this a silly question, but...

if 'DATE' = 'NUMBER' and length('DATE')>0 and length('')>0 then

that branch is never going to execute, right? Unless the length of an empty string somehow manages to be greater than zero.


Not the kind of brain-damagedness that I usually expect from machine-generated code.

You misspelled "unless the constant strings 'DATE' and 'NUMBER' suddenly become equal."

You also misspelled "Unless the length of NULL somehow manages to be greater than zero.", because orrible conflates NULL and ''.  See the IHOC forums for details.

Well, I guess if someone is being paid per line of code developed this makes sense......

I've got it. The bot that generated this code is being paid by the line.

It looks silly, but the code was probably in the form of a template, and the generator just used replaceable variables. This way, the generator code doesn't have to do all of those checks (is it a NUMBER, INTEGER, etc.), since the generated code will do them.

That is why Oracle rules and is so expensive.
With an open source solution (mysql, PostgreSQL) you'd only get one line!

--
They didn't know they were robots....

Remember, there is no "reusability", "maintainability", "readability" or even "understandability" criteria for generated code. The only criteria for generated code is: Does it perform its intended task correctly? This appears to do that, even though it is really, really ugly.

It does seem to me that if you know enough to substitute 'DATE' into your template, you should know enough to only choose the part of the template that is needed.

But then again, Orable paid good money for Peoplesoft and JDEdwards - and what quality software that is!

Fixed:

begin

if false;

else

  execute immediate 'alter table "SCOTT"."EMP" add(FIREDATE DATE)';

end if;

end;

Speaking of generated code, has anyone else here ever used Microsoft Excel to generate code?

I do it occasionally, when I'll have a table of data that needs to be hard-coded into an array or something.  Just insert columns containing the needed spaces and punctuation, then fill down for all the data.  Then just copy/paste the spreadsheet into notepad, s/\t//g, and copy/paste that right into your code.  I've never seen anyone else do this, but I figure there must be others that have done this, since it makes a few specific tasks much faster and less error prone.

Anonymous:

why do in one line what you can do in 10?

Great!  Now we're measuring ROI by LOC.

To expand upon what I said earlier, the template probably looked something like this (just a guess):

begin

if '%DATATYPE%' = 'NUMBER' and length('%DATATYPE%')>0 and length('%DATALEN2%')>0 then

  execute immediate 'alter table "%TABLESPACE%"."%TABLENAME%" add(%COLUMNNAME% %DATATYPE%(%DATALEN%, %DATALEN2%))';

elsif '%DATATYPE%' = 'INTEGER' or 

  ('%DATATYPE%' = 'NUMBER' and length('%DATATYPE%')>0 and length('%DATALEN2%')=0) or

  '%DATATYPE%' = 'VARCHAR2' then

  execute immediate 'alter table "%TABLESPACE%"."%TABLENAME%" add(%COLUMNNAME% %DATATYPE%(%DATALEN%))';

else

  execute immediate 'alter table "%TABLESPACE%"."%TABLENAME%" add(%COLUMNNAME% %DATATYPE%)';

end if;

end;

If you think this is bad, you should see what piles of steaming code come out of SQL Server's Enterprise Manager for the very same task.  And yes, SQL Server has the ability to add a column in one line.  Just no one ever told Enterprise Mangler.  The best part of the SQL Server generated code is it includes a drop table statement WITH NO ERROR CHECKING.  Have an error?  Too bad, I am dropping your one copy of the table, anyway.

Anonymous:

It looks silly, but the code was probably in the form of a template, and the generator just used replaceable variables. This way, the generator code doesn't have to do all of those checks (is it a NUMBER, INTEGER, etc.), since the generated code will do them.

I've written a few code generators that generated some ugly code, so that was my initial reaction as well... All code generators produce WTFs. However, assuming NUMBER and INTEGER are constants in the template, I don't understand the logic that would ultimately produce this:

Original Post:

'DATE' = 'NUMBER' and length('DATE')>0

That's enough for me to confirm it's place on this board.

Did anyone else have trouble with the pun in the title? It took me a bit; I thought Alex had misspelled Oracle. I almost posted as such, until I finally got the joke.

However, assuming NUMBER and INTEGER are constants in the template, I don't understand the logic that would ultimately produce this

The replaceable parameters. The user probably fills out a form specifying data type, tablespace, tablename, datalength 1, and datalength 2. The generator simply replaces %DATATYPE% with whatever was selected, and runs the resulting script. It would allow changes to how columns are added to be made by simply changing the template, instead of modifying code deeper in the generator. It looks very WTF, but I don't think it is.

Paul is right ... noWTF here. Move along...

Maybe tomorrow.

A whole lot of ugly SQL gets generated by applications, and SQL people often forget that because it's interpreted code, generated on the fly, things that look like constants might not be. The reason this is ugly is that it's a simple way to make the syntax correct for a variety of column types. A person would not do that, but it's probably because a person would not need to construct code that could handle EVERY datatype, only the one they intend to use.

kipthegreat:

Speaking of generated code, has anyone else here ever used Microsoft Excel to generate code?

I do it occasionally, when I'll have a table of data that needs to be hard-coded into an array or something.  Just insert columns containing the needed spaces and punctuation, then fill down for all the data.  Then just copy/paste the spreadsheet into notepad, s/\t//g, and copy/paste that right into your code.  I've never seen anyone else do this, but I figure there must be others that have done this, since it makes a few specific tasks much faster and less error prone.

Yes.  I can't think of any specific code examples, but I remember the time I did it when I was creating some data for the first time.  I had a bunch of chart names that needed to be entered into a table and it was much faster to type chartname_1, chartname_2, and then fill down to 40 or so and import than any other sort of copy/paste/backspace/type number method or programming any sort of quick and dirty method for it.

Remember some days ago when I said SQL is a good query language but an awful programming language?



The real WTF here isn't the logic, it's that the logic is being done in
SQL.  If PL/SQL had just put those rules in its code, the
generated SQL would be a single line.



I've used PL/SQL some.  I'm not enough of a database expert to
comment on its database capabilities, but I am a UI expert and I know a
hideous user interface when I see one.  PL/SQL's user interface is
hideous.



I'm not surprised they have other problems.  The app feels like it's been dragged into this century kicking and screaming.

I'm no DB guru, so correct me if i'm wrong but isn't length('')=0 like saying 3==3 ?

Disgruntled DBA:

If you think this is bad, you should see what piles of steaming code come out of SQL Server's Enterprise Manager for the very same task.  And yes, SQL Server has the ability to add a column in one line.  Just no one ever told Enterprise Mangler.  The best part of the SQL Server generated code is it includes a drop table statement WITH NO ERROR CHECKING.  Have an error?  Too bad, I am dropping your one copy of the table, anyway.

If all I need to do is add a column to a table, I'll simply right the one line alter table statement myself instead of having to use a tool to do it. Why bother with a tool SQLDeveloper or doing something in enterprise manager if it is not necessary?

Given the derision of the example on this page I found it interesting that
Google chose to run this ad on the page (at least my copy)

RAD Code Generation tool

Template based code generation ASP.Net, VB, C# over 50 templates

ferrengi:

Disgruntled DBA:

If you think this is bad, you should see what piles of steaming code come out of SQL Server's Enterprise Manager for the very same task.  And yes, SQL Server has the ability to add a column in one line.  Just no one ever told Enterprise Mangler.  The best part of the SQL Server generated code is it includes a drop table statement WITH NO ERROR CHECKING.  Have an error?  Too bad, I am dropping your one copy of the table, anyway.

If all I need to do is add a column to a table, I'll simply right the one line alter table statement myself instead of having to use a tool to do it. Why bother with a tool SQLDeveloper or doing something in enterprise manager if it is not necessary?

Ahh, you have apparently not had to deal with the Disney Automaton Ride Refugees that I have to work with here.  I swear, if I close my eyes, I can here them all singing "It's a small world".

If the template designer had just put all the replaceable parameters in local variables, and used local variables throughout the script, it probably wouldn't have been posted here. Comparing the local var (although hard-coded) to a literal looks a lot better and is easier to understand the purpose behind than comparing two literals.

The whole thing seems very enterprisey. They could've even had file describing forms for various functions, which is then read to display this "Add Column" function, and the data from is then plugged into some "Add Column" template. The system wouldn't even have to know what the forms and templates are for. It pushes any maintenance on the code to a higher, more abstracted level that keeps people from mucking about in the system.

I think that the main WTF here is that the tool tried throwing the datatype check into the SQL code instead of putting it in the generation tool code.

Digitalbath:

Fixed:

begin
if false;
else
  execute immediate 'alter table "SCOTT"."EMP" add(FIREDATE DATE)';
end if;
end;

Doesn't work, since you cannot have empty branches in PL/SQL (but you can use a null statement to fill it) and if requires a then.

Here is what it's meant to be:

begin
if false then null;
else
  execute immediate 'alter table "SCOTT"."EMP" add(FIREDATE DATE)';
end if;
end;

Screenshots here...

I can't beleive no one has made this joke yet:

 

It's missing a bit.  There should be an

update "SCOTT"."EMP" set FIREDATE = sysdate()

VGR:

I've used PL/SQL some.  I'm not enough of a database expert to
comment on its database capabilities, but I am a UI expert and I know a
hideous user interface when I see one.  PL/SQL's user interface is
hideous.

PL/SQL doesn't have a user interface. There is Orace Forms, a RAD tool
that uses PL/SQL as language. Maybe that's what you're thinking of.

GoatCheez:

I'm no DB guru, so correct me if i'm wrong but isn't length('')=0 like saying 3==3 ?

No, since in Oracle lenght('') is null. A good chance to write endless-loops in PL/SQL..

while length(myString)>0 loop

  myString:=substr(myString, 2); /* removes the first character from myString */

end loop;

ammoQ:

VGR:

I've used PL/SQL some.  I'm not enough of a database expert to
comment on its database capabilities, but I am a UI expert and I know a
hideous user interface when I see one.  PL/SQL's user interface is
hideous.

PL/SQL doesn't have a user interface. There is Orace Forms, a RAD tool
that uses PL/SQL as language. Maybe that's what you're thinking of.

My mistake.  What I used was PL/SQL Developer.  I had come to
know it as just "PL/SQL."  Guess I can take away a lesson about
using imprecise terminology.

VGR:

ammoQ:

VGR:

I've used PL/SQL some.  I'm not enough of a database expert to
comment on its database capabilities, but I am a UI expert and I know a
hideous user interface when I see one.  PL/SQL's user interface is
hideous.

PL/SQL doesn't have a user interface. There is Orace Forms, a RAD tool
that uses PL/SQL as language. Maybe that's what you're thinking of.

My mistake.  What I used was PL/SQL Developer.  I had come to
know it as just "PL/SQL."  Guess I can take away a lesson about
using imprecise terminology.

Ouch. PL/SQL developer is XXXXXXXXXX. TOAD is much better, though it's
expensive. The new tool, SQLDeveloper, seems like a good tool for
PL/SQL development to me (despite today's WTF ;-).

My favourite editor for PL/SQL is Slickedit.

Anonymous:

However, assuming NUMBER and INTEGER are constants in the template, I don't understand the logic that would ultimately produce this

The replaceable parameters. The user probably fills out a form specifying data type, tablespace, tablename, datalength 1, and datalength 2. The generator simply replaces %DATATYPE% with whatever was selected, and runs the resulting script. It would allow changes to how columns are added to be made by simply changing the template, instead of modifying code deeper in the generator. It looks very WTF, but I don't think it is.

Well... that would still not explain the thing that bullseye homed in on:

'DATE' = 'NUMBER' and length('DATE')>0

If the first condition has already established that the value of %DATATYPE% is 'number', then checking the length of %DATATYPE% must be totally redundant. Unless the template itself is *also* generated from another template - an even scarier prospect.

Hans Liss & bullseye: Good point -- that does seem pretty dumb. I noticed it when I was making the template post, but I didn't realize that's what you were talking about. A template template -- now that's enterprise.

Yes, but is there a template template factory? A template template factory factory?