Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Sqoop >> mail # user >> Any one know how to handle lower case table name and column name in Oracle ?


+
John Zhao 2014-02-18, 02:19
+
Martin, Nick 2014-02-18, 02:38
+
John Zhao 2014-02-18, 03:21
Copy link to this message
-
Re: Any one know how to handle lower case table name and column name in Oracle ?
John- use upper case for your username and table name, despite it being lowercase in Oracle.

-BC
From: John Zhao <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Reply-To: "[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Date: Monday, February 17, 2014 10:20 PM
To: "[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Subject: Re: Any one know how to handle lower case table name and column name in Oracle ?

Thanks for your suggestion.If I use the upper case name , It will say table does not exist.

14/02/17 19:17:27 INFO manager.OracleManager: Time zone has been set to GMT
14/02/17 19:17:27 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM MINER_DEMO.GOLF_CAP_COL t WHERE 1=0
14/02/17 19:17:27 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)

And the lower case table surely exists:
[cid:DADDB7BE-DDE4-485A-9001-28EF15CA6DAE]
Any idea ?
Thanks.
John.
On Feb 17, 2014, at 6:37 PM, Martin, Nick <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:

Try the table name as caps in your Sqoop syntax (even though it is lwr case on the Oracle side).

Sent from my iPhone

On Feb 17, 2014, at 9:19 PM, "John Zhao" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:

I got an oracle table with lower case character in table name and column name:

CREATE TABLE "MINER_DEMO"."golf_cap_col"(outlook VARCHAR2(32), temperature integer,humidity integer,wind VARCHAR2(32), “play" VARCHAR2(32)) ;
INSERT INTO "MINER_DEMO"."golf_cap_col"( outlook, temperature, humidity, wind, “play") VALUES ('sunny', 80, 81, 'false', 'no’)

So when I run this
./sqoop import --connect jdbc:oracle:thin:@192.168.1.28:1521/xe --username miner_demo --password miner_demo  --table MINER_DEMO.golf_cap_col --username miner_demo --password miner_demo --connection-manager org.apache.sqoop.manager.OracleManager -m 1

It will throw the following error :
14/02/17 17:04:21 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM MINER_DEMO.golf_cap_col t WHERE 1=0
14/02/17 17:04:21 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)

If I run the command with --table "MINER_DEMO".”golf_cap_col”, it will throw the same error .
If I run it with --table \"\"MINER_DEMO\".\”golf_cap_col\"\” , it shows the following error :

 ./sqoop import --connect jdbc:oracle:thin:@192.168.1.28:1521/xe --username miner_demo --password miner_demo  --table  \"\"MINER_DEMO\".\"golf_cap_col\"\" --username miner_demo --password miner_demo --connection-manager org.apache.sqoop.manager.OracleManager -m 1
....
14/02/17 17:00:01 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "MINER_DEMO"."golf_cap_col" t WHERE 1=0
14/02/17 17:00:01 ERROR tool.ImportTool: Imported Failed: Attempted to generate class with no columns!

Also the same issue happens in export command.
So, as I tried every way I can think, it still does not work. Does anyone know how to handle it?

Thanks.

John Zhao