ORA 6502 - False alarm?

From: Hudson N. Jackson II <h2jackson_at_prodigy.net>
Date: 17 Sep 2000 09:58:33 +0200
Message-ID: <004801c0207d$21c2f6c0$38441440@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 - 02:58:33 CDT