Hi all,



I realise that Oracle Personal Express v5 is hopelessly old, but we are
trying to replace the app that it runs off. Unfortunately we need to get
the data out of the existing database and I can find zero documentation
on how to do this.



When I had a look at the app I noticed there was a command line ...
obviously though since at the mo this is a production system with no
backup, I really don't want to go in and just mess with this.



Does anyone have any idea

1) Where I can find any documentation to help me?

2) How the hell I use the CLI (or any other tool) to get at the data and
extract it into XL or similar?



Many thanks for any help proferred



Meri


--
Posted via http://dbforums.com

Hi PJ

There are many ways of doing this - You don't say what format you want
to export the data, or what the target system is, so you'll need to
decide which is most appropriate. Pretty much all of what follows can
be done via the command line, or if you prefer you can do it in a little
program which saves all the commands you use in one place.

You need to check out the HELP system that comes with Personal Express,
for the following commands in particular (one of these options is likely
to be what you want):

1) EXPORT - to EIF file. This is useful if you want to move data from
one Express database to another (or to an Oracle 9iOLAP Analytic
Workspace - Oracle9i OLAP includes all the Express functionality and can
be regarded in most respects as the next major release of Express).
Also check out the EIF PIPELINE option - this is where you can have two
Express systems communicating to each other via an XCA (Express
Communication Architecture) connection. eg Personal Express v 5 talking
to Express Server v 6.x If your new system is in Express 6 or
9iOLAP, then you should seek advice on how to take advantage of the much
improved sparsity management features in these releases, and also on how
to efficiently define the new fact data variables in Express 6/9iOLAP.
Some stuff in the Express language changed between Express 5 and version
6, and now in 9i - so expect to have to modify some of your application
when it is in the new version.

2) SQL command - Personal Express v5 can push data into relational
tables via the Express Language SQL command. You can insert into
tables, update, commit etc. This is very useful if you want to move
data to and from Express and a relational database like Oracle or one of
the others.

3) REPORT command - a simple but extremely powerful and flexible command
that lets you output data into text format - eg flat files - in most
common styles of output. By default it obviously prints row and column
labels etc, but you can remove them using the NOHEAD keyword.

4) ROW command - if you need to be even more sophisticated than REPORT
allows, use ROW (generally you use this in a program)...

With all of the above the HELP system provides examples. You will need
to check out the LIMIT command also, so that you can set the scope of
the data that you want to output.

Hope it helps, Kevin @ Oracle

PS - Ofcourse, version 5 of Express is indeed old, but not hopeless at
all - it still has more talent than alot of so called modern competitors
;-) But then, I'm biased. Express 6.x and 9iOLAP ofcourse build on
all the good things there and add alot more new good things on top. If
you haven't already done so, do check them out.


PJ1382 wrote:

Hi Meri,
As you thought, Personal Express is quite an old product now, and it's
been upgraded and enhanced over the years and its latst incarnation
was Oracle Express Server 6.3.4. OES had many similarities to Personal
Express (programming language, datatypes and so on) and now Oracle
have included the technology behind Express in the Oracle 9i Database
server.

Getting data in and out of Express is a fairly common task, and as you
suspected, it involves the command line interface that you've
discovered. Basically, you need to look into the Express Programming
Language, a procedural language that comes with all versions of
Express that has commands for reading and writing to files, accessing
databases (via ODBC), and interacting with users via a graphical user
interface. To get data out of your Personal Express database, you'd
need to put a program together to read the data in the database and
copy it to a file - most probably, a comma separated (CSV) file. Of
course you can do direct exports out of Personal Express and into
another Express / Oracle 9i OLAP database, using the EIF file format,
but this is specific to Express (like an Oracle export file) and it's
usable outside of this environment.

You're going to need to get hold of some documentation to at least go
through the basics of the Express architecture (which, being
multidimensional, is quite different to the tables and columns
approach of relational databases), and the best source of information
you could get hold of would either be through the Oracle Express
Server 6.3.4 documentation available on OTN at
http://otn.oracle.com/documentation/express_server.html (the sections
on file reading and writing haven't changed much since Personal
Express), or get hold of a book, the most useful of which is Oracle
Express OLAP by Arkhipentov
(http://www.amazon.com/exec/obidos/AS...rkrittmanso-20)

One of the key differences between relational databases and Express
data objects is that, instead of tables with columns and rows, you've
got variables, that are 'dimensioned' by one or more dimensions. Each
variable (for example, AMOUNT_SOLD), stores a value for each
combination of dimension entries (STORE, TIME, PRODUCT), and to get
data out of the variables, you have to retrieve a value for each of
these dimension combinations. There's a lot more to it than this in
practice, but that's the basic concept, and it's worth bearing in mind
the fundamental differences between how Express stores data and how
relational databases store data. Having said that - they're both
databases, and with a working knowledge of the two approaches, it's
fairly easy to move data between the two systems.

The approach to reading data out of Express and into a CSV file would
be along the lines of;

1. Load up Personal Express and 'attach' the database you wished to
export from
2. Choose the objects (variables, dimensions etc) in the database you
wish to export by using commands such as LISTNAMES and DSC to query
the data dictionary
3. Use the OUTFILE command to send output to your CSV file
4. (and this is the tricky bit) Write a procedure to take each
variable, loop through all the combination of dimension values
attached to it, and use the ROW command to output the values of the
variable.

If your Express database is big, or it contains lots of sparse data
(where only a small subset of the possible dimension combinations have
a variable entry assigned), you might need to convert these blanks to
zeros, and/or you'll need to make sure you deal with this efficiently
otherwise your output file size could balloon.

Having said that, is it a given that you're moving the data out of
Personal Express and into a relational database? It might be worth
looking at more recent versions of Oracle Express Server, as these
have far better administration tools (Express Administrator, Instance
Manager and so on) and there's a whole range of ways you can present
data to users, including Express Web Agent (a thin-client HTML
interface), Web Publisher, and client-server object-orientated tools
such as Oracle Express Objects. In addition, you can directly export
your Personal Express database into an Oracle 9i OLAP analytic
workspace, which has tools to enable your Express database to be
queried via SQL (opening it up to tools such as Discoverer, Oracle
Reports, Business Objects and so on), and you can then build user
interfaces using products like JDeveloper, Visual Studio and the like.

What we'd normally say on this sort of occasion is that, particularly
as Express is fairly new to your organisation, it'd probably be worth
having a word with a specialist Express/Oracle consultancy who could
offer you guidance and resources who could help with your migration or
upgrade. If you'd like any assistance with this, or would like any
other advice, don't hesitate to drop me a line.

best regards

Mark Rittman
mrittman (AT) plusconsultancy (DOT) co.uk