Query Tool / Oracle : itgenefd016: Scale is set while precision isn't

Questions
,
1

On Invantive Query Tool Beta 20.1.301, the issue when precision and scale are not correct with Oracle is still present

Do you have any clue how to overcome this ?

Capture d’écran 2020-12-11 à 10.13.12
Capture d’écran 2020-12-11 à 10.13.122512×1738 567 KB

Capture d’écran 2020-12-11 à 10.46.25
Capture d’écran 2020-12-11 à 10.46.253060×1720 490 KB 

itgenefd016: Scale is set while precision isn't.Bericht ID: ef6a3851-539f-4ccf-8f32-ad7d2088348cOpgetreden (UTC): 11-12-2020 09:13:04Retrieval object metadata for '.MARINA.MA_BOOTE'Invantive.Basics.InvantiveSqlException
   at Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String messageText, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable)
   at Invantive.Basics.InvantiveSqlException..ctor(GlobalState owner, ExecutionOptions executionOptions, String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId)
   at Invantive.Data.DatabaseColumn..ctor(GlobalState owner, ExecutionOptions executionOptions, Int32 position, String columnName, String dotnetDataType, String databaseDataType, Nullable`1 maximumLengthCharacters, Boolean fetchByDefault, String documentation, String example, String displayControl, Nullable`1 precision, Nullable`1 scale, String labelSingular, String labelPlural, String labelSingularPrefix, String labelSingularPostfix, String labelPluralPrefix, String labelPluralPostfix, Nullable`1 nullable, Boolean derivedValue, Boolean influencesInsert, Boolean userChangeable, String labelHeading, Nullable`1 displayOrder, Nullable`1 displayLengthAverage, Nullable`1 displayLengthSD, Boolean displayInFilter, Boolean displayInLOV, Boolean displayInRecord, Boolean displayInResults, Boolean checkSpelling, Boolean canFilterServerSide, Int32 minimumLengthCharacters, TextPadding minimumLengthPadding, String sourcePath, String origin)
   at Invantive.Data.Windows.Providers.OracleManaged.OracleManagedProvider.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, Connection connection, IEnumerable`1 objectSpecifications, Boolean raiseErrorIfNotFound)
   at Invantive.Data.AnsiSqlProviderBase.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, ObjectSpecification objectSpecification, Boolean raiseErrorIfNotFound)
   at Invantive.Sql.V1.DataSourceOrFunctionTree.OnExecute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.QueryTree.Execute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.SelectStatementTree.OnExecute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.QueryTree.Execute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.QueryPlan.ForcePlan(GlobalState owner, ExecutionOptions executionOptions)
   at Invantive.Sql.V1.QueryPlan.Fetch(GlobalState owner, ExecutionOptions executionOptions, ObjectDefinition objectDefinition, String sqlStatement)
   at Invantive.Data.ConnectionManager.DX.T(QueryPlan )
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   at Invantive.Producer.Windows.Forms.QueryToolResults.AJF(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean )
   at Invantive.Producer.Windows.Forms.QueryToolResults.MSF(GlobalState , ExecutionOptions , String , ParameterList )
   at Invantive.Producer.Windows.Forms.QueryToolResults.QI.T()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
   at Invantive.Data.DatabaseColumn..ctor(GlobalState owner, ExecutionOptions executionOptions, Int32 position, String columnName, String dotnetDataType, String databaseDataType, Nullable`1 maximumLengthCharacters, Boolean fetchByDefault, String documentation, String example, String displayControl, Nullable`1 precision, Nullable`1 scale, String labelSingular, String labelPlural, String labelSingularPrefix, String labelSingularPostfix, String labelPluralPrefix, String labelPluralPostfix, Nullable`1 nullable, Boolean derivedValue, Boolean influencesInsert, Boolean userChangeable, String labelHeading, Nullable`1 displayOrder, Nullable`1 displayLengthAverage, Nullable`1 displayLengthSD, Boolean displayInFilter, Boolean displayInLOV, Boolean displayInRecord, Boolean displayInResults, Boolean checkSpelling, Boolean canFilterServerSide, Int32 minimumLengthCharacters, TextPadding minimumLengthPadding, String sourcePath, String origin)
   at Invantive.Data.Windows.Providers.OracleManaged.OracleManagedProvider.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, Connection connection, IEnumerable`1 objectSpecifications, Boolean raiseErrorIfNotFound)
   at Invantive.Data.AnsiSqlProviderBase.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, ObjectSpecification objectSpecification, Boolean raiseErrorIfNotFound)--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.AnsiSqlProviderBase.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, ObjectSpecification objectSpecification, Boolean raiseErrorIfNotFound)--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.AnsiSqlProviderBase.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, ObjectSpecification objectSpecification, Boolean raiseErrorIfNotFound)
   at Invantive.Sql.V1.DataSourceOrFunctionTree.OnExecute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.QueryTree.Execute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.SelectStatementTree.OnExecute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.QueryTree.Execute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.V1.QueryPlan.ForcePlan(GlobalState owner, ExecutionOptions executionOptions)
   at Invantive.Sql.V1.QueryPlan.Fetch(GlobalState owner, ExecutionOptions executionOptions, ObjectDefinition objectDefinition, String sqlStatement)
   at Invantive.Data.ConnectionManager.DX.T(QueryPlan )
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   at Invantive.Producer.Windows.Forms.QueryToolResults.AJF(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) in C:\Users\gle3.WS212\Documents\ws-master\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolAvailableObjectsViewModel.cs:line 55
   at Invantive.Producer.Windows.Forms.QueryToolResults.MSF(GlobalState , ExecutionOptions , String , ParameterList ) in C:\Users\gle3.WS212\Documents\ws-master\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolAvailableObjectsViewModel.cs:line 30
