Home » Open Source » Programming Interfaces » Creating Trigger via PHP
Creating Trigger via PHP [message #189755] Sat, 26 August 2006 09:00 Go to next message
figrin
Messages: 4
Registered: August 2006
Junior Member
Hi,

Trying to create triggers dynamically via PHP. Is this possible?

I am trying doing this at the moment:
$s = oci_parse($c, <<<SQL
CREATE OR REPLACE TRIGGER {$tbname}_TRG
BEFORE INSERT ON {$tbname} FOR EACH ROW
BEGIN
SELECT {$tbname}_SEQ.nextval INTO :new.FEED_ID FROM DUAL;
END;
SQL
);
oci_execute($s);

where $tbname is a changing variable to a table name I have just created.

PHP returns this:
OCI_SUCCESS_WITH_INFO: ORA-24344: success with compilation error

While in dba_errors table it returns:
Line 1, Pos 6: PLS-00103: Encountered the symbol "" when expecting one of the following ...
Line 2, Pos 79: PLS-00103: Encountered the symbol "" when expecting ...

When I run that statement within sqldeveloper/sqlplus it compiles fine.

Any help would be appreciated. Thanks
Re: Creating Trigger via PHP [message #189864 is a reply to message #189755] Mon, 28 August 2006 03:27 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Do You run it with using the same user?

Can You succesfully recompile the trigger, which are created with errors?
- This You can do using SQL*Plus. Should the error reoccur, then enter "show error", and You should get the line which fails.

Br
Kim
Re: Creating Trigger via PHP [message #189983 is a reply to message #189755] Mon, 28 August 2006 20:49 Go to previous messageGo to next message
figrin
Messages: 4
Registered: August 2006
Junior Member
Yes same user, recompile in sqlplus with no errors (and in sqldeveloper, I open up the trigger saved from PHP with no editing and click compile, no errors), PHP just doesn't like it.
Re: Creating Trigger via PHP [message #190324 is a reply to message #189755] Wed, 30 August 2006 04:05 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Have You tried adding a trailing slash "/" ?

- it complains about something missing in line 6, which You do not have....

Br
Kim
Re: Creating Trigger via PHP [message #190335 is a reply to message #190324] Wed, 30 August 2006 04:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
try to echo the statement instead of executing it. I think the contents of $tbname could contain something unexpected.

@kimant: it says Pos 6, not line 6
Re: Creating Trigger via PHP [message #190630 is a reply to message #189755] Thu, 31 August 2006 08:27 Go to previous messageGo to next message
figrin
Messages: 4
Registered: August 2006
Junior Member
Yep the php is fine when echoed back. I should make it more clear, when I enter the statement via PHP I get the ORA-24344 error, and immediately I go into sqldeveloper and recompile the trigger, and that recompiles fine.
Re: Creating Trigger via PHP [message #190758 is a reply to message #190630] Fri, 01 September 2006 02:30 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

I never got to ask this question, but it is coming back to every time:

Why would You create triggers using PHP?
- Do You dynamically create schema's?

An alternative approach would be to have the PHP code call a stored procedure, which creates the trigger....

Br
Kim
Previous Topic: OCI/PHP Make Error
Next Topic: perl DBD
Goto Forum:
  


Current Time: Sat Dec 20 20:48:38 CST 2014

Total time taken to generate the page: 0.11806 seconds