mORMot Open Source

mattsbg

Member

Registered: 2017-05-23

Posts: 20

SynDBOracle - Truncated column with bound parameters

Hi,

if we use the following query

select bound from (Select :bound bound from dual union all Select :bound2 bound from dual) order by 1

the column named "bound" returns ''.

SynLog reveals, that the column has been truncated.

20170621 09311207 SQL   TOurOracleStatement(04242EE0) select bound from (Select 'Testinhalt' bound from dual union all Select 'Testinhalt2' bound from dual) order by 1
20170621 09311207 DB    Truncated column BOUND

If the query is run again, it works due to the query cache. Disabling the query cache always generates this error.

The logging is called in the TSQLDBOracleStatement.GetCol-method.

Can anyone confirm this or have an idea of how to solve it?

thanks for your response,
Matthias

Last edited by mattsbg (2017-06-21 10:00:15)

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,539

Website

Re: SynDBOracle - Truncated column with bound parameters

This is as expected. Try to swap a param values (first  longer when 2nd) end error disappear.
Or change your subquery to indicate a column type.  Select cast(:bound as varcnar(100)) as bound from dual union ....

mattsbg

Member

Registered: 2017-05-23

Posts: 20

Re: SynDBOracle - Truncated column with bound parameters

Thanks for your suggestions!

Swapping columns did not help, but casting the data does the trick.

But why is it as expected? If I execute the same query using SynDBFireDAC, no errors will occur. Also using TOAD would not cause an error!

thanks,
Matthias

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,539

Website

Re: SynDBOracle - Truncated column with bound parameters

But why is it as expected? If I execute the same query using SynDBFireDAC, no errors will occur. Also using TOAD would not cause an error!

Because we can't determinate TYPE of resulting column in query

Select :bound bound from dual union all Select :bound2 bound from dual

without knowledge of parameter values.

I think the same error should be in TOAD in case you execute a query with params (reset a server-side statement cache first)

mattsbg

Member

Registered: 2017-05-23

Posts: 20

Re: SynDBOracle - Truncated column with bound parameters

Of course I can follow that this is the expected behaviour.

But just to let you know, tested in TOAD 12.9.0.71 it just works never having executed this query on the instance.

Also as mentioned before it just works if using SynDBFireDAC.

thanks, Matt

mattsbg

Member

Registered: 2017-05-23

Posts: 20

Re: SynDBOracle - Truncated column with bound parameters

Testing continued, other than with the previous query just truncating the data this query results in an ORA-01406 exception.

select bound from (
    Select :bound bound from dual connect By Rownum <= 100001
    union all
    Select :bound bound from dual connect By Rownum <= 100001
) order by 1

If using only 10001 rows instead, it works. It also does work if executed the second time (from cache).

In TOAD 12.9.0.71 both variants work.

thanks,
Matthias

Last edited by mattsbg (2017-07-04 07:21:21)