2

When launching the query a second time after the above described error, I get a FireHoseResultSet error:

Capture d’écran 2020-12-11 à 10.52.57
Capture d’écran 2020-12-11 à 10.52.572456×1804 528 KB 

itgensql160: Row length 38 doesn't equal the field count 12 on row #0. Type 'FirehoseResultSet'.Bericht ID: caa7be56-5a4e-41d3-935f-1fd799f17ef6Opgetreden (UTC): 11-12-2020 09:52:49select * from MARINA.MA_BOOTE@MM_NAARDENInvantive.Basics.InvantiveSystemException
   at Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String messageText, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable)
   at Invantive.Basics.InvantiveSystemException..ctor(GlobalState owner, ExecutionOptions executionOptions, String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId)
   at Invantive.Sql.V1.FirehoseResultSet.LR.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__8.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.UR.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.LR.MoveNext()
   at Invantive.Sql.V1.ChainedFirehose.CR.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.LR.MoveNext()
   at Invantive.Sql.V1.SelectListIterator.JI.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.LR.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__8.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.UR.MoveNext()
   at Invantive.Sql.V1.MaterializeSparseArrayIterator.<Iterator>d__1.MoveNext()
   at Invantive.Sql.V1.QueryPlan.KI.MoveNext()
   at Invantive.Data.ExtensionMethods.SLF`1.ALF.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.DX.T(QueryPlan )
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   at Invantive.Producer.Windows.Forms.QueryToolResults.AJF(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean )
   at Invantive.Producer.Windows.Forms.QueryToolResults.MSF(GlobalState , ExecutionOptions , String , ParameterList )
   at Invantive.Producer.Windows.Forms.QueryToolResults.QI.T()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
   at Invantive.Sql.V1.FirehoseResultSet.LR.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__8.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.UR.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.LR.MoveNext()
   at Invantive.Sql.V1.ChainedFirehose.CR.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.LR.MoveNext()
   at Invantive.Sql.V1.SelectListIterator.JI.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.LR.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__8.MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.UR.MoveNext()
   at Invantive.Sql.V1.MaterializeSparseArrayIterator.<Iterator>d__1.MoveNext()
   at Invantive.Sql.V1.QueryPlan.KI.MoveNext()
   at Invantive.Data.ExtensionMethods.SLF`1.ALF.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.DX.T(QueryPlan )
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters)
   at Invantive.Producer.Windows.Forms.QueryToolResults.AJF(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) in C:\Users\gle3.WS212\Documents\ws-master\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolAvailableObjectsViewModel.cs:line 55
   at Invantive.Producer.Windows.Forms.QueryToolResults.MSF(GlobalState , ExecutionOptions , String , ParameterList ) in C:\Users\gle3.WS212\Documents\ws-master\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolAvailableObjectsViewModel.cs:line 30
3

The problem with itgenefd016 on Oracle RDBMS occurs with some exotic data types on Oracle (traditionally people used double and float on SQL Server, even for financial data (yuk), but I’ve never seen them on Oracle). The metadata was behaving differently than expected. Last week BETA release should further finetune the Oracle drivers (both native SQL*Net as managed version) in this aspect. Looking forward to hear your experiences!

4

When I understand correctly, the query executed is the select-part of:

create or replace table jhn_boote@sql2019
as
select *
from   marina.ma_boote@mm_naarden
limit 10

The select raises an itgensql160 error which is an integrated check of the data retrieved by Invantive SQL from the backing platform (here probably Oracle) against the metadata retrieved earlier from the data source.

Sofar, I have only seen it occur when the metadata has changed between logging on and retrieving or uploading data. For instance, when you drop the table on Oracle and re-create it with columns different in type, name or number of, the itgensql160 error should occur.

