Database reserved keywords -- tfileproperties

One Star

Database reserved keywords -- tfileproperties

Hi,
I am using the tFileList, tFileProperties and tOracleOutput to store the file statistics of all the files I am processing. What I have noticed is that when the table is created for the first time, one of the columns called "size" is actually an Oracle Keyword. The Built-in default schema provided by Talend has a column named "size", which is an Oracle keyword and hence has to be in double quotations (" ") while creating the table. So basically you will have to create a table as follows in Oracle.
CREATE TABLE TALEND_FILE_STATS
(
ABS_PATH VARCHAR2(4000 BYTE),
DIRNAME VARCHAR2(4000 BYTE),
BASENAME VARCHAR2(4000 BYTE),
MODE_STRING VARCHAR2(100 BYTE),
"size" NUMBER(20),
MTIME NUMBER(20),
MTIME_STRING VARCHAR2(30 BYTE)
)
ORACLE RESERVED KEYWORDS URL
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14354/appb.htm

While writing queries, for example you will have to say something like this.
Select ABS_PATH, "size" from talend_file_stats ------this query will work
Select ABS_PATH, size from talend_file_stats ------this query will not work because now the column "size" is a keyword and hence you will have to use double quotations. When ever you use double quotations to name a table or a column while creating a table in Oracle, you will have to use double quotation in your queries also because Oracle will not recognize either the table or the column if not in double quotation.
Oracle will throw an ORA error 00900
----------------------------------------------------------------------
invalid SQL statement
Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.
Action: Correct the syntax or install the Procedural Option.
------------------------------------------------------------------------
There is no problem doing this, but my suggestion would be to make sure that your developers are given a list of all the keywords in all database which should not be used for naming the columns for all you default Built-In schemas. For example, if the "size" column is named to something like FILESIZE which is probably not a keyword in any of the databases that would reduce a lot of troubleshooting problems for all your Clients using Talend.
Thank you,
Santhosh.
Community Manager

Re: Database reserved keywords -- tfileproperties

Hi
that would reduce a lot of troubleshooting problems for all your Clients using Talend.

I agree with you, I will talk it to our product manager. Thanks you!
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business