DOS command line to load SQL

[dsv]

We just switched from Oracle to Access. Does anyone know how to load SQL
files directly into Access through a DOS command line statement?

We use SQLPlus for our Oracle scripts.

Thanks

 

[TC]

Are you asking, how to import an SQL*Plus file into Access?

If so, the general answer is: you can't. SQL*Plus has many commands
that are specific to Oracle, and don't exist in Access.

HTH,
TC (MVP Access)
http://tc2.atspacecom

 

[Dirk Goldgar]

We just switched from Oracle to Access. Does anyone know how to load
SQL files directly into Access through a DOS command line statement?

We use SQLPlus for our Oracle scripts.

Are you asking how to execute SQL scripts from a command line? Access
has no provision for that. In fact, Access has no built-in provision
for SQL scripts containing multiple statements, even if you wanted to
load and run them from the user interface.

However, it would be fairly easy to write a program to do it -- read a
script file, parse it into SQL statements, and use DAO to execute each
statement against a specified database. The parsing would be the tricky
bit, but if you're willing to put certain constraints on the statements
it shouldn't be too hard.

 

[Mike Labosh]

We just switched from Oracle to Access. Does anyone know how to load SQL

files directly into Access through a DOS command line statement?

We use SQLPlus for our Oracle scripts.

Oracle migration to Access?!?

Somewhere, my brain is exploding.

The more important thing to ask yourself or your bosses, is "WHY?!?"

To answer your question, though, the best thing that Access has to import
data is the IMEX wizard. There is no way to do it on the command line, but
you could make empty tables to contain the data, and then run the wizard to
import the stuff. Alternately, you can also do it in VBA code. Also,
Access has data types and auto-incrementing features for surrogate keys that
are more or less familiar to Oracle people, but there will be a learning
curve: Just the different way that Access does things that you already
know. BEWARE that Access has a 2GB limit on storage size. If you have some
giant database that you are scaling down (again, I must ask WHY?!?) be
certain that you don't blow that limit. And if that's a concern, then think
about multiple Access databases to span tables and use UNION alot in your
SQL.

But before you proceed, ask yourself (and your bosses) very seriously: WHY
would you go from Oracle to Access?
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

 

[George Nicholson]

WHY would you go from Oracle to Access?

Because Access has better newsgroups?

 

['69 Camaro]

Hi.

We just switched from Oracle to Access. Does anyone know how to load SQL
files directly into Access through a DOS command line statement?

Whoa! Hold your horses. Oracle scripts are incompatible with Jet, Access's
database engine, so you can do this the hard way or the easy way. An
experienced Oracle DBA can set up this data load in minutes for a one-time
migration or for recurring uploads of data into the Jet tables. Or it could
take an inexperienced person a month or more to (maybe) get it right.

Please tell us your circumstances in what has brought this Oracle-to-Access
switch on, because database experts will tell you that this rarely a good
idea. If the answer is, "Oracle is too expensive to license," then Oracle
offers a free solution, Oracle Express (if your data is 4 GB or less), where
you can store all of your existing schemas, tablespaces, stored procedures,
data, et cetera, without having to alter what you currently have, so you
won't lose your previous investments in Oracle. You can download Oracle
Express from the following Web page:

http://www.oracle.com/technology/products/database/xe/index.html

Or, if you just need a user-friendly application to access the data in the
tables and you don't have the time or expertise to use Oracle developer
tools, then you could keep the schemas, data, et cetera, where they are in
the Oracle tables (back end) and build the interface (front end) with Access
or some other development environment, such as VB, Java, VB.Net, C#, et
cetera.

And please tell us your situation, such as whether you have an Oracle DBA
available and, if not, why not -- perhaps he's coming back soon? -- and your
own skill set and skill level, whether you still have the Oracle instance
running, where the data is located (Oracle tables, export files, SQL
scripts), whether this is a one-time migration or a recurring upload, whether
you have TOAD installed, and what types of Oracle script files you have,
because Oracle script files are designed to work with various Oracle tools.
The scripts can be SQL*Plus scripts to spool the results of dynamic SQL into
a file containing the individual SQL INSERT statement for each record, or the
file containing the actual SQL statements, or par files, or dump files, or
PL/SQL stored procedures, so you definitely need to know which is which, and
what you actually have.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Mike Labosh]

WHY would you go from Oracle to Access?

Because Access has better newsgroups?

Are they really better?

Hi, I'm an idiot, and I'm having trouble with the spell-checker in MS Word
integrating with my fonts in Excel. PLEEEEEASSSE HELP! Oh, and while
you're at it, please tell me what this "windows thing" is and help me use
a mouse.

--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei