ORACLE Triggers

[ethanasi]

I created this trigger so I can get the next sequential number in field: kodi_p:

declare nr number(15,0);
begin
select max(kodi_p) into nr from p_personeli;
if nr>0 then
nr:=nr+1;
else
nr:=1;
end if;
:new.kodi_p:=nr;
end;

It works OK when I insert from Oracle's EE and SQLTalk:

insert into p_personeli
(id_inst)
values
(7);

When I try to insert from a TD5.1 app, I get strange errors like:
ORA-24373: invalid length specified for statement

I tested in another table, another trigger, and other error messages are given.
When I drop the trigger and use a constant ID the statement is executed OK.
Is this a known issue?
Am I doing anything wrong?

[ethanasi]

The silence tells me that I cannot use ORACLE triggers!
Is it so Unify?

[Ana Paula Bonani]

Hi,

I gave it a try but got trigger related syntax errors from SQLDeveloper when trying to insert the row.

I'm probably missing something basic but it would make it much faster if you could please provide a simple reprocase with scripts to create the table, create the trigger and a simple TD application demonstrating the problem.

Your help is appreciated here.

Thanks in advance,
Ana
--
Unify Technical Support

[ethanasi]

create table p_personeli
( kodi_p number(15,0) NOT NULL,
name varchar2(30));

Then create a trigger tg_kodi_p in ORACLE Enterprise Manager:
Trigger for each row, before insert in p_personeli:

declare nr number(15,0);
begin
select max(kodi_p) into nr from p_personeli;
if nr>0 then
nr:=nr+1;
else
nr:=1;
end if;
:new.kodi_p:=nr;
end;

It works OK when I insert from Oracle's EE and SQLTalk:

insert into p_personeli
(name)
values
('test');

From TD51:
Call SQLPrepareAndExecute(hSql, "insert into p_personeli
(name)
values
('test')")

Hope you can reproduce

[Ana Paula Bonani]

Hello,

Thanks for the steps to repro, they were helpful.

Please see attached screenshots. I was able to insert from TD5.1 Service Pack 3 without errors on Oracle 10.

Do you have SP3 installed? What is your Oracle version?

Thanks,
Ana
--
Unify Technical Support

[ethanasi]

Hi Ana,
Thanks for your help.
I was using SP2, but now I have SP3 and still have the same problem.
ORACLE version is 9iR2.

The error message I've got now is:

ORA-24324: service handle not initialized

I'm sure that this error is raised by mistake, since this error is related to Oracle initialization.

Regards,
Tony

[Ana Paula Bonani]

Hi Tony,

This is most likely a non-TD problem.

I did a quick Google search and found other reports about ORA-24324 showing up in unrelated situations like you mention below.

For instance:

SUMMARY
After connecting to an Oracle database you get the following message: "ORA-24324 service handle not initialized".

CAUSE
Your password is about to expire.

RESOLUTION
Reset your password by using the following SQL statement in an ISQL window:

ALTER USER USERNAME IDENTIFIED BY "PASSWORD";

Or another resolution:

log in as root and execute command "ipcs". should you see ipcs owned by the oracle user, delete them using the "ipcrm" command, and everything will work fine again

Please investigate further from the Oracle side and let us know if you find that it's a TD only issue.

Thanks,
Ana

[ethanasi]

Hi Ana,
As I mentioned in previous posts, I can insert from SQLTalk and from ORACLE Enterprise Manager.
In such cases the trigger works OK.
I've got problems only when I try to insert from TD51.
So, I am pretty sure that this is not an ORACLE related problem.
Thanks,
Tony

[Ana Paula Bonani]

Hi Tony,

I just tested again with Oracle 9i. Same results as before - TD inserted the first and sub-sequent rows without problems.
Not sure what is different in our environments. Maybe Oracle client version..
Would you be able to install Oracle10 Express on a clean machine and try from TD5.1 SP3?

Thanks,
Ana
--
Unify Technical Support