Importing data from SQL sources with query file
Summary
This operator imports data from MSSQL, ORACLE, PostgreSQL or ODBC data sources storing the query into a TIS File.
Please note that from V 7.0, due to security reason the query files have to be uploaded to a specific TIS Files folder, namely \TIS\$SysFiles\SQLQueries. |
Configuration
Settings
Overview
Screenshot | |
---|---|
Query file reference |
|
Want to learn more?
This operator imports data from MSSQL, ORACLE or ODBC data sources
Troubleshooting
ORACLE:
Version 6.9 and higher uses the odp.net managed dataaccess client from ORACLE.
The managed dataaccess client does not use the registry settings any longer.
This means, that no oracle-client ist needed anymore and settings in tnsnames.ora are not used any longer.
ORACLE - Datasources:
- For Version 6.9 and higher it is necessary to change the connection string to a fully qualified tnsnames.ora like connection string.
- For Version 6.9 consider using the culture parameter in the functions to_number and to_date if you import data from another culture than the current managed dataaccess client culture.
to_date
select
to_date(
'01DEC2022'
,
'ddMonYYYY'
,
'NLS_DATE_LANGUAGE = American'
)
from
dual;
returns
TO_DATE(
'01DEC2022'
-------------------
2022-12-01 00:00:00
to_number
select
to_number(
'1,125.14'
,
'9G999D99'
,
' NLS_NUMERIC_CHARACTERS = '
'.,'
''
)
from
dual;
returns
TO_NUMBER(
'1,125.14'
,
'9G999D99'
,
'NLS_NUMERIC_CHARACTERS='
'.,'
''
)
----------------------------------------------------------------
1125.14
-- The first character in NLS_NUMERIC_CHARACTERS is the decimalpoint
-- The second character in NLS_NUMERIC_CHARACTERS is the grouping character (thousand separator).
Related topics
- Operators (alphabetical overview)
- Importing data from SQL sources 5.0
- Get the information which database is actually in use via XIH Functions in the Formula operator