Oracle Forms

[mys]

Hi,

Is there any way to connect to MS-Access or sql-server from Oracle Forms.Thanks In Advance

[slash]

First create an odbc Data source through the control panel

Attach EXEC_SQL.pll to the form. Its available in
%ORACLE_HOME%\oca20\plsqllib

In a WHEN-BUTTON-PRESSED trigger for a push button use the following code.

DECLARE
connection_id EXEC_SQL.ConnType;
cursor_number EXEC_SQL.CursType;

-- Variables for the data to be returned into
v_empno NUMBER;
v_firstname VARCHAR2(10);
v_lastname VARCHAR2(20);

-- Control variables
irc NUMBER;

BEGIN
-- Connection to Access Databse

connection_id := EXEC_SQL.OPEN_CONNECTION('scott/tiger@odbc:<>');

IF EXEC_SQL.IS_CONNECTED(connection_id) THEN
message('Connected to external db');
ELSE
message('ERROR: No connection established');
END IF;

-- Opening a Cursor to insert data into a table in the database selected

cursor_number := EXEC_SQL.OPEN_CURSOR(connection_id);

IF EXEC_SQL.IS_OPEN(connection_id,cursor_number) THEN
message('Insert Cursor is opened');
END IF;

-- Parsing the data to Insert a new row into the Employees table
--- Define the statement

EXEC_SQL.PARSE(connection_id,cursor_number,'insert into employees(FirstName, LastName ,Birthdate) values (''aaaa'',''bbbb'',''01-JAN-1998'')');


-- Executing the Parsed Statement

irc := EXEC_SQL.EXECUTE(connection_id,cursor_number);
message(TO_CHAR(IRC)||' A Row hase been inserted into employee table in the external database');

-- Close the Insert Cursor

EXEC_SQL.CLOSE_CURSOR(connection_id,cursor_number);

message('Closed the Insert Cursor');

-- create a cursor for the commit statement so that inserted

cursor_number := EXEC_SQL.OPEN_CURSOR(connection_id);

IF EXEC_SQL.IS_OPEN(connection_id,cursor_number) THEN
message('Commit Cursor is opened');
END IF;

-- Commit Statement

EXEC_SQL.PARSE(connection_id,cursor_number,'commit');

-- Execute the the Parsed statement for committ

irc := EXEC_SQL.EXECUTE(connection_id,cursor_number);

message('Insert Committed');

-- Close the commit cursor

EXEC_SQL.CLOSE_CURSOR(connection_id,cursor_number);

message('Closed the Commit Cursor');

-- Open another cursor to select data from employee table of

cursor_number := EXEC_SQL.OPEN_CURSOR(connection_id);

IF EXEC_SQL.IS_OPEN(connection_id,cursor_number) THEN
message('Select Cursor is open');
END IF;


EXEC_SQL.PARSE(connection_id,cursor_number, 'select EmployeeId,FirstName,LastName from employees');

-- Define the columns for the data to be returned

EXEC_SQL.DEFINE_COLUMN(connection_id,cursor_number,1,v_Empno);
EXEC_SQL.DEFINE_COLUMN(connection_id,cursor_number,2,v_FirstName,10);
EXEC_SQL.DEFINE_COLUMN(connection_id,cursor_number,3,v_LastName,20);

-- Execute the Cursor

irc := EXEC_SQL.EXECUTE(connection_id,cursor_number);
-- Loop around and fetch each row from the result set.

WHILE EXEC_SQL.FETCH_ROWS(connection_id,cursor_number) > 0
LOOP
EXEC_SQL.COLUMN_VALUE(connection_id,cursor_number,1,v_Empno);
EXEC_SQL.COLUMN_VALUE(connection_id,cursor_number,2,v_FirstName);
EXEC_SQL.COLUMN_VALUE(connection_id,cursor_number,3,v_LastName);

-- message the result
message('Fetched: '||v_FirstName||' '||v_LastName||'Empno='||to_char(v_Empno));
END LOOP;

-- Close the cursors

EXEC_SQL.CLOSE_CURSOR(connection_id,cursor_number);

message('Normal End - Cleaned up Cursor');

EXEC_SQL.CLOSE_CONNECTION(connection_id);

message('Normal End - Cleaned up Connection');

EXCEPTION
WHEN EXEC_SQL.INVALID_CONNECTION THEN
message('Unexpected Invalid Connection error from EXEC_SQL');

WHEN EXEC_SQL.PACKAGE_ERROR THEN
message('Unexpected error from EXEC_SQL: '||to_char(EXEC_SQL.LAST_ERROR_CODE(connection_id))|| EXEC_SQL.LAST_ERROR_MESG(connection_id));

IF EXEC_SQL.IS_OPEN(connection_id,cursor_number) THEN
EXEC_SQL.CLOSE_CURSOR(connection_id,cursor_number);
message('Exception - Cleaned up Cursor');
END IF;


IF EXEC_SQL.IS_CONNECTED(connection_id) THEN
EXEC_SQL.CLOSE_CONNECTION(connection_id);
message('Exception - Cleaned up Connection');
END IF;

END;