Home » RDBMS Server » Server Utilities » Exporting data from oracle
Exporting data from oracle [message #69328] Tue, 11 December 2001 00:18 Go to next message
Joserra
Messages: 3
Registered: December 2001
Junior Member
Hi there:
I've to export data from oracle to a flat file which I could send through mail.This exportation must be excecuted after a concrete period of time from a Visual Basic program. Do you know how to do it?
Thanks a lot.
Joserra

----------------------------------------------------------------------
Re: Exporting data from oracle [message #69332 is a reply to message #69328] Tue, 11 December 2001 04:53 Go to previous messageGo to next message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi
You have to connect to oracle database using ADO.
Then select the data by simple select queries and open a flatfile and write to it...

this is a simple vbscript(windows script host)
which selects data from emp table and spool it to a file..
-----------------------------------------------
Const ForReading = 1, ForWriting = 2, ForAppending = 8
dim objConn, strEmpSQL
Dim fso, f

set objConn = Wscript.CreateObject("adodb.connection")

'create a odbc dsn for your database and give the dsn name, database userid, password below
if isobject(objConn) then
objConn.Open "DSN=dsnname","userid","password"

'write your sql statement and keep it in a string
strEmpSQL = "Select * FROM emp"


'open your qurey and run against your db
set rsEmp = Wscript.CreateObject("adodb.Recordset")
rsEmp.open strEmpSQL,objConn

'create a file object and open it for writing
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("c:emp.txt", ForWriting, True)

'write the records one after another into that file, seperated by a ,
'WScript.Echo rsEmp("ename")
while not rsEmp.EOF
f.Write rsEmp("EMPNO") & "," & rsEmp("ENAME") & "," & rsEmp("JOB") & "," & rsEmp("MGR") & "," & _
rsEmp("HIREDATE") & "," & rsEmp("SAL") & vbcrlf

rsEmp.MoveNext
wend
f.Close
end if

WScript Quit()
----------------------------------------------

Bala.

----------------------------------------------------------------------
Few more questions [message #69334 is a reply to message #69332] Tue, 11 December 2001 05:35 Go to previous messageGo to next message
Joserra
Messages: 3
Registered: December 2001
Junior Member
Hi there again:
I imagine Wscript is an ADO object.Where should it be defined? If I take the code Bala (thanks a lot for that ...)provided, changing of course the refererece to a correct Table in a database in a click event of a button in a form of Visual Basic 6 and we run, it gaves a compilation problem cause Wscript is not defined. Should I import something before using Vscript?
Thanks a lot for everything ...
joserra

----------------------------------------------------------------------
Re: Few more questions [message #69337 is a reply to message #69334] Tue, 11 December 2001 06:09 Go to previous message
Bala
Messages: 205
Registered: November 1999
Senior Member
Hi

I gave the VB script as an example
i am not sure you can use WSH objects for VB forms
You might have to write VB code in similler fashion...

Bala.

----------------------------------------------------------------------
Previous Topic: Exporting from oracle 8i
Next Topic: migration exp/imp
Goto Forum:
  


Current Time: Thu Mar 28 15:37:44 CDT 2024