Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

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


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
  
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB