Oracle forms connecting SQL Server [message #286321] |
Fri, 07 December 2007 03:05  |
mann.rulz
Messages: 5 Registered: December 2007 Location: Kerala
|
Junior Member |
|
|
Could anyone please guide me in detail to connect Oracle Forms 9i/10g with MS SQL Server database?
To connect with Oracle Database, we need to copy and paste the tnsnames.ora file from the Oracle Database folder to the Oracle Forms folder.
But how do i connect with other databases like SQL Server, MS Access, and so on?
Thanks in advance.
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Oracle forms connecting SQL Server [message #286922 is a reply to message #286878] |
Mon, 10 December 2007 08:00   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Ok, here it is. A small example using Access instead of MS SQL Server.
NOTE: No guarantees whatsoever. This example just shows how to connect to an Access database from within Forms. I strongly suggest that you do this from the database and not from forms directly. But for the sake of the example, here goes:
Environment:- WIndows XP
- Oracle 10g XE
- MS Access 2003
- Forms 10g
STEP I:
Create a simple access database. In this example, it is "C:\orafaq\Orafaq.mdb". This database contains a single table, called "emp" with the columns "ename" and "empno".
STEP II:
In the ODBC Data source administrator, you need to add a System DSN pointing to this database. So you select "MS Access Driver" and add the name "OrafAccess" in the data source name. Make sure you've added the correct .mdb file (orafaq.mdb)
STEP III:
Now we set up the HS init file: it's name is fixed (init<ODBCNAME>.ora). It contains only two lines of code:
$ORACLE_HOME/hs/admin/initOrafAccess.ora:
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = OraFaccess
HS_FDS_TRACE_LEVEL = 0
STEP IV:
Next, I changed the listener.ora of my XE database:
$ORACLE_HOME/network/admin/listener.ora:
added in SID_LIST:
(SID_DESC =
(SID_NAME = OrafAccess)
(ORACLE_HOME = <oracle_home>)
(program = hsodbc)
)
STEP V:
$ORACLE_HOME/network/admin/tnsnames.ora:
The setup of the HSODBC is done, and we need to make it accessible for our Oracle users. Like any other database there has to be an entry in the TNSNAMES.ORA file:
ORAFACCESS =
(description =
(address = (protocol=tcp)(host=localhost)(port=1521))
(connect_data = (sid=orafaccess))
(hs=ok)
)
STEP VI:
bounce the listener. You can do this befor step V, but I just did it in this order.
STEP VII:
SQL*Plus:
SQL> create database link orafaccess using 'ORAFACCESS';
Database link created.
SQL> desc emp@orafaccess
Name Null? Type
----------------------------------------- -------- ----------------------------
empno NUMBER(10)
ename VARCHAR2(50)
SQL> create synonym access_emp for emp@orafaccess;
Synonym created.
SQL> desc access_emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
empno NUMBER(10)
ename VARCHAR2(50)
SQL>
STEP VIII:
In Forms we make a basic form:
- data block wizard
- table Access_emp
- key mode UPDATEABLE (rowid won't work)
- empno = primary key
- layout wizard
- tabular design
I added double quotes (") around the column names in the property palette. I also added the following triggers:
ON-COMMIT:
ON-LOCK:
ON-DELETE:
BEGIN
DELETE_RECORD;
END; I'm not 100% convinced that the ON-DELETE trigger is necessary but it didn't hurt. The ON-LOCK trigger and ON-COMMIT triggers were necessary due to differences in Access locking mechanism. I can't tell whether it will work like that in MS SQL Server...
Test the form. Enjoy!
How's that as a step for step explanation .
MHE
[edit]typo...again
[Updated on: Tue, 11 December 2007 01:13] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|