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

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

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

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

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