Re: Help Needed External Procs ORA-28575

Thanks a lot for your help. It works nicely.

However, I want to pass a "table of binary_integer index by binary_integer" to a C procedure and back and I want to process it within the C procedure as int** (one binary_integer can be processed as an int and back as an int*).

Do you also have an example for this?

Martin

TimKArnold wrote:
>
>
>
> Bookmark Fixed font Go to End
>
> Doc ID: Note:70638.1
> Subject: External Procedures - Troubleshooting ORA-28575 Errors
> Type: BULLETIN
> Status: PUBLISHED
> Content Type: TEXT/PLAIN
> Creation Date: 03-JUN-1999
> Last Revision Date: 18-APR-2000
> Language: USAENG
>
>
> External Procedures - Troubleshooting ORA-28575 on UNIX Platforms
> =================================================================
>
> Introduction
> ------------
>
> This article provides a brief overview of the Oracle external procedure
> architecture and provides a number of suggestions for troubleshooting
> the ORA-28575 error. Although the emphasis of this bulletin is on the
> UNIX environment, many of the concepts also apply to Windows and other
> environments. All concepts apply equally to Oracle 8 and 8i.
>
> This article does not address all aspects of external procedures. It
> provides just enough background information to provide a context for
> discussing the ORA-28575 error which is the focus of this article.
>
> Oracle's External Procedure Architecture
> ----------------------------------------
>
> A list of the essential components for executing an external procedure
> is included below, along with a brief description of the role of each.
>
> The database:
>
> - Responsible for storing any information required to locate and
> execute an external procedure (e.g. the location of shared
> library, number and types of arguments, etc.)
>
> - Initiates the call to the procedure via the listener and external
> procedure agent
>
> - Both the library alias and call specification are stored in the
> database.
>
> The listener:
>
> - Listens for external procedure requests from databases and starts
> external procedure agent processes (one per session)
>
> The external procedure agent:
>
> - Accepts information (arguments, shared library location, etc.) from
> the database, loads the shared library as required, and executes the
> corresponding C function
>
> - Corresponds to the extproc executable which resides in $ORACLE_HOME/bin
>
> The user-provided shared library:
>
> - Contains the user-written function that implements the desired
> functionality
>
> The library alias:
>
> - Records the location of the shared library within the database
>
> - Used in the PL/SQL call specification
>
> The external procedure call specification:
>
> - Provides a description of the procedure, including its PL/SQL name,
> arguments and types, and the name of the library alias and C function
> implementing the procedure
>
> A Typical Sequence of Events
> ----------------------------
>
> The following is a typical sequence of events:
>
> 1. The user invokes the call specification corresponding to an external
> procedure via PL/SQL or some other programmatic interface.
>
> 2. The database examines the call specification to find the name of
> the shared library implementing the function, as well as the name
> of the function and the number and type of the arguments.
>
> 3. The database uses the extproc_connection_data service name in the
> tnsnames.ora file to contact the external procedure listener.
>
> Note: This step only occurs the first time the external procedure
> is invoked within a given session. After that, the existing
> external procedure agent can be contacted directly.
>
> 4. The listener starts an external procedure agent process to service
> the external procedure execution request and returns the address
> of the new extproc agent to the requesting database.
>
> 5. The database sends the external procedure agent the name of the
> shared library, the name of the C function to invoke, and after
> performing the required conversions from PL/SQL to C datatypes,
> the actual arguments.
>
> 6. The external procedure agent loads the shared library, invokes
> the appropriate C function, converts any returned data from C
> to the corresponding PL/SQL types, and sends the data back to
> the database.
>
> Why does the ORA-28575 error occur?
> -----------------------------------
>
> The ORA-28575 error indicates that a failure occurred in steps 3-4
> above. Either the database was unable to establish a connection to
> the external procedure listener (step 3), OR the external procedure
> listener was unable to execute the extproc agent (step 4).
>
> Troubleshooting the ORA-28575 error
> -----------------------------------
>
> Reasons for the ORA-28575 include:
>
> o Issues with extproc
>
> o Database configuration issues
>
> o Listener configuration issues
>
> Issues with extproc
> -------------------
>
> You can receive this error if the extproc executable either does
> not exist, OR the listener does not have permissions to execute it,
> OR you cannot execute extproc for any other reason.
>
> Check the following:
>
> 1. Verify that the extproc executable specified in listener.ora
> exists in the bin directory of the specified $ORACLE_HOME.
> See the section on listener configuration below for more information.
>
> 2. Verify that extproc can be executed by the listener. On many
> systems, the listener executes with the effective user id
> of the oracle user, but this can be changed. Verify that the
> permissions are available for whatever user is applicable.
>
> 3. Other problems such as file corruption could also cause the
> extproc program to not be executable. Perform the following
> steps to verify whether the extproc program is valid and executable:
>
> a. Log in to the system as the applicable user.
> b. cd to the $ORACLE_HOME/bin directory.
> c. Execute the following command at the prompt:
>
> $ ./extproc
>
> If all is well, you should see a banner displayed similar to
> the following (the text may differ slightly):
>
> Oracle Corporation --- Wed Jul 19 11:00:04.139352
>
> Heterogeneous Agent based on the following module(s):
> - External Procedure Module
>
> If you do not see such a banner or you receive an error, you
> may need to reinstall and/or rebuild the extproc executable
> or change the permissions to make it executable.
>
> Database Configuration Issues
> -----------------------------
>
> This article does not address database configuration issues other
> than those which result in the ORA-28575 error.
>
> Database configuration issues resulting in an ORA-28575 include:
>
> 1. The database is referencing the wrong copy of tnsnames.ora
>
> In some environments, multiple copies of the tnsnames.ora file
> may exist in the file system. The copy used by the database is
> that which is pointed to by TNS_ADMIN when the database is started.
>
> To ensure that your changes to the tnsnames.ora file are picked
> up by the database, you may need to shut down the database, set
> the TNS_ADMIN environment variable to indicate the directory with
> the appropriate file, and restart the database.
>
> 2. The tnsnames.ora file is improperly configured
>
> The tnsnames.ora file must be configured to contain a special
> service name called extproc_connection_data. This service name
> provides the database with the necessary parameters to connect
> to the external procedure listener. The entry looks like:
>
> extproc_connection_data =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key))
> (CONNECT_DATA = (SID = extproc_agent))
> )
>
> a. extproc_connection_data cannot be changed except as follows:
>
> If, and only if, the sqlnet.ora file contains the following:
>
> names.default_domain = some.domain (e.g. us.oracle.com)
>
> then you must modify the above tnsnames.ora entry as follows:
>
> extproc_connection_data.some.domain =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_key))
> (CONNECT_DATA = (SID = extproc_agent))
> )
>
> Setting default_domain causes some.domain to be automatically
> appended to all service names that are not already qualified
> with a domain. This is also true for extproc_connection_data.
>
> If default_domain is set, and that domain is not appended to
> the service name in tnsnames.ora, the connection fails.
>
> Note: As was the case for tnsnames.ora, the database uses the
> sqlnet.ora file that was pointed to by TNS_ADMIN at the
> time the database was last started. Make sure you are
> looking at the right copy of the file.
>
> b. extproc_key must be the same as the KEY=extproc_key entry in
> the listener.ora file (see below). Otherwise, it can be any
> string that is not already in use. This uniquely identifies
> which external procedure listener is to be contacted.
>
> c. extproc_agent must be the same as the SID_NAME=extproc_agent
> entry in the listener.ora file (see below). Otherwise, it can
> be any SID that is not already in use. This is the service
> that appears in the list displayed by 'lsnrctl status'.
>
> d. PROTOCOL must be set to IPC. Other protocols (e.g. TCP) are
> currently not supported for external procedures.
>
> 3. The names.directory_path parameter in the sqlnet.ora file does
> not include the tnsnames.ora file as a source of directory info.
>
> If the sqlnet.ora file pointed to the the TNS_ADMIN environment
> variable contains an entry of the following form:
>
> names.directory_path = (tnsnames, onames)
>
> then Net8 looks in both the tnsnames.ora file and Oracle Names
> to resolve service names (including extproc_connection_data). If
> the 'tnsnames' entry is omitted as follows:
>
> names.directory_path = (onames)
>
> then only Oracle Names is searched and the modifications to
> the tnsnames.ora file are not picked up by the database.
>
> Listener Configuration Issues
> -----------------------------
>
> This bulletin does not address listener configuration issues other
> than those which result in the ORA-28575 error.
>
> Listener configuration issues resulting in an ORA-28575 include:
>
> 1. The listener was started with the wrong copy of listener.ora
>
> The external procedure listener reads the listener.ora pointed to
> by TNS_ADMIN when it is started. If the listener is started prior
> to some modification to the listener.ora file, you must shut down
> the listener, set the TNS_ADMIN environment variable to point to
> the modified copy of listener.ora, and restart the listener.
>
> For example:
>
> $ lsnrctl stop callout_listener
> $ export TNS_ADMIN=/u02/etc # assumes Korn shell
> $ lsnrctl start callout_listener
>
> The name callout_listener is not fixed, but is used in this example
> for consistency with the examples that follow.
>
> 2. The listener.ora file is improperly configured
>
> When configuring the listener for external procedures, the following
> two options exist:
>
> o Configure the existing database listener to listen for external
> procedures and database connections
>
> or
>
> o Configure a separate listener specifically for external procedures.
>
> There are several benefits to the second option including:
>
> a. Maintenance is simplified since the external procedure listener
> can be started and stopped independent of database connections.
>
> b. Security is enhanced since a separate listener can be run with
> a different effective user id. Running external procedures as
> the 'oracle' (or equivalent) user can expose the database to
> unnecessary risk since user-written procedures can then have
> unrestricted access to database resources (e.g. datafiles).
>
> For these reasons, we recommend the second method. It is the only
> method addressed in this article.
>
> To properly configure a separate listener for external procedures:
>
> a. Decide on a name for the new listener (callout_listener in the
> examples that follow). This name appears in several places in
> the listener.ora file. Substitute the name you selected for
> callout_listener in the examples that follow.
>
> b. Modify the listener.ora file as follows:
>
> i. Add the following entries:
>
> callout_listener =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = IPC)
> (KEY = extproc_key)
> )
> )
>
> sid_list_callout_listener =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = extproc_agent)
> (ORACLE_HOME = oraclehomedir)
> (PROGRAM = extproc)
> )
> )
>
> where callout_listener is the name of the listener
> extproc_key agrees with the KEY in tnsnames.ora
> extproc_agent agrees with the SID in tnsnames.ora
>
> ii. Remove any entries related to external procedures from
> the default listener that is being used to listen for database
> connections if such entries exist. This is only an issue if
> you have previously attempted to configure a single listener
> for both purposes.
>
> iii. Restart the database listener to make sure it reflects the
> most recent listener.ora changes. Restarting the original
> listener is especially important if it was previously set
> to listen for external procedures.
>
> % lsnrctl stop listener
> % setenv TNS_ADMIN /u02/etc # assumes C shell
> % lsnrctl start listener
>
> Observe and correct any errors that may have resulted from
> modifying the listener.ora file (i.e. typographical errors).
>
> iv. Start the new external procedure listener.
>
> % lsnrctl start callout_listener
>
> Observe and correct any errors that may have resulted from
> modifying the listener.ora file (i.e. typographical errors).
>
> Testing Your Configuration
> --------------------------
>
> The database ships with a sample program which can be used to test your
> external procedure configuration. You can find the following files in
> the $ORACLE_HOME/plsql/demo directory:
>
> extproc.c # the C file implementing the external procedures
>
> extproc.sql # an SQL script for creating and invoking the required
> # packages and procedure/function call specifications.
>
> demo_plsql.mk # a makefile for compiling the extproc demo
>
> The steps for running the demo are:
>
> 1. Compile the extproc.c and create the extproc.so shared library
>
> $ make -f demo_plsql.mk extproc.so
>
> 2. Ensure user scott and requisite tables (e.g. EMP and DEPT) exist.
> Also, ensure that the DBMS_OUTPUT package exists and is valid.
>
> 3. Follow the steps outlined in the comments section of extproc.sql.
> A couple of steps must be completed before you run the extproc.sql
> script, including creating the LIBRARY and granting any privileges
> required.
>
> When creating the LIBRARY, make sure that you specify the correct
> location for your shared library. The example assumes that you put
> it in /tmp, however, this may not be the case.
>
> 4. From SQL*Plus, execute the following to create the PL/SQL package
> and call specifications and execute the demo:
>
> SQL> @extproc.sql
>
> When executed, the demo invokes several examples, each of which produces
> output to the screen via the DBMS_OUTPUT package. You should not see any
> errors. If you receive the ORA-28575, retrace the steps in this article to
> correct the problem. For other errors, refer to the documentation.
>
> References
> ----------
>
> For Oracle8:
>
> "PL/SQL User's Guide and Reference, Release 8.0" (A54654-01)
> "Net8 Administrator's Guide, Release 8.0" (A51576-01)
>
> For Oracle8i:
>
> "Application Developer's Guide - Fundamentals, Release 8.1.5" (A68003-01)
> "Net8 Administrator's Guide, Release 8.1.5" (A67440-01)
>
> Additional Search Words
> -----------------------
>
> unable to open RPC connection to external procedure agent
> .
>
>
>
> --------------------------------------------------------------------------
> ------
>
> Copyright (c) 1995,1999 Oracle Corporation. All Rights Reserved. Legal Notices
> and Terms of Use.
Received on Mon May 29 2000 - 00:00:00 CDT