Invantive SQL assumes that the metadata is stable for the duration of a session. On some platforms, like Teamleader or AFAS Profit, the metadata will be cached aggressively so it can take even hours for new custom fields or GetConnectors to show up.

In these scenarios it is a quick fix to just log on to the virtual database again. The metadata will be reloaded then.

Did logging on again help in your case?

If yes or not, please add it to the topic.

In the future the behaviour of the SQL engine might be adapted to avoid an itgensql160 error, such as reloading the metadata and re-running the query when it would otherwise have been triggered. But as of today, no decision yet has been made regarding that scenario; we are still collecting experiences.

PS. It is best to create a separate question/thread for each issue. That makes it easier to handle them.

5

Hi, only one problem is raised here: it is the Oracle exotic metadata.
Unfortunately 20.1.306 BETA does not solve the issue :

Capture d’écran 2020-12-21 à 09.43.06
Capture d’écran 2020-12-21 à 09.43.062360×1552 357 KB

the second point that I wanted to share (the FirehoseResultSet) appears when the statement is launched a second time in the same session, eg:

1st statement execution : select * from marina.ma_boote@mm_naarden limit 10
get a Scale is present while precision isn’t error

2nd statement execution : select * from marina.ma_boote@mm_naarden limit 10
get a FirehoseResultSet error

With the session logoff/logon , relaunching the statement will get again a Scale/presicion error (followed again by a Firehose error if launched a second time).

It is true that I found with other jobs, when the session remains open while the metada-data is changed, the the statement raises a itgensql160 error. just log-off/log-on solves the issue.

6

Good morning, BETA 20.1.313 does not solve the issue.
Is there any turnaround that I could use?
That would be wonderful if we could solve this by January 4th because I will need to sync daily this table … We are migrating some systems…

Capture d’écran 2020-12-22 à 10.51.16
Capture d’écran 2020-12-22 à 10.51.163070×1706 599 KB

7

Exotic Oracle metadata

I assume that giving naming of the table that it is a standard package, so altering the data type typically violates the support conditions. In this case, PERSONEN_ANZAHL of MA_BOOTE probably has an INTEGER data type on Oracle.

We have been able to reproduce the issue using an Oracle 11g database with tables from IGEL (some supplier of thin clients). Their tables use the INTEGER data type on Oracle. The INTEGER data type is an alias for:

number(*,0)

Note that the INTEGER data type on Oracle allows storage of values larger than the typical value range for signed or even unsigned integers (2^32). Normally, the precision of an integer should be something like

upper(32 / log(10) * log(2))

which is approximately 10 digits, but Oracle allows 38 digits nowadays.

Well, that is background. Using the reproduction case, the latest BETA has additional logic to handle the number(*, n) with n != null data types. Please re-try using release 20.1.315 available from the BETA release notes on Invantive Query Tool. The actual change is shortly commented on with the Windows drivers available on Invantive SQL.

Itgensql160 error

The itgensql160 is probably collateral damage of Invantive SQL not being able to load the metadata. It solely adds some columns and then fails. The columns added are not rolled back by design. Subsequent queries can therefor fail. Expectation is that the itgensql160 will miraculously disappear when the metadata can be loaded on 20.1.315.

8

I confirm that the issue has been addressed by 20.1.315 :heart:

9

Great to hear that the issue on Oracle has been resolved. Good luck with the migration.

10

While 20.1.315 solves the data type issue not documented by Oracle and discussed in this topic , it creates on the destination table a numeric value, whereas on Oracle, when we have number(*, n) datatype, then it is an integer.

Would that be an idea to choose instead Integer in the destination table ?

11

Yes, it would be great when the original data type would be carried along the create table ...@target as select * from ...@source.

Now Invantive SQL first maps the original data type number(*, n) (which Oracle knows to represent as an integer) to a matching type (probably decimal with at most 128 digits, but in this case 38 digits). And then to a type supported by the target database system.

I am sad to report there is currently no feature available to carry forward the original data type across the process using create table. There are also no plans to add support for this feature since the use of data types such as double and integer on Oracle is not common for real Oracle packages.

As a workaround you could manually fill the table and add an explicit cast(COLUMN as integer) to the select part of the create table as in:

create or replace table COPY@targetsqlserver
as
select t.* except specialcolumn1, specialcolumn2
,      cast (t.specialcolumn1 as integer) specialcolumn1
,      cast (t.specialcolumn2 as double) specialcolumn2
from   SOURCE@source_on_oracle

The select * except COLUMNS is an Invantive SQL extension to the ANSI SQL standard. It enables users to include all columns except a few specific columns that are handled differently. This extension reduces the amount of SQL needed for many transformation processes.