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 5 6190
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
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?
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
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 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: steve |
last post by:
Hi, I have researched but have not found a good solution to this
problem.
I am importing large amounts of data (over 50 Meg) into a new mysql db
that I set up. I use
>mysql dbname <...
|
by: Matthias HALDIMANN |
last post by:
When importing data into MS SQL Server 2000 from a MyODBC (v3.51) data
source using Data Transformation Services, special characters like öäüéàè
are not imported correctly. However, when the MyODBC...
|
by: Fabian Kr?ger |
last post by:
Hello,
I got a weird problem and need your help and ideas...
I´ve written an php application which imports data in XML format and
writes this data to a MySQL database to have a faster access....
|
by: Nathan Bloom |
last post by:
Hi,
I have a procedure that transfer data from an Excel spreadsheet to an Access
2000 table. There is a start date and an end date in the range specified.
One of the date fields transfers...
|
by: Darren |
last post by:
I have been attempting to create a reservation planning form in excel
that imports Data from an Access database and inserts that information
automaticly into the correct spreed sheet and the...
|
by: pemigh |
last post by:
A while back I imported tables to a new database via Files-->Get
External Data --> Import...
All was well for several months, and then the database started behaving
badly in a couple of ways,...
|
by: seeCoolGuy |
last post by:
I've been using Access to import some data straight into a sql server
database by simply importing the xml file. However lately some of the
newer xml files will contain vaild data such as
...
|
by: JMO |
last post by:
I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the...
|
by: Andrew Lowe |
last post by:
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...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |