Question

I am trying to execute the following SQL to create a trigger

  • 7 October 2015
  • 7 replies
  • 92 views

Badge
Hi Im trying to set up a trigger for a table using FME and so far not having much success, any help would be much appreciated.

 

 

Heres what I have in the SQLExecutor :

 

 

FME_SQL_DELIMITER;

 

 

set define off

 

create or replace 

 

trigger DECISION_ID_$(ENC_ID)

 

BEFORE INSERT ON DECISION$(ENC_ID)

 

FOR EACH ROW

 

BEGIN

 

     SELECT SEQ_DECISION.nextval into :new.id from dual;

 

END;

 

 

heres what it looks like in the log :

 

 

SQLExecutor_DECISION_TRIGGER(QueryFactory): Performing query against ORACLE8I dataset `XXXX.net\\orcl'

 

Delimiter token must be declared at beginning of file. Ignoring delimiter and continuing. SQL is: 'FME_SQL_DELIMITER;

 

 

set define off

 

create or replace

 

trigger DECISION_ID_123

 

BEFORE INSERT ON DECISION123

 

FOR EACH ROW

 

BEGIN

 

SELECT SEQ_DECISION.nextval into :new.id from dual;

 

END;'

 

ORACLE Reader: Using Oracle 8 SC Reader to read tables from database `', server type `ORACLE8i', server name `XXXX\\orcl', user name `XXXX', password `***'

 

ORACLE Reader: Using Rich geometry.

 

ORACLE Reader: Executing SQL Statement `FME_SQL_DELIMITER;

 

set define off

 

create or replace

 

trigger DECISION_ID_123

 

BEFORE INSERT ON DECISION123

 

FOR EACH ROW

 

BEGIN

 

SELECT SEQ_DECISION.nextval into :new.id from dual;

 

END;' to obtain features for feature type `QueryFactory_SQLExecute'

 

FME Configuration: No destination coordinate system set

 

Writer `ORACLE8I_1' of type `ORACLE8I' using group definition keyword `ORACLE8I_1_DEF'

 

Using Oracle 8 SC Writer to write tables to database `', server type `ORACLE8I', server name `UKHO-test-XXXX.net\\orcl', user name `XXXX', password `***'

 

Oracle Writer: Starting at transaction 0 with a transaction interval of 1000

 

 

No real mention of am error but my trigger does not appear, the same SQL works fine in ORACLE, anyone know whay its not working?

 

 

