Re: Forms 4.5 and Sql Server

JodyLewis wrote:
> Could you please help us out with something. We need some ideas of how to grab
> information from Sql Server tables into Oracle Forms 4.5 -- Right now we have a
> software inventory and maintenance system (SMS) that stores all of its
> information in a Sql Server database. We are currently writing a system that
> not only needs to use this information but that from our Oracle tables as well.
> How do you pull inforamtion from a different source into a form.
 

> Could you please provide us with some assistance on this matter?
 

> Thank you in advance!
 

> Jody Lewis

Hi Jody,

You can use the Oracle Open Client Adapter to connect to a SQL Server database using ODBC. It comes with Developer/2000 1.3.2 (with Forms 4.5).

In the version you most likely have on your CD, Oracle Open Client Adapter v 1.0.14.3.4, there is a PL/SQL package calls OCA.PLL which can be used to open a separate connection in PL/SQL to a ODBC database, and then grab data from your ODBC data source. However, in the most recent version of Open Client Adapter, OCA.PLL is OBSOLETE!!!

However, in the very latest version of Developer/2000 (in a patch), there is a new Oracle Open Client Adapter (version 1.0.14.5.0) which provides a new package called EXEC_SQL, which can be used to open a new connection to ANY DATASOURCE, including Oracle. You can then write PL/SQL to fetch data from your SQL Server database (or from Oracle, if you want SQL server to be your default data source) and copy back and forth in your PL/SQL Forms triggers.

Also, 1.0.14.5.0 has several preformance improvements, especially when using Microsoft SQL Server through Microsoft's ODBC driver. You can contact Oracle support to get this new patch.

Note: In 1.0.14.5.0, OCA.PLL is still provided, but it is implemented using EXEC_SQL. OCA.PLL may not always be provided.

Once you have the patch, just write PL/SQL program units that use EXEC_SQL (similar to DBMS_SQL on the Oracle 7.3 server) to fetch data from whatever database you want, and display it in your forms.

Contact me if you have any questions.

• Art Clarke Open Interfaces aclarke_at_us.oracle.com

--