Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Control how Long Varchar columns are displayed (Read 6531 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Control how Long Varchar columns are displayed
30.12.05 at 11:52:31
 
Long varchar columns (data type Long in Oracle ; data type Text in SQL Server) usually contain a lot of text. In Oracle you can store up to 2GB of text in a Long column. We introduced a way to control how many characters of this text you wish to see in Excel.  
 
We built in support for the set long SQL*Plus statement: set long n where n is the number of characters you wish SQL*XL to put in the cell. If you run the following commands only the first 25 characters of a long column will be displays:
set long 25;
select my_long_column from my_table;

 
See:
The Set command:
http://www.oraxcel.com/projects/sqlxl/help/commands/set.html
Back to top
 
« Last Edit: 30.12.05 at 11:53:00 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Control how Long Varchar columns are displayed
Reply #1 - 09.02.06 at 11:55:59
 
Support for the set long command has been added to the SQL editor. It will be available in SQL*XL 4.0.40
 
Syntax:
 
set long n
 
Where n is a number
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Control how Long Varchar columns are displayed
Reply #2 - 15.02.06 at 14:44:19
 
Raelyn from the USA noticed the following:
 
Question:
Even setting long to 200, the long value appears to stop at 100 characters in OraOLEDB for me.
 
Answer:
In OraOLEDB you need to give a special parameter when connecting to the database to set the size. Currently (latest release is SQL*XL 4.0.39) there is no facility in SQL*XL to do this but I will take this on board in on of the next releases. I am working on a replacement for the connection dialog. It should also be possible to edit the connection string. I will send you a registry hack privately so you can make this change now.
 
You need to add the following to the connection string:
ChunkSize=500;  
 
The number for chunksize should be between 1 and 65535.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1