External procedure issue

From: <slezakp_at_billmax.com>
Date: Thu, 21 Dec 2000 04:29:16 GMT
Message-ID: <91s0uo$bk9$1@nnrp1.deja.com>

Yet another question about external procedures.

I have plugged in verbatim the example out of the PL/SQL programming book by O'Reilly and I still get the

ORA-28575: unable to open RPC connection to external procedure agent

I started a separate listener using

LSNRCTL> start external_procedure_listener

I turned on trace and saw absolutely no activity when I tried to connect.
I even tried a "tnsping EXTPROC_CONNECTION_DATA" and saw no activity in the trace file. Instead I got TNS-03506: Failed to create address binding. Don't know if I should be able to tnsping this type of resource.

For what it is worth, it is Intel Solaris 2.6, Oracle 8.1.6 with 128 MB RAM. Thanks in advance for any help you can offer.

Paul

Here are the gory details:

In listener.ora (The only stuff in listener.ora)-

EXTERNAL_PROCEDURE_LISTENER =
    (ADDRESS_LIST =

        (ADDRESS =


            (PROTOCOL=IPC)

            (KEY=extproc)

        )

    )

SID_LIST_EXTERNAL_PROCEDURE_LISTENER =
    (SID_LIST =

        (SID_DESC=


            (SID_NAME=extproc)

            (ORACLE_HOME=/u01/app/oracle/product/8.1.6)

            (PROGRAM=extproc)

        )

    )

In tnsnames.ora (along with two database entries) -

EXTPROC_CONNECTION_DATA =

                         (DESCRIPTION =
                              (ADDRESS = (PROTOCOL=IPC)
                                         (KEY=extproc)
                               )
                             (CONNECT_DATA =(SID=extproc)
                          )

And finally the stuff done in sqlplus:

CREATE OR REPLACE LIBRARY libc_1
AS

   '/lib/libc.so';

CREATE OR REPLACE
PACKAGE random_utl
IS

    FUNCTION rand
    RETURN PLS_INTEGER;
    PRAGMA RESTRICT_REFERENCES (rand, WNDS, RNDS, WNPS, RNPS);

    PROCEDURE srand

       (seed IN PLS_INTEGER);
    PRAGMA RESTRICT_REFERENCES (srand, WNDS, RNDS, WNPS, RNPS);

END random_utl;
/

CREATE OR REPLACE
PACKAGE BODY random_utl
IS

    /* Tested with: (1) Solaris 2.5.1 and Oracle 8.0.3     || (2) Windows NT 4.0 and Oracle 8.0.3     */
    FUNCTION rand RETURN PLS_INTEGER
    /* Return a random number between 1 and (2**16 -1), using     || the current seed value
    */
    IS EXTERNAL

       LIBRARY libc_1
       NAME "rand"
       LANGUAGE C;

    PROCEDURE srand (seed IN PLS_INTEGER)     /* Store a seed value used by external rand() function */     IS EXTERNAL

       LIBRARY libc_1
       NAME "srand"
       LANGUAGE C
       PARAMETERS (seed ub4);

END random_utl;
/

• Now call the external function a few times

SET SERVEROUTPUT ON SIZE 1000000 DECLARE
   rnd_value PLS_INTEGER;
   seed PLS_INTEGER;
BEGIN
   /* Generate a seed value from the current system time. */    SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO seed FROM DUAL;

   /* Call the srand external procedure to store our seed in memory. */    random_utl.srand (seed);

   /* Now demonstrate some random numbers. */    FOR v_cnt IN 1 .. 10 LOOP

      rnd_value := random_utl.rand;
      DBMS_OUTPUT.PUT_LINE ('rand() call #' || v_cnt ||
                            ' returns ' || rnd_value);

   END LOOP;
END;
/

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 20 2000 - 22:29:16 CST