Hi all,

I'm having an oracle trigger issue. When I insert an address in a sales order, I get the following error in the log.oracle8.

2006-05-04[11:00:25]:E:bsp: Error ORA-1722 occurred during execute.
2006-05-04[11:00:25]:E:bsp: ORA-01722: invalid number
2006-05-04[11:00:25]:E:bsp: ORA-06512: at "BAAN.TTDSLS042100_INSERT", line 14
2006-05-04[11:00:25]:E:bsp: ORA-04088: error during execution of trigger 'BAAN.TTDSLS042100_INSERT'
2006-05-04[11:00:25]:E:bsp:
2006-05-04[11:00:25]:E:bsp: Error BDB-512 returned.
2006-05-04[11:00:25]:E:bsp: DD incompatible with table.
2006-05-04[11:00:25]:E:bsp: See fatal SQL statement in '//baan/bse/log/log.ora.sql'.
2006-05-04[11:00:25]:E:bsp: Flushed at /view/port.6.1c.07.03/vobs/tt/servers/ORACLE_2/ora_native.c : #2134.
2006-05-04[11:00:25]:E:bsp: ******* E N D of Error message *******

Here is the trigger that I made
DECLARE
username varchar2(15);
currenttime number;
BEGIN

/* Set Username and Time */
select osuser into username from v$session
where audsid = (select userenv ('sessionid') from dual);

currenttime := 3600 * to_number(to_char(SYSDATE,'HH24'))
+ 60 * to_number(to_char(SYSDATE,'MI'))
+ to_number(to_char(SYSDATE,'SS'));

insert into ttdsls992100 values( :new.T$ORNO, sysdate, currenttime,'INS',username
,:new.T$NAMA,:new.T$NAMA
,:new.T$NAMB,:new.T$NAMB
,:new.T$NAMC,:new.T$NAMC
,:new.T$NAMD,:new.T$NAMD
,:new.T$NAME,:new.T$NAME
,:new.T$NAMF,:new.T$NAMF,0,0);

END TTDSLS042100_INSERT;

Here is the table structure that it is doing the inserting into.


Date : 05-04-06 [11:04] TABLE DEFINITIONS BY PACKAGE / TABLE / VRC Page : 1
Standard_Data Company : 000

------------------------------------------------------------------------------------------------------------------------------------
Package : td Distribution BAAN IV Date : 01-17-95
Module : sls Sales Control BAAN User : keesbt
Table : tdsls992 Sales Address Line Audit Update Reference Message:
VRC : B40Cc4csi0 Delete Reference Message:
Relation Type : All fields
------------------------------------------------------------------------------------------------------------------------------------
Field Name | Description | Domain | Datatype Physic.| Mand. | Acti | Refer. Reference
| | | length | | | Table Mode
---------------+-------------------------------------+-----------------+---------------------+-------+-------+----------------------
1 orno | Order | tcorno | Long 4 | No | Yes |
2 date | Date | tcdate | Date 4 | No | Yes |
3 time | Time | tctime | Long 4 | No | Yes |
4 type | Type | tcmcs.type | Enumerated 1 | No | Yes |
5 user | User | tcmcs.user | String 12 | No | Yes |
6 nama | | tcnama | Multi Byte 35 | No | Yes |
7 onama | | tcnama | Multi Byte 35 | No | Yes |
8 namb | | tcnamb | Multi Byte 30 | No | Yes |
9 onamb | | tcnamb | Multi Byte 30 | No | Yes |
10 namc | | tcnamc | Multi Byte 30 | No | Yes |
11 onamc | | tcnamc | Multi Byte 30 | No | Yes |
12 namd | | tcnamd | Multi Byte 30 | No | Yes |
13 onamd | | tcnamd | Multi Byte 30 | No | Yes |
14 name | | tcname | Multi Byte 30 | No | Yes |
15 oname | | tcname | Multi Byte 30 | No | Yes |
16 namf | | tcnamf | Multi Byte 30 | No | Yes |
17 onamf | | tcnamf | Multi Byte 30 | No | Yes |
-----
Record Length: 395

------------------------------------------------------------------------------------------------------------------------------------
Index | Description | Index Parts | Dupl | Acti
| | 1 2 3 4 5 | |
-------+--------------------------------------------------------------+----------------------------------------------+-------+------
1 | Sales Order Line Address Audit | orno nama | No | Yes
-----------------------------------------------------------------------------------------------------------------------------------|
Please help

I don't think you can use oracle sysdate to calculate your baan date / time..


Dave

I do have triggers that use that same bit of code and they work

I also get this when I go into the log.ora.sql
User : bsp (pid 8828)
Date : 2006-05-04[10:51:22]
SQL :
LOCK TABLE baan.ttdsls042100 IN ROW SHARE MODE NOWAIT
------ End of Log Message -----
------ Log Fatal SQL Statement -----
User : bsp (pid 8828)
Date : 2006-05-04[11:00:25]
SQL :
LOCK TABLE baan.ttdsls042100 IN ROW SHARE MODE NOWAIT

First the caveat - I'm an MK user and not Baan so I don't know all the details of what changed between the two since they split. I've been told that up to Baan IV, they're still pretty similar at a tools level, though.

If you go into Oracle and do a Describe on your table, does it match the DD? In MK, we have to do a convert to runtime when we update a table definition. I've been burned before when I've changed the table but forgot to run the convert and then I have a mismatch between what Oracle thinks the table looks like and what the DD has stored for the definition.

As an aside, it looks like Baan allows you to create triggers in the application? In MK, we have to create our triggers in native SQL. That's a nice feature.

I have checked that and they are the same. I'm lost :(

Just for grins, try replacing the sysdate in your values list with TRUNC(sysdate). I saw this in another thread.

Also, for the time value, you can use TO_NUMBER(TO_CHAR(SYSDATE,'SSSSS')) instead of doing the math yourself (yes, I stole this from that other thread too... :))

For the table in question, check each of the fields domains being used...that maybe where the difference lies.

Also, check your $BSE/lib/defaults/db_resource and see if you have pessimistic locking or delayed locking on (I think that may be why you are seeing LOCK TABLE command vs. actual SQL update statement).

Well dumb me. The type was different that what it should be. I missed it when I went back through but now I found it. Thanks a bunch for all ya'lls help.