How to use varchar2( x char) datatype insteat of varchar2(x byte)
Hi, I am hope I am right here. I want know if it is possible to use in Talend varchar2(x char) datatype when I use the "create table" component or other create table operation. Talend use automatically varchar2(x byte) for creation but I need varchar2(x char). The requirement is not to use the i.e. the tOracleRow component with an ordinary sql! The requirement is to use the "create table" component with defined schema in Talend. I use Talend Studio 5.5.1 Thanks in advance & regards Michael
Yes it is. You can change the behaviour Talend maps the data types in the Preferences: Preferences->Talend->Specific Settings->Metadata of Talend Types. The mapping is configured in xml files for every database type. The mapping consists of 3 major parts: In the first tag dbtypes you find all types provided by the database. The next section is the tag talendToDbTypes: Change here the default db type for id_String to VARCHAR2. This change must be done in all studios which are works on the same project.
The last section is the mapping from the db type to the Talend type.
I'm not sure that is what micwed meant jlolling. They want to create a Varchar2 using char to size the field rather than byte (I am assuming something to do with multibyte characters). My first thought was to look where you suggested as well, but there is nothing in there to set by which datatype (byte or char) the Varchar2 columns are sized. I am out of ideas and think that the only way of forcing the creation of the tables using char over byte might be to create them manually. However, I would love to be proven wrong.
You are right, I miss interpret your question. But in this case I am a bit confused where your problem is. You can simply multiply the size in char to have the size in byte (OK this is a bit larger than necessary but it works for sure). The size in the varchar2 data type is the column size in always in byte. Means you must enlarge it when using UTF-8.
If you are wanting to size your column by the number of characters expected and you are using Chinese characters (for example), you cannot guarantee that for a Varchar2(10) it will hold 10 Chinese characters. But if you define the column as varchar2(10 char), Oracle handles the sizing to guarantee that you can hold 10 characters in the field.
Since space is cheap, this example isn't necessarily a major problem. But it is functionality that would be nice to be able to use in Talend. Since neither of us have heard of this, I am going to assume that this functionality does not exist. Maybe a Jira is required.
Hi jlolling, hi rhall,
thanks for quick respond on my post. rhall figure the reason out why I am want to say explicit I need char instead of byte.
Background of my question is I build a small job to easy migrate or copy data from one environment to an other environment i.e. Prod to Dev for developing or test reasons.
My idea to use this job is. Make a Copy of the job. Retrieve the table schema of the source table. set the same schema for the destination. create the table and copy some some or all data to the destination and that's all. The problem is when in the source system use char for German umlaut or Chinese character reason Talend will build the table with the wrong datatype and on loading the data will fail. And I have more work than I want.
Yes, I can handle it with SQL script and create it first on the destination and so on and so on. But it is not very convenient and I don't want that the developer or anybody else must use more then one tool or make some copy & paste stuff of scripts or worry about the source system, you know?
Anyway, you both helped me a lot. And yes it would be imho a great enhancement when Talend allows to specify more in detail the need of some datatypes.
Thanks and regards
Michael
I have had this problem problem before, but not while using Talend. There is a workaround for you, but it will take a bit of work. When you create your schemas for Talend to build, you need to adjust the bytes used to be the max number of bytes the column will allow on your system that uses char. There are several ways to do this. But if you want to just accommodate the data that is currently in the system using char sizing, you can query your data to find the largest record in terms of bytes and use that as the size in bytes that you need to use in your Talend schema. An example of an Oracle query to do this is below....
Select Max(colSize)
From (
Select ColumnName,
lengthb(ColumnName) as colSize
TableName);
