SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-25-2008, 07:06 AM
| ||||
| ||||
 ODBC " character issue I export an access table using Oracle ODBC driver to Oracle schema, it add character " to table name and all its column names like this "ff_to_oracle" ( ID VARCHAR2(20), "empid" VARCHAR2(20), "empname" VARCHAR2(50), "empdept" VARCHAR2(20), "create_date" DATE ) and when I want to query the imported table in Oracle I have to say: select * from "ff_to_otracle" Is there anyway to get rid off " charater during the ODBC process? Thanks for help      |
|
02-25-2008, 07:06 AM
| |||
| |||
| Re: ODBC " character issue On 15 Sep 2006 10:34:20 -0700, soalvajavab1@yahoo.com wrote: >I export an access table using Oracle ODBC driver to Oracle schema, >it add character " to table name and all its column names like this > >"ff_to_oracle" >( > ID VARCHAR2(20), > "empid" VARCHAR2(20), > "empname" VARCHAR2(50), > "empdept" VARCHAR2(20), > "create_date" DATE >) > >and when I want to query the imported table in Oracle I have to say: > >select * from "ff_to_otracle" > > >Is there anyway to get rid off " charater during the ODBC process? >Thanks for help There is: Make sure ALL of your identifiers (table names, column names etc), are in UPPERCASE prior to conversion. So FF_TO_ORACLE (ID EMPID EMPNAME EMPDEPT CREATE_DATE etc. This is a known feature and has been documented here often. You could have searched http://groups.google.com prior to posting. -- Sybrand Bakker, Senior Oracle DBA |
|
02-25-2008, 07:06 AM
| |||
| |||
| Re: ODBC " character issue Thanks Sybrand, you are right, I try that and if the names and columns are uppercase then ODBC behave fine now my next question is, I already have bunch of tables in MS Access which are not Uppercase and I need to tranfer them to Oracle without the extra " charachter. Any thought to make all those tables and columns uppercase at once or any other solution? Thanks again Sybrand Bakker wrote: > On 15 Sep 2006 10:34:20 -0700, soalvajavab1@yahoo.com wrote: > > >I export an access table using Oracle ODBC driver to Oracle schema, > >it add character " to table name and all its column names like this > > > >"ff_to_oracle" > >( > > ID VARCHAR2(20), > > "empid" VARCHAR2(20), > > "empname" VARCHAR2(50), > > "empdept" VARCHAR2(20), > > "create_date" DATE > >) > > > >and when I want to query the imported table in Oracle I have to say: > > > >select * from "ff_to_otracle" > > > > > >Is there anyway to get rid off " charater during the ODBC process? > >Thanks for help > > There is: > Make sure ALL of your identifiers (table names, column names etc), are > in UPPERCASE prior to conversion. > So > FF_TO_ORACLE > (ID > EMPID > EMPNAME > EMPDEPT > CREATE_DATE > > etc. > > This is a known feature and has been documented here often. You could > have searched http://groups.google.com prior to posting. > > -- > Sybrand Bakker, Senior Oracle DBA |
|
02-25-2008, 07:06 AM
| |||
| |||
| Re: ODBC " character issue On 15 Sep 2006 11:56:27 -0700, soalvajavab1@yahoo.com wrote: >Thanks Sybrand, you are right, I try that and if the names and columns >are uppercase then ODBC behave fine >now my next question is, I already have bunch of tables in MS Access >which are not Uppercase and I need to tranfer them to Oracle without >the extra " charachter. Any thought to make all those tables and >columns uppercase at once or any other solution? Thanks again Could probably be arranged by coding it in Access Basic (or whatever it is called now) using DAO (or whatever it is called now), but my Access Basic is really rusty. On the Oracle side you'll probably can use dbms_redefinition, but I have never had to use that myself. Please do not top post. -- Sybrand Bakker, Senior Oracle DBA |
|
02-25-2008, 07:13 AM
| ||||
| ||||
| Re: ODBC " character issue Sybrand Bakker wrote: > On 15 Sep 2006 11:56:27 -0700, soalvajavab1@yahoo.com wrote: > > >Thanks Sybrand, you are right, I try that and if the names and columns > >are uppercase then ODBC behave fine > >now my next question is, I already have bunch of tables in MS Access > >which are not Uppercase and I need to tranfer them to Oracle without > >the extra " charachter. Any thought to make all those tables and > >columns uppercase at once or any other solution? Thanks again > > Could probably be arranged by coding it in Access Basic (or whatever > it is called now) using DAO (or whatever it is called now), but my > Access Basic is really rusty. > On the Oracle side you'll probably can use dbms_redefinition, but I > have never had to use that myself. > > Please do not top post. > > -- > Sybrand Bakker, Senior Oracle DBA Here is a snippet. You will have to figure out how to exclude system tables: Public Sub setColumnNamesUpper() Dim dbs As Database Dim tdf As TableDef Dim fld As Field Set dbs = CurrentDb For Each tdf In dbs.TableDefs For Each fld In tdf.Fields fld.name = UCase(fld.name) Next fld Next tdf End Sub |
Linear Mode
