Get data from Oracle to Navision 4.0

TRSOEgroup

anyone tried this? a customer wants me to do this for him but i don't know where to start.....

any help would be appreciated

dmitrip

I never work with Oracle relational database (only with Oracle Financial Analyzer). But I suppose that you can use SQL Server DTS to download data from Oracle DB to SQL Server DB.
Latter you can use:
1. Excel - to verify data plus Navision dataport to upload data into Navision.
2. Upload data directly on SQL Server.

All master data can be uploaded to corresponding tables directly (i.e. customer, vendor, etc.). Open balances are uploaded to corresponding journals and are posted from there.

As far as I know Microsoft suggest a special transition program (what does it include???) from Oracle to Navision. Please check their site for more info.

Dmitri.

wonmo

Just use ADO. We've done this to connect to an Oracle database and as long as you design your tables and code well, it's very, very fast.

Angelo

Hello,Wonmo

May I know the code using ADO to connect to Oracle database system?

regards,
Angelo

rossi

Hi!

Here is an example how to use ADO to retrieve data from external datasources, in this case a MS SQL server. This should work on most databases, just change the connection string accordingly..

Examples of connection strings at http://www.connectionstrings.com/

To test this codeunit, just run the codeunit from object explorer.

Enjoy!


OBJECT Codeunit 80006 ADO Usage
{
OBJECT-PROPERTIES
{
Dato=10.08.05;
Tidspunkt=11:05:13;
Endret=Ja;
Versjonsoversikt=;
}
PROPERTIES
{
OnRun=BEGIN
IF ISCLEAR(aADO_Con) THEN
CREATE(aADO_Con);
IF ISCLEAR(aADO_Rs) THEN
CREATE(aADO_Rs);

aADO_Con.Open('Provider=SQLOLEDB;Data source=LocalHost;database=Northwind;User Id=sa;Password=;');
aADO_Rs.Open('select * from Customers',aADO_Con);

aADO_Rs.MoveFirst;
WHILE NOT aADO_Rs.EOF DO
BEGIN

// Do something with the record
MESSAGE('%1',aADO_Rs.Fields.Item(0).Value);

aADO_Rs.MoveNext();
END;
END;

}
CODE
{
VAR
aADO_Con@1000000000 : Automation "{EF53050B-882E-4776-B643-EDA472E8E3F2} 2.7:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.7 Library'.Connection";
aADO_Rs@1000000002 : Automation "{EF53050B-882E-4776-B643-EDA472E8E3F2} 2.7:{00000535-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.7 Library'.Recordset";
}
}

wonmo

The above code for SQL Server is pretty much what you want to do - establish the connection and then populate a Recordset object with the data that you want. However with Oracle you will need to acquire the ADO data provider. DO NOT USE ODBC as this is very, very slow. Also. be aware that the SQL syntax will be slightly different than with SQL Server. As an example of selecting records based on date range in Oracle:

...WHERE CREATED_DATE >= TO_DATE(''06/20/05'', ''MM/DD/YY'')...