Data not importing correctly

[Andrew Lowe]

Hi all,
First off, I'm new to the Oracle world so please be gentle on me. Now
down to the problem at hand. I support a CAD application that has an Oracle
backend. I've been on site at my clients offices and got a dump of data,
via TOAD, to bring back to my office. This has given me a file containing a
large number of insert statements of the format:

Insert into tgds.trip_bodies
(ARRIVAL_TIME, ADJUSTED_ARRIVAL_TIME, ADJUSTED_DEPARTURE_TIME,
ADJUSTED_RECOVERY_TIME, MASTER_NODE, CURRENT_NODE, DEPARTURE_TIME, TSN_REF,
NODE_MODE, ARRIVAL_SYMBOL, DEPARTURE_SYMBOL, RECOVERY_TIME, NODE_NAME,
TME_ID, THR_ID)
Values
(10419, NULL, NULL, NULL, 107868,
108381, 10419, 102745, '', ' ',
' ', 0, 'CMPG', 1218, 2550470,
);

My problem is the 9th entry, NODE_MODE which comes across as a square
box between quotes. In the db schema, this is defined as "char(1)" and for
some reason, the data in the clients NODE_MODE column is made up of the
NON-printable values, the ones below 32, from the ASCII table, hence the
box within quotes. If I look at one of these insert statements in a
programmers editor, I get values such as '^C', '^K', all values that give
the square box. I have now loaded this data, using the insert statements,
into Oracle via TOAD. My problem is that the data comes across as a dot ie
a period. If I re-export this data, instead of the '^C' & '^K', I now get
'.'. Is there a way that I can import this data into my instance of Oracle
and get these values to come across correctly?

Any thoughts on this are greatly appreciated.

Andrew Lowe

[Mark Clark]

On 03 Oct 2004 16:46:14 GMT, Andrew Lowe <ag*@wht.KILLSPAM.com.au>
wrote:

Andrew

I'm not familar with Toad -- but you would benefit from a quick look
at Oracle's export tool for future reference.

Hi all,
First off, I'm new to the Oracle world so please be gentle on me. Now
down to the problem at hand. I support a CAD application that has an Oracle
backend. I've been on site at my clients offices and got a dump of data,
via TOAD, to bring back to my office. This has given me a file containing a
large number of insert statements of the format:

Insert into tgds.trip_bodies
(ARRIVAL_TIME, ADJUSTED_ARRIVAL_TIME, ADJUSTED_DEPARTURE_TIME,
ADJUSTED_RECOVERY_TIME, MASTER_NODE, CURRENT_NODE, DEPARTURE_TIME, TSN_REF,
NODE_MODE, ARRIVAL_SYMBOL, DEPARTURE_SYMBOL, RECOVERY_TIME, NODE_NAME,
TME_ID, THR_ID)
Values
(10419, NULL, NULL, NULL, 107868,
108381, 10419, 102745, ' ', ' ', ' ', 0, 'CMPG', 1218, 2550470,
);

My problem is the 9th entry, NODE_MODE which comes across as a square
box between quotes. In the db schema, this is defined as "char(1)" and for
some reason, the data in the clients NODE_MODE column is made up of the
NON-printable values, the ones below 32, from the ASCII table, hence the
box within quotes. If I look at one of these insert statements in a
programmers editor, I get values such as '^C', '^K', all values that give
the square box. I have now loaded this data, using the insert statements,
into Oracle via TOAD. My problem is that the data comes across as a dot ie
a period. If I re-export this data, instead of the '^C' & '^K', I now get
'.'. Is there a way that I can import this data into my instance of Oracle
and get these values to come across correctly?

Any thoughts on this are greatly appreciated.

Andrew Lowe

Mark
http://www.linxcel.co.uk

[Hans Forbrich]

