• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

Oracle && BDE

What component should i use to define new stored procedure (Oracle) with BDE components ?
I try to define the following sql :
CREATE OR REPLACE TRIGGER triggername
BEFORE INSERT
ON my_table  --must exist, create it if not
FOR EACH ROW
BEGIN
 IF ID IS NULL THEN
   SELECT my_seq.nextval  -- must exist, create it if not

     INTO :NEW.ID  -- :NEW missed
     FROM DUAL;
 END IF;
END;

in TQuery.sql property and tQuery.execSql
 and got error :
Query Field NEW is from unknown type ...
0
asi
Asked:
asi
  • 5
1 Solution
 
kretzschmarCommented:
you can't

use sql+ instead
0
 
kretzschmarCommented:
btw.
this
IF ID IS NULL THEN
 
should be
IF :NEW.ID IS NULL THEN
 
meikl ;-)
0
 
asiAuthor Commented:
Ok, i finally success to do the following steps:

1)
create table try4 (id number,text_col varchar2(80))
2)
    CREATE OR REPLACE TRIGGER Inc_Field_onTry4
    BEFORE INSERT
    ON try4  --must exist, create it if not
    FOR EACH ROW
    BEGIN
    IF :NEW.ID IS NULL THEN
    SELECT my_seq.nextval  -- must exist, create it if not
    INTO :NEW.ID  -- :NEW missed
    FROM DUAL;
    END IF;
    END;

But when i try to execute
INSERT INTO TRY4 (text_col)  VALUES('as111')  
i got ORA-04098
why ?

;-)asi
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kretzschmarCommented:
ORA-04098 trigger 'string.string' is invalid and failed re-validation
Cause: A trigger was attempted to be retrieved for execution and was found to
be invalid. This also means that compilation/authorization failed for the
trigger.
Action: Options are to resolve the compilation/authorization errors, disable
the trigger, or drop the trigger.

---

your trigger isn't valid

check if the sequence exists,
if yes, create a public synonym for the sequence

check if the table exists,
if no, create it

triggercode itself looks good now

do you have any tool, like toad?
(if not, i guess there is a trial version at www.quest.com)

btw. toad is coded with delphi

it makes your life with oracle a bit easier
(is more comfortable as sql+)

meikl ;-)
0
 
kretzschmarCommented:
here you can download the trial from toad

http://www.quest.com/requests/?RequestDefID=49

if you have it, you can examine
your trigger and find out what is missed

meikl ;-)
0
 
kretzschmarCommented:
hello?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now