-
Oracle Forms
Hi,
Is there any way to connect to MS-Access or sql-server from Oracle Forms.Thanks In Advance
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|