microsoft access

[NITIN27]

Hi.
can a script be runned in microsoft access just as we do in oracle sql
eg. sqlplus
connect system/manager

@c:\all\alter.sql

This alter.sql has three tables creation script

I want to do same in Microsoft Acess

Thanks in advance.

 

[sem]

My bet is that the answer is NO, but why don't you ask it in Access forum???

Regards, Dima

http://seminihin.narod.ru

 

[Cagliostro]

use OO4O (Ole Objects for Oracle) from a VBA function.

Ion Filipski

 

[sem]

Even OO4O don't understand sql*plus specific syntax, commands like CONN[ECT], @ etc

If you have versions 8 or prior you may use OCA (Open Client Adapter) and connect from sql*plus to ODBC data source. I'm not 100% sure but I think that this feature is no more available for 9i.

Regards, Dima

http://seminihin.narod.ru

 

[Cagliostro]

oo4o sure supports PL/SQL syntax in the same way as in sql*plus. Just a copy/paste from samples:

 Const ORATYPE_NUMBER = 2
 Const ORAPARM_INPUT = 1
 
 Dim OraSession As Object
 Dim OraDatabase As Object
 Dim OraDynaset As Object

    dept = ....

 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 OraDatabase.Parameters.Add "DEPTNO", dept, ORAPARM_INPUT
 OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER
 
 Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&) 
 .....

In the order to execute connect/disconnect..., common, you don't need them because of there is provided method orasession:pendatabase and oradatabase::close. Also you have there full control on what is inside PL/SQL code.

By the way, if you are afraid of oo4o, you can execute any pl/sql files from a .bat file. But any things you can do in sql*plus you can do in oo4o.

Ion Filipski

 

[sem]

Ion, in my message I just tried to emphasize that sql*plus syntax differs a lot from sql and even pl/sql one. Sql*plus supports a lot of specific commands, that are neither sql nor pl/sql. The most common examples are DESC, COPY, COLUMN, SHOW, SET etc. that either call behind the scenes a set of statements or make some client-side tasks. Thus sql*plus script (of course if it's not primitive sql statement stored in file) normally can not be launched from any other database tool, unless it's specially designed to be sql*plus-compatible.

Regards, Dima

http://seminihin.narod.ru

 

[gunjan14]

Hi,

Although this is wrong forum. But you can solve your problem of running the script by writing a piece of code in VBA and then running it. Because we were doing the same thing. We created the script for creating table and then wrote some code in VB 6.0 to run that script and create tables in Access.

Gunjan

 

[Cagliostro]

cold' you be a little more specific?
I mean, would you like to transfer data from access to oracle or vice versa?

Ion Filipski