Re: AGAIN: How to issue connect statement in PL/SQL

From: Bill Manry <B.Manry_at_upsizeme.us.oracle.com>
Date: 3 Jun 1998 00:51:02 GMT
Message-ID: <6l26lm$j4c$1@inet16.us.oracle.com>

Nicholas Moo (nmoo_at_netvigator.com) wrote:
>How to issue a "connect xx/xx_at_yyy" statement within PL/SQL.

You can't.

>I don't want to use db link for some special reasons, one of it is I want
>to do some DDL within the PL/SQL block. Some of the DDL do not support db
>link (or I don't know how to do it) as I've tried.

The only way to directly access one Oracle database from a stored procedure running in another is with a database link, which uses distributed transaction semantics (2-phase commit) to ensure integrity of updates. The distributed transaction mechanisms prohibit DDL/DCL because it is self-committing.

You might be able to use the DBMS_PIPES packaged procedures to communicate from your PL/SQL procedure to a separate Pro* or OCI program that connects to another Oracle database and issues the DDL/DCL you want.

HTH,
/b
--
Bill Manry - IBM Products Division - Oracle Corporation These are my opinions, not necessarily Oracle's. Remove "." from "B.Manry" to email me. Received on Tue Jun 02 1998 - 19:51:02 CDT