Re: ORA 6502 - False alarm?

• Original Message ----- From: "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> Newsgroups: comp.databases.oracle.misc Sent: Sunday, September 17, 2000 7:08 AM Subject: Re: ORA 6502 - False alarm?

> Sorry to say so, but this is an example of bad design.
>
> There are many problems in the design and the code
> - Under Oracle 8i the table desc fails to create because desc is a
 reserved
> word.
>
> - Secondly you're trying to make 'intelligent' id's in the table orig.
> Generally speaking you shouldn't do this.
> It is always more difficult to 'parse' an 'intelligent' number, than
 to
> concatenate it afterwards.
>
> -It very much looks like you have a very bad design.
> Your hardcoded 'manager' etc, should have gone into a separate table,
 with a
> foreign key from the orig table to the separarate table. In that case
 you
> won't need the descr table. Please try to study database design and
 above
> all normalization, otherwise you will soon find your self in a
 *complete*
> mess.

I do want to thank you for your help. There a few things I should clarify, too:

• The assignment I posted earlier was for a beginning PL/SQL class I'm in. And while I agree with you about the design, I'm not the professor and can actually tell you that I have no say over the matter. ;-) I know I'll have other opportunities to learn about database design, but they will come after I have time to pursue such courses.
• The desc thing was my mistake there; I had something with an underscore before it, so I dropped it to make my typing the script in shorter.

> With respect to your 6502
> the correct code is this
>
> 1 create or replace trigger trig after update or insert on orig
> 2 declare
> 3 v_descrip descr.description%type;
> 4 idcheck descr.id%type;
> 5 cursor c is
> 6 select * from orig;
> 7 v_sqlstring varchar2(1000);

[snip]

Just out of curiosity, how does the parse string length affect things? When I added the concatenation symbols in the parse string which sends the decode statement, I still got the ORA 6502 message. I increased v_sqlstring to 1000 characters as shown in your correction, and it went away.

The professor looked at this today, and he was surprised I used the dynamic SQL in the first place. He was telling me that the easier way would have been to use a row trigger instead of a statement-level trigger. I know now how that's done, but I would never have pulled it off in that manner earlier.

Again, thanks for the help.

--
Posted from pimout4-ext.prodigy.net [207.115.63.103] 
via Mailgate.ORG Server - http://www.Mailgate.ORG