Q

Accessing an ODBC data source, part 2

In my Oracle forms, I want to access another ODBC data source from our current database. How can I use command OPEN_CONNECTION in library EXEC_SQL?

Continued from part 1.

Configuring an ODBC datasource

1. Go to Start --> Settings --> Control Panel from your desktop.

2. Click the ODBC icon.

3. Click on User DSN/System DSN and click add to create a new data source. Select the SQL Server ODBC Driver and click finish. Create it as a System DSN.

4. Give a name and description for data source name. Use the dropdown list item to select the name of the SQL Server database you want to connect. If the database is installed on the local hard drive, it will be (local).

5. Check the radio button to use SQL Server Authentication to logon. You can also choose Windows NT authentication. It all depends on how SQL Server database is configured. Specify the username and passwords for the user. Again, this user should already exist in SQL Server database and password should match. Click Next.

6. Change the default database to whatever database you want to use in SQL Server. In this case, it is pubs.

7. You can also specify files for log file etc., but they are not required. Click Finish.

8. Test the ODBC connection. If the TEST is not successful, check all steps described above.

Creating data dictionary views:

Run the script from $ORACLE_HOMEoca60sqlsqlsrvsqs60bld.sql to create required views in the SQL Server data dictionary. The scripts needs to be run as sa/***** (sa is the system user in SQL Server database).

Connection from Forms and Data Block Wizard (Developer 2000)

Connect from forms as username/password@odbc:datasource_name. Use the normal Data Block Wizard to create the block.

1. Attach the oca.pll, located in the %ORACLE_HOME%/oca60/plsqllib directory, to the desired fmb. For this directory to appear the Oracle Open Client Adapter Component needs to be installed from the base Developer6i R2 CD.

2. Write a program unit similar to the following:

FUNCTION get_access RETURN pls_integer IS 

connection pls_integer; 

cursor1    pls_integer; 

stmt       varchar2(1000); 

n          pls_integer; 

BEGIN 

stmt := 'select count(*) from emp'; 

connection := oca.dbconnect('scott/tiger@odbc: datasource_name '); 

message(to_char(connection)); 

cursor1 := oca.execute(connection,stmt); 

loop 

begin 

oca.fetch_row(connection,cursor1); 

oca.coldata(connection,cursor1,1,n); 

message(to_char(n)||' record(s)'); 

exception 

when no_data_found then 

message('NO MORE ROWS'); 

exit; 

end; 

end loop; 

oca.close(connection,cursor1); 

oca.disconnect(connection); 

return n; 

exception 

when oca.connection_error then 

message('OCA-Connection error'); 

when oca.cursor_error then 

message('OCA-Cursor error'); 

when others then 

message('Other Error'); 

end; 

The oca_info.pdf is available in the following directory of the installation: ORACLE_HOMETOOLSDOC60USoca_info.pdf

This was first published in November 2003

Dig deeper on Oracle E-Business Suite

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close