DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Oracle Forms

  1. #1
    Join Date
    Dec 2002
    Posts
    40

    Oracle Forms

    Hi,

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

  2. #2
    Join Date
    Aug 2003
    Posts
    11
    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
  •  


Click Here to Expand Forum to Full Width