• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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