One Star

Oracle Mixed case column names

TOS 5.3.1
When using mixed case columns names in Oracle and tOracleOutput component I find that I need to modify the Db Column metadata for each column in the table by wrapping it in escaped double quotes in order for Talend to generate valid INSERT statements.
For example if the column name is:
Measurement_Period_End
Then the Db Column must be:
\"Measurement_Period_End\"
Is there a simpler and more automated way to achieve valid INSERT statements?
9 REPLIES
Moderator

Re: Oracle Mixed case column names

Hi,
You can right click the DB connection and choose "Retrieve schema" to get the corresponding table schema. Drag schema metadata into designer and you will get the DB Column and query automatically.
Is that what you needed?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Oracle Mixed case column names

Retrieve schema produces results that generate invalid SQL. I see from your screenshots that you have uppercase column names, retrieve schema works fine in this case.
The steps to recreate the issue are:
Create an Oracle Table with Lowercase column names
Retrieve Schema - the column names are lower case when retrieved
Use tOracleOutput to insert to the table - this fails.
Moderator

Re: Oracle Mixed case column names

Hi,
I have used a MySql DB as a tester (see my pics).
What's the invalid INSERT statements? Could you please upload your trouble screenshots into forum.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Oracle Mixed case column names

MySql is not a valid test. This is an Talend and Oracle issue. I created this issue as an insert problem, but the same issue exists for select and update. The issue is that in a mixed case column name Oracle database, metadata cannot be used as it, either custom sql must be written or metadata modified.

I will use your table D design:
CREATE TABLE D
(
"id" INTEGER,
"name" VARCHAR2(256 BYTE),
"age" INTEGER,
"place" VARCHAR2(256 BYTE)
);

INSERT INTO MWEST.D ( "id", "name", "age", "place")
VALUES ( 1 ,'MIKE_WEST',999,'Shambhala');

COMMIT:
See image of import metadata
Read from table D, invalid select statement created. See image of read job and image of error:
ORA-00904: "MWEST"."D"."PLACE": invalid identifier
I can fix this by writing the sql by hand but would perfer that Talend generage a valid select statement. Here is the working sql that I wrote:
"SELECT
MWEST.D.\"id\",
MWEST.D.\"name\",
MWEST.D.\"age\",
MWEST.D.\"place\"
FROM MWEST.D"
Now replicate the insert error:
See image of write job and write error. Same error as select.
ORA-00904: "PLACE": invalid identifier
Fix is to manualy modify the dbColumnMetadata as show in the image
Unfortunately the metadata fix for tOracleOutput does not work for tOracleInput. If I try to use the working output metadata to generate the tOracleInput query I get this invalid sql.
"SELECT
MWEST.D.\"\"id\\",
MWEST.D.\"\"name\\",
MWEST.D.\"\"age\\",
MWEST.D.\"\"place\\"
FROM MWEST.D"
So the complete solution is to always manually create the input query and always manually modify the dbColumn metadata for output.
What do I need to do for Talend to generate valid select / insert / update Oracle sql for mixed case columns?
Seventeen Stars

Re: Oracle Mixed case column names

It is a mistake what the Talend SQL generation process always do: If one enclosure a identifier in " this means take it exactly in the case sensitive writing.
Erase all enclosures " and it works without any problems.
1. NEVER use " in create statements !!!!!
2. NEVER use " in select/update or delete statements (except the closing " for the SQL string) !!!!
One Star

Re: Oracle Mixed case column names

I agree with JL. When a script is run in an Oracle database and object names, attribute names are enclosed in double quotes Oracle will enforce the case sensitivity. Oracle will even create a "same name" without name conflict because Oracle knows that those two are different objects. e.g.
if you created a table as
1) create table "My_Dba_Objects" as select * from DBA_OBJECTS where rownum < 10;
and then create another table as
2) create table My_Dba_Objects as select * from DBA_OBJECTS where rownum < 100;
Oracle will create two tables My_Dba_Objects and MY_DBA_OJBECTS.
then if you do a select as follows:
1) Select count(*) from my_dba_objects; (it will show 99 rows)
vs
2) select count(*) from "My_Dba_Objects"; (this will show 9 rows)
In the absence of MY_DBA_OBJECTS select statement 1 will fail with ORA-00942: table or view does not exist.
drop table my_dba_OBJects; (this will drop MY_DBA_OBJECTS)
then issue select statement 1 and it will fail as stated above.
Be prepared to take on a lot of unnecessary development overhead with double quotes.
Oracle SQL and PL/SQL code will have to adhere to the exact names which are strictly enforced -- this will prolong development
Any object model that is built on this model will most likely inherit the same standards and force the application development to use the same double quotes to reference objects and their attributes.
In essence custom development will prolong unnecessarily.
Thanks
Suresh
One Star

Re: Oracle Mixed case column names

I agree that using mixed case in Oracle is generally problematic and best avoided. However, these are arguments for Oracle to not provide that feature. Since Oracle does support creation of mixed case column names and as a Talend developer I am not always able to modify column names, I am required to work with mixed case column names using Talend.
The question is: how complete is Talend's support for Oracle? It appears it does not support mixed case column names.
I created a JIRA bug referencing this post:
https://jira.talendforge.org/browse/TDI-26906
Employee

Re: Oracle Mixed case column names

Hello Mike,
Please refer to the attached picture, it shows how to configure the metadata for the situation that column name are lower-cases.
right click on TEST item and choose the item:Edit Schema in the pop-up menu, then edit the DB columns to lower-cases.
One Star

Re: Oracle Mixed case column names

Yes, I supplied this solution in my first post.
Having to manually enter metadata for every column works. To me, this is more work than writing the insert statements by hand. I am hoping for a more automated approach