(I have replaced usernames and address's with XXXX)

 

 

 

7 replies

Userlevel 4
Hi

 

 

The most important part is that you need to drop the FME_SQL_DELIMITER from the SQLCaller. You will get warnings in the FME log, but you can ignore them.

 

 

You will probably also have to skip "set define off", as I suspect it is SQL*plus only.

 

 

David
Badge
Hi David,

 

 

Thanks for the response.

 

 

If I dont use SET DEFINE OFF I get this problem :

 

 

Unable to bind column named `:new': error was `ORA-01036: illegal variable name/number'. (serverType=`ORACLE8i', serverName=`XXXX.net/orcl', userName=`XXXX', password=`***', dbname=`')

 

Unable to bind column named `%5': error was `%6'. (serverType=`%0', serverName=`%1', userName=`%2', password=`***', dbname=`%4')

 

A fatal error has occurred. Check the logfile above for details

 

A fatal error has occurred. Check the logfile above for details

 

 

It thinks I need to bind the new variable but setting define off seems to work fine and avoids this error.

 

 

If I drop the FME_SQL_DELIMITER bit so my executor looks like :

 

 

SET DEFINE OFF

 

create or replace

 

trigger DECISION_ID_$(ENC_ID)

 

BEFORE INSERT ON DECISION$(ENC_ID)

 

FOR EACH ROW

 

BEGIN

 

     SELECT SEQ_DECISION.nextval into :new.id from dual;

 

END;

 

 

I still have no trigger created and the log looks like so :

 

 

SQLExecutor_DECISION_TRIGGER(QueryFactory): Performing query against ORACLE8I dataset `UKHO-test-1spatial.cloudapp.net\\orcl'Semicolons detected in SQL. May need to use FME_SQL_DELIMITER keyword to specify a delimiter. SQL is: 'SET DEFINE OFF

 

create or replace

 

trigger DECISION_ID_123

 

BEFORE INSERT ON DECISION123

 

FOR EACH ROW

 

BEGIN

 

SELECT SEQ_DECISION.nextval into :new.id from dual;

 

END;'

 

ORACLE Reader: Using Oracle 8 SC Reader to read tables from database `', server type `ORACLE8i', server name `XXXX.net\\orcl', user name `XXXX', password `***'

 

ORACLE Reader: Using Rich geometry.

 

ORACLE Reader: Executing SQL Statement `SET DEFINE OFF

 

create or replace

 

trigger DECISION_ID_123

 

BEFORE INSERT ON DECISION123

 

FOR EACH ROW

 

BEGIN

 

SELECT SEQ_DECISION.nextval into :new.id from dual;

 

END;' to obtain features for feature type `QueryFactory_SQLExecute'

 

FME Configuration: No destination coordinate system set

 

Writer `ORACLE8I_1' of type `ORACLE8I' using group definition keyword `ORACLE8I_1_DEF'

 

 

Using Oracle 8 SC Writer to write tables to database `', server type `ORACLE8I', server name `XXXX.net\\orcl', user name `XXXX', password `***'

 

 

 

 
Userlevel 4
Hi

 

 

Try quoting your colons (add a backslash before) to avoid them being interpreted as bind variables when defining the trigger:

 

 

create or replace

 

trigger DECISION_ID_$(ENC_ID)

 

BEFORE INSERT ON DECISION$(ENC_ID)

 

FOR EACH ROW

 

BEGIN

 

     SELECT SEQ_DECISION.nextval into \\:new.id from dual;

 

END;

 

 

I'm pretty sure you cannot use "set define off" in FME.

 

 

David
Badge
that doesnt stop it from getting the binding error:

 

 

Unable to bind column named `:new': error was `ORA-01036: illegal variable name/number'. (serverType=`ORACLE8i', serverName=`XXXX.net/orcl', userName=`XXXX', password=`***', dbname=`')

 

Unable to bind column named `%5': error was `%6'. (serverType=`%0', serverName=`%1', userName=`%2', password=`***', dbname=`%4')

 

A fatal error has occurred. Check the logfile above for details

 

A fatal error has occurred. Check the logfile above for details

 

 

I have tested putting set define off in another executor that works and it causes that one to not work so it is a problem.

 

 

If I use the command in SQLDEveloper without set define off an enter binds box comes up and you leave the null checkbox ticked and have to hit apply to continue.

 

 

Running this from FME that bind box wont pop up so it seems necessary to set define to off or set the bind up as the enter binds box does.

 

 

Is there is a way I can do what the Enter Binds box does within the SQL statement so it doent have the error? 

 

 

I'll have a mooch around see if I can find some syntax for setting up the bind, I really dont know much about bindings though.
Userlevel 4
As fas as I know, you cannot really do much in FME in regards to bind variables. I'd try the following, and if that doesn't work I recommend you ask Safe support:

 

 

FME_SQL_DELIMITER @

 

 

set define off @

 

create or replace 

 

trigger DECISION_ID_$(ENC_ID)

 

BEFORE INSERT ON DECISION$(ENC_ID)

 

FOR EACH ROW

 

BEGIN

 

     SELECT SEQ_DECISION.nextval into :new.id from dual;

 

END;

 

 

An alternative solution might be to write out the SQL statements to a text file rather than executing them with the SQLCaller. You can then execute the statements in the text file using the sql*plus command line interface after the workspace has terminated (e.g. shutdown script)

 

 

David
Badge
Thanks for the help david.

 

 

I have sent the problem to safe support to see what they come up with.

 

 

I will await thier reply before I try the textfile and shutdown script method as my scripting skills will need some

 

refreshing and I wont be able to devote much time to this until monday but it does sound like it should work.

 

 

I was wondering if I could just execute a python script to run the SQL in the workspace but not sure if a python script would have the same limitations as the SQL executor with regards to the :new variable.

 

 

Jim

 

 
Userlevel 4
I haven't tried sending SQL statements with bind-ish syntax through Python, but I would be surprised if it wasn't possible. You can also look into the cx_Oracle (https://pypi.python.org/pypi/cx_Oracle) module that allows you to connect directly to your Oracle instance from a Python script.

 

 

David

 

 

Reply