RE: Remote Procedure

From: ALTAIR, RSP_4 <RSP_4.ALTAIR_at_EU.RHODIA.COM>
Date: Tue, 6 Jun 2000 17:52:52 +0200
Message-Id: <10520.107908@fatcity.com>

Hi Jared

Here is an example of remote procedure (In reality, the package is 130 = Ko
...)

CREATE OR REPLACE PACKAGE EXAMPLE AS
   PROCEDURE MYPROCEDURE(DATABASE VARCHAR2,CONNECTSTRING = VARCHAR2,SCHEMA
VARCHAR2,

                        USER VARCHAR2,PASSWORD VARCHAR2,NAME VARCHAR2);

END EXAMPLE;
/

CREATE OR REPLACE PACKAGE BODY EXAMPLE AS   PROCEDURE MYPROCEDURE (DATABASE VARCHAR2,CONNECTSTRING = VARCHAR2,SCHEMA
VARCHAR2,

                        USER VARCHAR2,PASSWORD VARCHAR2,NAME VARCHAR2) =
IS
     cid INTEGER;
     dblink BOOLEAN;
   BEGIN


      /* Setting dblink to false */

      dblink :=3D FALSE;


      /* Open new cursor and return cursor ID */

      cid :=3D DBMS_SQL.OPEN_CURSOR;


      /* Create Database link to import data from desktop server */

      /*DBMS_OUTPUT.PUT_LINE('Creating Database Link ...');*/
      DBMS_SQL.PARSE(cid,'CREATE DATABASE LINK ' || DATABASE || ' =

CONNECT TO
' || USER ||=20

                     ' IDENTIFIED BY ' || PASSWORD || ' USING '=20
                     || '''' || CONNECTSTRING || '''',dbms_sql.NATIVE);
      dblink :=3DTRUE;
      DBMS_SQL.PARSE(cid,'LOCK TABLE ' || SCHEMA || '.' || NAME || '@' =

||
DATABASE || ' IN EXCLUSIVE MODE',dbms_sql.NATIVE);

/* Destroy database link */ =20

      DBMS_SQL.PARSE(cid,'DROP DATABASE LINK ' || = DATABASE,dbms_sql.NATIVE);

/* Close cursor */

      DBMS_SQL.CLOSE_CURSOR(cid);
   EXCEPTION
   WHEN OTHERS THEN

       DBMS_SQL.CLOSE_CURSOR(cid);
       if (dblink=3DTRUE) then
          EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || DATABASE;
       end if;
       RAISE; -- reraise the exception

  END MYPROCEDURE; END EXAMPLE;
/

• Commit

COMMIT COMMENT 'Package EXAMPLE created';

We execute this by calling :

sqlplus> execute
example.myprocedure('dbname','dbname','mychema','myuser','mypasswd','tab= leto
lock');

this succeeds in sqlplus

but if i do that in VB it fails !

Do you need an example for VB ?

Thanks

Seb

> -----Message d'origine-----
> De:	Jared Still [SMTP:jkstill_at_bcbso.com]
> Date:	mardi 6 juin 2000 16:25
> =C0:	ALTAIR, RSP_4
> Cc:	Multiple recipients of list ORACLE-L
> Objet:	Re: Remote Procedure
>=20
>=20
> We have done this with VB and Oracle stored code, and it
> continues to work properly.
>=20
> Code examples would be nice.
>=20
> Also, the error message indicates that this may be a bug. =20
>=20
> Jared
>=20
> On Mon, 5 Jun 2000, ALTAIR, RSP_4 wrote:
>=20
> > Hi,
> >=20
> > Currently, we are developing a client server architecture with =

Visual

> Basic
> > and Oracle 8i.
> > We are using :
> > Visual Basic 6.0 SP3 (We used Remote Data Object to connect to =

Oracle)

> > ORACLE 8i v8.1.5.0.0 + patch v8.1.5.1.0
> > ODBC Driver v8.01.56.00
> >=20
> > Client can be running a version of Personal Oracle 8 and we have a
> pl/sql
> > procedure on them that create a=20
> > database link and lock tables on main server.
> >=20
> > When we connect to laptop database sevrer via VB/RDO/ODBC, and we =

call

> the
> > pl/sql procedure the oracle laptop server returns=20
> > ORA-2041 ?
> >=20
> > What is the problem ? How to correct this ? Is there a patch ?
> >=20
> > I think that the problem comes from ODBC Driver because when we =

launch
> the
> > same procedure in a sqlplus environment (connected to laptop = server),

> the
> > procedure succeeds.
> >=20
> > Thanks
> >=20
> > Seb
> >=20
> > --=20
> > Author: ALTAIR, RSP_4
> >   INET: RSP_4.ALTAIR_at_EU.RHODIA.COM
> >=20
> > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California        -- Public Internet access / Mailing =

Lists
> > =

---

> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like =

subscribing).

> >=20
>=20
>=20
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist  ;-)

Received on Tue Jun 06 2000 - 10:52:52 CDT