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
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 `***'
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
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.
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
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
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
