I'm finding some problems in execut

[jonatan]

I'm finding some problems in execution of java triggers (stored procedures). The scripts follow:

create table a (
b number(5),
c varchar2(10)
);

create or replace procedure d (e in out varchar2)
as language java
name 'TriggerOracle.atribuiValor(java.lang.String[])';
/

create or replace trigger f
before insert on a
for each row
call d new.c)
/

*** TriggerOracle.java ***

public class TriggerOracle
{
public static void atribuiValor (String[] variavel)
{
variavel[0] = "xxxx";
}
}


When I try to insert a record in the table A, the size of the string that I use for field C in the insert is used, and not the size of the String defined by the trigger. So:

SQL> insert into a values (1,'aa');
SQL> insert into a values (2,'bbbb');
SQL> insert into a values (3,'cccccc');
SQL> select * from a;

B C
---------- ----------
1 xx
2 xxxx
3 xxxxcc

Is this correct? What is necessary for assign the value "xxxx" (in this case) to the field C?


Thanks,

Jonatan Schroeder
Medisoft - Brazil

 

[AOLEURO]

Hi Jonatan

I am not a Java programmer - I have done a lot of C code.

As I understand your code:

1. Trigger: :NEW.C = 'aa\0'
 2. Procedure D->atribuiValor.String = 'aa\0'
  3. variavel[0] = x on pos. 0, x on pos. 1 and STOP changes because End-Of-String reach on variavel.

1. Trigger: :NEW.C = 'bbbb\0'
 2. Procedure D->atribuiValor.String = 'bbbb\0'
  3. variavel[0] = x on pos. 0, x on pos. 1, x on pos. 2, x on pos. 3 and STOP changes string 'xxxx' ends.

1. Trigger: :NEW.C = 'cccccc\0'
 2. Procedure D->atribuiValor.String = 'cccccc\0'
  3. variavel[0] = x on pos. 0, x on pos. 1, x on pos. 2, x on pos. 3 and STOP changes because string 'xxxx' ends.

I understand variavel[0] = 'xxxx\0' as 'change values starting from position 0 and until ether string ends or end-of-string on variavel reach’.

Like this:

variavel[0]   ‘cccccc\0’
            + ‘xxxx\0’
            = ‘xxxxcc\0’

Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[AOLEURO]

Hi Again

Just forgot to ask you - you do know that you are making context switch from SQL(insert)->PL/SQL(trigger)->JAVA(atribuiValor)->PL/SQL(back to trigger)->SQL(the real insert)?

It cost a lot in resource to make this context switch - I hope you know this and you do not got a lot of inserts on the table.
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[jonatan]

Yeah, this is how it works, but I'd like that it changes also the size of the variable new.c). My question is: how to do that? In "C" I know it works so, but Java doesn't work so. If I call:

SQL> VAR x CHAR;
SQL> begin
2 x := 'aa';
3 end;
4 /
SQL> CALL d x);
SQL> PRINT :x;

X
----------
xxxx

So, the problem is in the trigger, not in the procedure.