Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

ORA-306500

297 views

Skip to first unread message

Rosario

unread,

Jan 10, 2002, 1:04:25 PM1/10/02

Podczas prob loczenia z ORACLE po ODBC mam taki komunikat jak w temacie. O
co w tym chodzi?

Sławomir Marcinkowski

unread,

Jan 11, 2002, 3:51:56 AM1/11/02

Może coś pomoże :)

BookmarkFixed font Go to End

Doc ID: Note:67516.1
Subject: CONNECTING TO AND WORKING WITH MULTIPLE DATASOURCES AT THE SAME
TIME FROM FORMS
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 30-DEC-1998
Last Revision Date: 04-DEC-2001

PURPOSE
=======

This bulletin gives steps and sample code to connect and work with multiple
data-sources at the same time from Oracle Forms using EXEC_SQL package.

SCOPE & KNOWLEDGE
===================

This bulletin is intended for the user who has good knowledge of using
PL/SQL
procedures and functions. The user should also have knowledge on ODBC.

USING EXEC_SQL TO WORK WITH MULTIPLE DATASOURCES FROM FORMS
===============================================================

INTRODUCTION
=============

Using normal ODBC connectivity, it is possible to connect to non-Oracle
databases and work with them. However, using ODBC, simultaneous connections
can not be made to two different data sources. For example, if an user
wants to make a native Oracle connection from forms and then connect to
an ODBC MS Access data source at the same time, this normally does not work.
This problem can be resolved using EXEC_SQL package.

EXEC_SQL package allows the users to execute dynamic SQL against
several different databases on several different connections at the
same time. The connections may be native Oracle connections or/and
ODBC data-sources. For complete information on EXEC_SQL and its
compatibility with different databases, please refer to the
exec_sql.pdf file that comes with your Oracle Developer/2000 documentation.

EXPLANATION OF THE SOLUTION
==============================

This demo application is written to work with northwind.mdb - a MS Access
demo database. Refer to sample databases in MS Access for further
information on the samples.

This demo application explains on how to make connections, how to insert
data into foreign data sources, and how to select data from them.

Step 1: Creating an ODBC Data Source
----------------------------------------------------

1. Go to Start -> Settings -> Control Panel from your desktop.
2. Click the ODBC icon OR Click on User DSN/System DSN
(see online help for more info)
3. Click add to create a new data source
4. Select the ODBC server you want to use (for this example, use
Microsoft Access ODBC Server) and click finish, you will be taken
to ODBC Microsoft Access 97 Setup Screen
5. Give the name of the database as Data Source Name (Northwind)
6. Under database section, select the database you want to use in this
data source (for MS Access, it will be a file with .mdb extension,
in this example nwind.mdb is used)
7. Click Advanced, and give scott as login and tiger as password
and click ok
8. Click ok
9. Click ok

Step 2 Building the Form
-------------------------

1. Build a form with one control block on a canvass
2. Include one push button in that block
3. Make EXEC_SQL.pll as an Attached Library (normally it's in
%ORACLE_HOME%\oca20\plsqllib
4. Create a WHEN-BUTTON-PRESSED trigger for the push button.
5. Write the following code in the trigger

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:northwind');

if EXEC_SQL.IS_CONNECTED(connection_id) then
message('Connected to Northwind');
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 northwind 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
-- data into northwind databse gets committed

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
-- northwind database

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;

-- Parse the Select statement

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

-- Define the columns for the data to be returned into.

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));
-- And Clean up

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;

Step 3 Connecting and Working with Multiple Datasources
-------------------------------------------------------------------------
1. From Forms, connect to native Oracle Connection as scott/tiger
2. Then Run the form, and Click the push button to see how it works
with northwind database in Microsoft Access

Caution: If the database that is being opened by an ODBC data source
is already open, then cursor handlers may cause problems.