Creating an Oracle trigger using ociexecute

ruski

I'm trying to create the following trigger

create or replace trigger mdlink_oid_trig
before insert on mdlink for each row
begin
select mdlink_oid_seq.nextval into :new.oid from dual;
end;

using php via ociexecute(). I get a database error 24344 when I run the query, and when I try to using the trigger, I get an error that says

ORA-04098: trigger 'PHP.MDLINK_OID_TRIG' is invalid and failed re-validation

Running show errors on trigger MDLINK_OID_TRIG in SQLPlus tells me

Errors for TRIGGER MDLINK_OID_TRIG:

LINE/COL ERROR

1/6 PLS-00103: Encountered the symbol "" when expecting one of the
following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
The symbol "" was ignored.

2/57 PLS-00103: Encountered the symbol "" when expecting one of the

LINE/COL ERROR

following:
begin declare end exception exit for goto if loop mod null
pragma raise return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>

As long as I end the trigger with a '/', I can create it via SQLPlus.

Why won't this work via ociexectue()? What's the trick?

bastien

why not insert with

"values (mysequenceName.Nextval, field2, field3..fieldn)";

ruski

Thanks for the insert suggestion. However, I found the problem:

I appears that creating triggers with imbedded end of line characters does not work via the ociexecute function.

My situation was this -

My trigger was in a text file.
Reading the text file using php and using ociexecute() to build the trigger did not work (it compiled with errors).
Copying and pasting the same text into SQLplus worked.

A little debugging showed I was blowing up at the EOL characters.

Must be that SQLplus filters out the end of line characters, whereas they were still in the ociexecute text.

My solution was to replace the EOL chars prior to parsing the trigger text:

$query = str_replace(chr(13), ' ', $query);
$query = str_replace(chr(10),' ', $query);

Hope this helps somebody down the road.

roibm

ruski wrote:
My solution was to replace the EOL chars prior to parsing the trigger text:

$query = str_replace(chr(13), ' ', $query);
$query = str_replace(chr(10),' ', $query);

Hope this helps somebody down the road.

well... guess what... it helped me... thanks!
and yes, more than 2 years later, the bug is still there.... and it looks like it was there as early as 2001, saw some posts complaining about this. way to go php, way to go...