Above or below that statement in the log file you should be able to see how the SQL is being issued to the database. Can you see if the substitution is happening as planned in the log?
Here are the lines around the error message:
Connected to ORACLE database `framme2003wg'
Environment variable ORACLE_HOME has value `D:\\apps\\oracle32\\product\\11.2.0\\client_1'
Connecting to ORACLE database `framme2003wg' with user name of `xxx' and password of `***'
Connected to ORACLE database `framme2003wg'
Opened table `DGS_SHAPE:WPDBA.DGS_SHAPE' for input with server type `ORACLE8I_DB', server name `framme2003wg', user name `', database name = `';
ORACLE Reader: Using Oracle 8 SC Reader to read tables from database `', server type `ORACLE8i', server name `framme2003wg', user name `xxx', password `***'
ORACLE Reader: Using Rich geometry.
Connected to ORACLE database `framme2003wg'
An error occurred while accessing a table result for feature type `__fme_execute_sql_results__'; message was `Execution of statement `select' did not succeed; error was `ORA-00936: missing expression'. (serverType=`ORACLE8i', serverName=`framme2003wg', userName=`xxx', password=`***', dbname=`')' (server type is `ORACLE8i', server name is `framme2003wg', username is `xxx', password is `***', database name is `')
@Relate: Failed to execute prefetch query. Continuing with incomplete cache
ORACLE Reader: Executing SQL Statement `SELECT "WPDBA"."DGS_SHAPE"."FILENAME", "WPDBA"."DGS_SHAPE"."RB_PRMRY" FROM "WPDBA"."DGS_SHAPE" WHERE FILENAME = :binding0' to obtain features for feature type `WPDBA.DGS_SHAPE'
I do not see the SQL for the query that should be returned from the Python script. I believe the query that is being shown is the "default" query that would be run if I didn't specify a prefetch query.
Ok, this is weird. I added the print statement and confirmed that the Python script is formatting the SQL select statement correctly as expected. However, what I see in the error message
Execution of statement `select' did not succeed
is telling me that the SQL passed to Oracle was just "select". If I change the Python script and take out all the spaces in the select statement I am formatting I see an error message like
Execution of statement `selectrb_prmry,lower(trim(replace(filename,'.dgn',NULL)))filenamefromWPDBA.DGS_SHAPE' did not succeed
It appears that my SQL statement returned by the Python script is getting truncated at the first space in the string before it is being sent off to Oracle. Is there some setting to preserve spaces within a parameter string?
I've had some assorted challenges like this
before and I wonder if you're hitting a similar issue. Can you try different variations of quotes and double quotes. I'm pretty sure you might even need to try doubling up your escape characters. Sorry I can't be more help, I'm not clear on the rules of how the statement is being parsed so it might be a matter of trial and error. If you hit the wall then escalate the issue direct to Safe using the support option above. Best of luck.
Hi,
if you're passing parameter values to FME over the command line that contains spaces, you will have to enclose them with double quotation marks ("...") to prevent the command line interpreter from splitting them. Example:
fme.exe myworkspace.fmw --Param1 "A parameter value" --Param2 "Another value"
The quotation marks themselves will not be passed on to FME.
David
That was the answer. If I return the SQL statement with double quotes around it from the Python script, the SQL is passed and executed correctly in the Joiner.
Thanks to all for the suggestions!!
Steve
