Question

ORA-01756: quoted string not properly terminated only when using Published paramater

  • 12 October 2019
  • 6 replies
  • 28 views

Hi, im using FME in an oracle database copying data from one table to another

I am trying to put a published parameter on a "WHERE clause..".

If i write the whole query, everything is OK:

"DE" in (SELECT CZ FROM REF_CZ WHERE MONO = 'BORË')':

but,

If i put a part of it as a published parameter i get this error:

Query: "DE" in (SELECT CZ FROM REF_CZ WHERE MONO = '$(P1)')

Field type is a string.

Error is : ORA-01756: quoted string not properly terminated'. (serverType=`ORACLE8i'..........

 


6 replies

Badge +16

In your post there is an extra bracket, in the where clause with the parameter, is that a typo?

Userlevel 5

What exactly is the value of the published parameter P1?

Also, can you please post the entire error message.

In your post there is an extra bracket, in the where clause with the parameter, is that a typo?

its not a typo, it is because its a string field not a number and in oracle it needs the bracket, at least when i do it as above when i put directly the value 'BORË' WHICH WORKS

What exactly is the value of the published parameter P1?

Also, can you please post the entire error message.

The value for example is 'BORË', field type is string that's why i putt it on bracelet.

`ORA-01756: quoted string not properly terminated'. (serverType=`ORACLE8i', serverName=.............

Badge +3

@shefqetlulja

Following in a sql executor with works:

 

##testtablecreation##

 

with REF_CZ as (select 'BORË' as MONO, 'tst' as CZ from dual)

 

,bla as (select 'tst' as DE from dual)

 

 

##yourquery##

 

 

select * from REF_CZ,bla

 

where "DE" in (select CZ from REF_CZ where mono = '$(P1)')

 

No errors on FME(R) 2018.0.0.2 (20180414 - Build 18301 - WIN64)

Are you sure the error is caused by this part of the query?

AS @david_r asked, maybe post the entire error message?

Badge +8

In your case if your parameter is actually 'BORË', then it would put the value as is WHERE MONO = ''BORË''). Adding the log around where the error is (also the warning and the info) you can see if that sheds more light. Usually the explanation of the error is around the error in the log.

In this case it will probably give you the full SQL query it tries to run, and we can help with trouble shooting that. The current error could be anything in the query.

Reply