TOracleScript and trigger creation

[Petros Amiridis]

Hi,

Under Oracle 9.2.0.1.0, I am creating a trigger using TOracleScript but it shows up as invalid, giving me ORA-04093: references to columns of type LONG are not allowed in triggers.

The trigger is an "after insert for each row" on a table that has a LONG RAW column. I *DO NOT* reference this column inside the trigger. I do not declare any LONG TYPE variables that could cause a problem.

Afterall, the same trigger *is created* without problems through SQLPlus Worksheet (9.2.0.1.0).

TOracleScript creates a valid trigger without problems under Oracle 7.3.4.

If it helps, the same thing happens from within PL/SQL Developer too.

What is the problem? Is this a known bug? Is there a workaround?

Petros

 

[Marco Kalter]

Can you post or email the trigger script and table definition?

The TOracleScript and PL/SQL Developer simply send the "create or replace" statement to the server, so if it returns an ORA-04093, there must be a reason.

------------------
Marco Kalter
Allround Automations

 

[Petros Amiridis]

Originally posted by mkalter:
Can you post or email the trigger script and table definition?

I will post both the trigger and the table definition on Monday, when I will go back to the office. Unfortunately I don't have the definition with me. In the meantime let me just tell you...

Originally posted by mkalter:
The TOracleScript and PL/SQL Developer simply send the "create or replace" statement to the server, so if it returns an ORA-04093, there must be a reason.

...that I don't understand why it works under 7.3.4 and it doesn't work under 9.2.0.1.0. I also don't understand why exactly the same trigger script works when executed from within SQLPlus Worksheet and doesn't work when executed from TOracleScript or PLSQL Developer.

When I say it doesn't work, I mean that the trigger is being created, but when I am examining it from PLSQL Developer, it shows up us invalid (ORA-04093). If I compile it... it keeps showing up us invalid. If I recreate it through SQLPlus Worksheet it shows up as valid.

Anyway, I am going to post the scripts on Monday hoping it is something I am doing wrong and not some nasty bug.

Petros

[This message has been edited by amiridis (edited 24 January 2003).]

 

[Petros Amiridis]

Hi,

Here is the trigger and the table definition script:

Trigger:

create or replace trigger
TR_MOM_MISTOS_ERGAZ_DEL
after delete on MISTOS.ERGAZ
for each row
declare
begin
if ld.AA_XRHSH =
MOM_CONFIG.GETACTIVEXRHSH then
SMART.WORKERS_DEL( ld.AA_ETOS,
ld.AA_XRHSH, ld.KOD_ER));
end if;
exception
when others then
MOM_ERROR.RAISEMOMSMART;
end TR_MOM_MISTOS_ERGAZ_DEL;

-----%

 

[Marco Kalter]

Thanks, I was able to reproduce this in PL/SQL Developer. In a SQL Window it worked okay, and in a Program Window is failed with ORA-04093. When I disabled the preference "Add debg information when compiling" in PL/SQL Developer, it worked okay. It seems like an Oracle Server bug to me.

------------------
Marco Kalter
Allround Automations

 

[Petros Amiridis]

What about TOracleScript? Why does it happen when I create the trigger using TOracleScript? Is there such an option there too?

Petros

[This message has been edited by amiridis (edited 27 January 2003).]

 

[Petros Amiridis]

Forget my previous message. Let me give you the big picture in order for you to understand why I was caught up in this situation:

1. I had the table I posted in a previous message.

2. I was trying to create the offending trigger through TOracleScript in one of my applications.

3. The trigger was being created succesfully (although I thought otherwise).

4. The schema, due to the re-creation of various objects made some triggers and packages invalid.

5. I was recompiling these objects through PL/SQL developer while having "Add debug information when compiling" option as true.

6. This was causing ORA-04093 in the trigger mentioned above.

7. I didn't know the fact about the "Add debug information when compiling" option and I thought it had something to do with TOracleScript itself.

Thank you for clearing up the situation.

Petros

[This message has been edited by amiridis (edited 28 January 2003).]