Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation