Re: ORA 6502 - False alarm?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 17 Sep 2000 13:08:35 +0200
Message-ID: <969196544.12486.0.pluto.d4ee154e@news.demon.nl>

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.

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);
  8      v_cursor    number;
  9      v_dummy     integer;
 10    begin
 11      for v_descrecord in c loop
 12        idcheck:=substr(v_descrecord.id,1,1);
 13        v_cursor:=dbms_sql.open_cursor;
 14        v_sqlstring:='select decode('||idcheck||',''1'',''manager'',
 15                                            ''2'',''technical'',
 16                                            ''3'',''sales'',
 17                                            ''4'',''cleaning staff'',
 18                                                  ''general'')
 19                        from dual';
 20        dbms_sql.parse(v_cursor,v_sqlstring,dbms_sql.v7);
 21        dbms_sql.define_column(v_cursor,1,v_descrip,15);
 22        v_dummy:=dbms_sql.execute(v_cursor);
 23          if dbms_sql.fetch_rows(v_cursor)=0 then exit;
 24          end if;
 25        dbms_sql.column_value(v_cursor,1,v_descrip);
 26        update descr
 27          set id=v_descrecord.id,
 28              description=v_descrip
 29            where id=v_descrecord.id;
 30        if sql%notfound then
 31           insert into descr(id,description)
 32             values(v_descrecord.id,v_descrip);
 33        end if;
 34      end loop;

 35* end trig;
SQL> / Trigger created.

SQL> insert into orig values(12,'Bakker','Sybrand')   2 /

1 row created.

SQL> commit
  2 /

Commit complete.

SQL> select * from orig
  2 /

       ID LNAME                FNAME
--------- -------------------- --------------------
       12 Bakker               Sybrand

SQL> select * from descr
  2 /

ID DESCRIPTION
---- ---------------
12 manager

SQL> However, I should just urge you to reconsider your design.

Regards,

Sybrand Bakker, Oracle DBA

""Hudson N. Jackson II"" <h2jackson_at_prodigy.net> wrote in message news:004801c0207d$21c2f6c0$38441440_at_default...
> I'm working with triggers at the moment. There is a slight problem,
> however, with a current assignment I'm working on. If I create two
> tables:
>
> create table desc(id varchar2(4), description varchar2(15));
> create table orig(id number(4), lname varchar2(20), fname varchar2(20));
>
> it will create the two tables properly. Then, I create a script which
> creates the trigger as follows:
>
>
> create or replace trigger trig after update or insert on orig
>
> declare
> v_descrip desc.description%type;
> idcheck desc.id%type;
> cursor c is
> select * from orig;
> v_sqlstring varchar2(100);
> v_cursor number;
> v_dummy integer;
>
> begin
> for v_descrecord in c loop
> idcheck:=substr(v_descrecord.id,1,1);
> v_cursor:=dbms_sql.open_cursor;
> v_sqlstring:='select decode(idcheck,''1'',''manager'',
> ''2'',''technical'',
> ''3'',''sales'',
> ''4'',''cleaning staff'',
> ''general'')
> from dual';
> dbms_sql.parse(v_cursor,v_sqlstring,dbms_sql.v7);
> dbms_sql.define_column(v_cursor,1,v_descrip,15);
> v_dummy:=dbms_sql.execute(v_cursor);
> if dbms_sql.fetch_rows(v_cursor)=0 then exit;
> end if;
> dbms_sql.column_value(v_cursor,1,v_descrip);
> update desc
> set id=v_descrecord.id,
> description=v_descrip
> where id=v_descrecord.id;
> if sql%notfound then
> insert into desc(id,description)
> values(v_descrecord.id,v_descrip);
> end if;
> end loop;
> end trig;
>
>
> Executing the script succeeds, and the trigger is created. However,
> when I insert a statment into table orig like this:
>
> insert into orig(id,lname,fname) values ('1111','rizal','jose');
>
> the ORA 6502 message gets thrown at line 37. In an attempt to get rid
> of the numeric/value error issue, I tried forcing conversions to happen
> by first using the to_char function in the update and insert statements,
> then to_number. Neither got rid of the message. Is there something I'm
> overlooking? I'm not sure if it's due to the dynamic SQL stuff I put in
> there. . . but if that's true, does someone have an alternate way of
> using the decode function without using if/then? Ideally, I'd like the
> decode function - or something similar to put the result into v_descrip.
>
> It's kind of urgent; I need to know before Monday Eastern Time. Thanks
> in advance for your help.
>
>
>
> --
> Posted from pimout1-ext.prodigy.net [207.115.63.77]
> via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Sun Sep 17 2000 - 06:08:35 CDT