Please note that VisualCron support is not actively monitoring this community forum. Please use our contact page for contacting the VisualCron support directly.


Jon Tofte-Hansen
2017-09-27T11:44:15Z
If I run this sql in an Oracle SQL task:
select 'a ',' b',' ',chr(13)||chr(10) from dual

The output is this (with " as text qualifier):
"a"," b","","
"


The trailing space is removed in cell 1 (and probably 3) while the leading space in cell 2 remains. CrLf is not removed either (cell 4).
Sponsor
Forum information
Support
2017-09-27T13:13:23Z
We do not alter the result in any way. We just present it as it comes so it might be something with Oracle, ODBC or whatever type of Connection you use.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-28T10:44:24Z
Hi Henrik

The server version is 8.2.9.

The connection details:

Storage type: Database
Database type: Oracle
Data provider: Native
Connection type: Standard
Data Source: <a tnsnames entry>
Unicode: true
Convert data types: unticked

When I spool the output of SQL*Plus (also using the Oracle client) the spaces are not removed.

Should I connect otherwise?

EDIT: removed erronious comment on Data provider
Support
2017-09-28T11:28:13Z
Originally Posted by: Jon Tofte-Hansen 

Hi Henrik

The server version is 8.2.9.

The connection details:

Storage type: Database
Database type: Oracle
Data provider: Native (uses the locally installed Oracle client)
Connection type: Standard
Data Source: <a tnsnames entry>
Unicode: true
Convert data types: unticked

When I spool the output of SQL*Plus (also using the Oracle client) the spaces are not removed.

Should I connect otherwise?



For testing, please use our Native client instead.

2017-09-28_1327.png
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-28T11:46:46Z
I already use Native (and it uses the local tnsnames.ora).
Support
2017-09-28T11:48:19Z
Originally Posted by: Jon Tofte-Hansen 

I already use Native (and it uses the local tnsnames.ora).



It is not the same as the one specified. Please create a new with the one I specified.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-28T12:18:04Z
Ok - still on VC 8.2.9:
1: I created a new SQL connection using the Conneciton Wizard (Use connection guide)
2: Storage type: Database, Database type: Oracle, Data provider Native
3: Connection type: Standard, Data Source: IP/SID (ie. without using tnsnames.ora)
4: Created a new job with a new SQL task (and a write to file task) with this SQL (deliberately swapping the space position):
select ' a','b ',' ' from dual


The output (also when writing to a file) is unfortunately still removing trailing spaces (in Output, Field separator is Comma and Text qualifier is "):
" a","b",""


The database version: 11.2.0.4.0 (same result against 9.2.0.8.0).
Support
2017-09-28T12:32:20Z
Unfortunately this seems to be something from Oracle. Not sure if you get the same from ODBC or OLEDB but it is clear that VC by itself does not alter the output.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-28T12:58:54Z
Hi Henrik

Thanks for the answer.

I admit that this particular case might not be a big problem, but I find it hard to believe that an Oracle produced driver would modify the output.
Support
2017-09-28T13:01:25Z
Originally Posted by: Jon Tofte-Hansen 

Hi Henrik

Thanks for the answer.

I admit that this particular case might not be a big problem, but I find it hard to believe that an Oracle produced driver would modify the output.



What you have a table with data date contains a blank space?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-28T13:16:02Z
I discovered this because I moved a Java-program (that produced a list with a JDBC driver) to VisualCron. In the VC output some fields now were empty and not with one or more spaces.

The receiving program can handle this, so I don't have an acute problem, but I still think the output should be exact. Thank you for looking into this.
Support
2017-09-28T13:17:59Z
Originally Posted by: Jon Tofte-Hansen 

I discovered this because I moved a Java-program (that produced a list with a JDBC driver) to VisualCron. In the VC output some fields now were empty and not with one or more spaces.

The receiving program can handle this, so I don't have an acute problem, but I still think the output should be exact. Thank you for looking into this.



We are open to investigate further but would like to know the query above. If you have a column with content "a " for example (ending blank space) if that is trimmed or not. This way we do not just test incoming data but outgoing data only. Helps to find the culprit.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-28T13:34:21Z
Issued:
CREATE TABLE testtab
AS
    SELECT ' a' c1, 'b ' c2, ' ' c3, '        ' c4 FROM DUAL;


Selecting in the db tool TOAD (using Oracle client) the spaces are in the output.

VC (or the Native driver) still removes the trailing spaces in the output :
" a","b","",""
Support
2017-09-28T13:49:12Z
Interesting. But space is there in select if you insert "a a"?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-28T13:55:26Z
Only trailing spaces are removed ie. spaces to the right. If I eg. put an "X" at the end of the 8 spaces in c4 above the ouput is 8 spaces and an X. So obviously "a a" will be correctly outputted - otherwise the SQL task would be useless.

You can not reproduce this?
Support
2017-09-28T14:05:21Z
Originally Posted by: Jon Tofte-Hansen 

Only trailing spaces are removed ie. spaces to the right. If I eg. put an "X" at the end of the 8 spaces in c4 above the ouput is 8 spaces and an X. So obviously "a a" will be correctly outputted - otherwise the SQL task would be useless.

You can not reproduce this?



I am just double checking everything.

Here is the result when using SQL Server:

"a ","23"
"a a","55"
" b","33"

So everything works there so it is not a general problem. We have to add some logging for Oracle to detect where it goes wrong. I do not have access to any Oracle server at the moment.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-28T14:21:44Z
You can install Oracle Database 11g Express Edition to test the SQL task (on a real database 😄 ).
Support
2017-09-28T16:55:32Z
Originally Posted by: Jon Tofte-Hansen 

You can install Oracle Database 11g Express Edition to test the SQL task (on a real database 😄 ).



Unfortunately there was problems with installation and permissions. Very different from other databases. Strange thing is that we have a lot of users running Oracle. We will add some debugging for a new build for you.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Support
2017-09-28T16:59:15Z
Can you verify that when you create Oracle->Native you then select "Direct mode" in the Connection string.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2017-09-29T08:39:43Z
As stated above I used Standard and not Standard 2 - direct mode (no Oracle Client) to connect.

I have created a new connection with the second type:
Provider=Oracle;data source=<db host IP>;user=<db user>;password=<pw>;direct=true;Port=1521;SID=;Service Name=<db service name>;

But the result is the same - both a select from DUAL and from a real table: trailing spaces are trimmed.
Scroll to Top