When you say 'Oracle Back End' .... does the application use Oracle's
Spatial option or Oracle Locator functionality? (Locator is a subset of
Spatial used to store adn retrieve GIS coordinate maps.) Or does it use a
customized method of storing the data. (IOW, what is the data type of the
column that's giving the problem - try givng us a 'DESCRIBE' on the table.)

Versions of Oracle, and version of operating system, would be useful.

Finally, you extracted the data - seemingly using Toad. You apparently did
not use Oracle's export/import tools. Any specific reason?

[Andrew Lowe]

Hans Forbrich <ne*******@telus.net> wrote in
news:IcW7d.9495$N%.815@edtnps84:

When you say 'Oracle Back End' .... does the application use Oracle's
Spatial option or Oracle Locator functionality? (Locator is a subset
of Spatial used to store adn retrieve GIS coordinate maps.) Or does
it use a customized method of storing the data. (IOW, what is the
data type of the column that's giving the problem - try givng us a
'DESCRIBE' on the table.)
It's not a GIS app, it actually deals with train timetabling. It just
uses "plain" Oracle. The describe is as follows, the column in question,
NODE_MODE:

Name Null? Type
----------------------------------------- -------- -------------
ARRIVAL_TIME NOT NULL NUMBER(6)
ADJUSTED_ARRIVAL_TIME NUMBER(5)
ADJUSTED_DEPARTURE_TIME NUMBER(5)
ADJUSTED_RECOVERY_TIME NUMBER(5)
MASTER_NODE NOT NULL NUMBER
CURRENT_NODE NUMBER
DEPARTURE_TIME NOT NULL NUMBER(6)
TSN_REF NUMBER
NODE_MODE NOT NULL CHAR(1)
ARRIVAL_SYMBOL NOT NULL CHAR(1)
DEPARTURE_SYMBOL NOT NULL CHAR(1)
RECOVERY_TIME NOT NULL NUMBER(5)
NODE_NAME NOT NULL CHAR(4)
TME_ID NOT NULL NUMBER(8)
THR_ID NOT NULL NUMBER(8)

Versions of Oracle, and version of operating system, would be useful.
Oracle is 9i, Release 9.2.0.1.0 running on Win2000

Finally, you extracted the data - seemingly using Toad. You
apparently did not use Oracle's export/import tools. Any specific
reason?

As I said I'm new to the Oracle world. I hav TOAD on both the clients
machine and my own and it makes life a lot easier for me, eg SQL Plus Vs
TOAD. When I was at my clients office, my concern was that their setup of
Oracle 9i running on Solaris, ie 64 bit on a SPARC CPU, would cause binary
problems when trying to load data into my setup, 9i on Win2000, ie 32bit on
Intel, hence the "text way".

Any help that you can give me would be greatly appreciated.

Andrew Lowe

[Hans Forbrich]

Andrew Lowe wrote:

As I said I'm new to the Oracle world. I hav TOAD on both the
No worries. There are reasons for using exp and there may be some for
avoiding it. Wanted to verify first.
clients
machine and my own and it makes life a lot easier for me, eg SQL Plus Vs
TOAD. When I was at my clients office, my concern was that their setup of
Oracle 9i running on Solaris, ie 64 bit on a SPARC CPU, would cause binary
problems when trying to load data into my setup, 9i on Win2000, ie 32bit
on Intel, hence the "text way".

Assuming you want ALL the table data, Oracle provides a pair of utilities to
export data from a database and import that same data. There are
preconditions (version, char set), but in general these utilities will help
you ensure you get the data across properly regardless of OS.

You want to run exp for a table using the table owner on the solaris box.
Then imp on windows. (Command line utilities)

Both commands respond to parameter 'help=Y' to provide syntax - should be
enough to help. Both are very flexible and are fully described in the
Utilities manual at http://tahiti.oracle.com

HTH
/Hans

[Andrew Lowe]

Hans Forbrich <ne*******@telus.net> wrote in
news:7zb8d.20310$223.5959@edtnps89:

Andrew Lowe wrote:

As I said I'm new to the Oracle world. I hav TOAD on both
the

No worries. There are reasons for using exp and there may be some for
avoiding it. Wanted to verify first.

clients
machine and my own and it makes life a lot easier for me, eg SQL Plus
Vs TOAD. When I was at my clients office, my concern was that their
setup of Oracle 9i running on Solaris, ie 64 bit on a SPARC CPU,
would cause binary problems when trying to load data into my setup,
9i on Win2000, ie 32bit on Intel, hence the "text way".

Assuming you want ALL the table data, Oracle provides a pair of
utilities to export data from a database and import that same data.
There are preconditions (version, char set), but in general these
utilities will help you ensure you get the data across properly
regardless of OS.

You want to run exp for a table using the table owner on the solaris
box. Then imp on windows. (Command line utilities)

Both commands respond to parameter 'help=Y' to provide syntax - should
be enough to help. Both are very flexible and are fully described in
the Utilities manual at http://tahiti.oracle.com

HTH
/Hans

Hans,
Thanks for the reply but the blame as to why this was not working
lays directly at the feet of TOAD. As I said I had to import a series of
rows that contained as one of its columns a column that consisted of the
lower, non displayable, ASCII characters, the characters such as '^C',
the value 3 or ETX and '^K', the value 11 or VT. When I viewed the
columns in question in a text editor, it appeared as a square box inside
quotes, something I've seen before for non-displayable characters, so I
loaded this file as a script into TOAD and executed it. TOAD displayed
the column in question as '.' which I assumed to be its way of displaying
the lower value ASCII characters. IT'S NOT!!!! After loading the data
several times I eventually worked out that TOAD can't handle the lower
value ASCII characters when it reads in scripts to its SQL editor. I have
hunted around and found DBOne from Fox Software which HAS been able to
handle lower ASCII values and has successfully loaded my data.

So after all that, thanks for your time and ideas but it was my $%%
&&^$%$^$$^$%&^%**$%##$% copy of TOAD.

Regards,
Andrew Lowe