Re: stored procedures and SQL*Connect

In article <19940110.072456.776_at_almaden.ibm.com>, alanb_at_vnet.IBM.COM (Alan Beal) wrote:

> Can SQL*Connect be invoked from a stored procedure? For example,
> suppose you have a program that invokes a stored procedure in
> an Oracle7 database. Can this stored procedure invoke a non-Oracle
> database using SQL*Connect? Or must the program talk directly to
> SQL*Connect? What exactly are the interfaces to SQL*Connect?
>
> Alan Beal alanb_at_vnet.ibm.com
> (607)751-2026

First you need to understand how SQL*Connect works. SQL*Connect makes a non-Oracle database look like a Oracle database to an Oracle user. At my company we have SQL*Connect to DB2 and SQL*Net TCP/IP installed on 3 of our MVS mainframes. On our VAX and Unix machines we create public database links and public synonyms that make the DB2 tables appear like locate tables. DB2 is treated as just another Oracle database accessible via SQL*NET TCP/IP. From an Oracle program (i.e. stored procedure) the user access the table as if it was a locate table (you will get errors if you use a SQL extension that DB2 doesn't understand i.e. decode). This works great for query as long as you are not doing large distributed joins. Large distributed joins will run slow and should be avoided. Update requires that you have Oracle's Distributed Option.

-- 
==============================================================================
Dave Erwin
Lockheed Missiles & Space Company
Email: erwin_at_lmsc.lockheed.com