How to bind a stored procedure parameter in oraperl? (LONG)

From: Carl Tichler <tichlc_at_dali.pfizer.com>
Date: 1995/05/12
Message-ID: <TICHLC.95May12100437_at_dali.pfizer.com>#1/1

I am trying to set up an oraperl script to run as a daemon, reading requests from a database pipe (DBMS_PIPE). I have been successful in returning rows from a SQL query in oraperl, but to use a pipe requires me to bind variables for both input and output to stored procedures. I have spoken to ORACLE support, and they have given me some sample PRO*C code to do this, ("Problem # 1005666.6, Solution# 2039453.6, DYNAMIC SQL AND SYSTEM COMMANDS USING DBMS_PIPE"). When I ran the oraperl "server" script, and then tried to send requests to it on the pipe, I got the following oracle error:

ORA-01041: internal error. hostdef extension doesn't exist

Here is some of the code -- if you would like to see the whole thing, please e-mail me at catech_at_cloud9.net. I will use the PRO*C for the time being, but I suspect that it is just a flaw in the way I am constructing the bind in oraperl.

Thanks in advance for any pointers!

Carl Tichler (catech_at_cloud9.net)

• Code ----------

The stored procedure prototypes are as follows:

• beginning of sql procedure definitions --- create or replace function shell_command (command varchar2) return varchar2 is ... [THIS IS THE CLIENT, WHICH SENDS REQUESTS]

create or replace package shell_server is

	procedure get_shell_command(command out varchar2);
	procedure return_shell_result(errormsg in varchar2,
		result_msg in varchar2);

[THIS IS THE PACKAGE WHICH IS CALLED BY THE SERVER TO RETRIEVE REQUESTS, AND THEN SEND ERRORS]

• end of sql procedure definitions ---

Here is the part of the perl script (server) which attempts to call the stored procedures and bind to their results:

• beginning of perl script (fragment) ---
  # Set up the SQL statement which will get shell requests.
  # The substitution variable :1 is the requested statement.
  #
  

$sql_request_stmt=<<EOR;
begin shell_server.get_shell_command(:1); end; EOR
    1; # dummy statement

#
# Set up the SQL statement which will send back the returned result
# and/or errors.
# The substitution variables :1 and :2 are the
# error (if any) and the resulting output from the command.
#

$sql_sendback_stmt=<<EOS;
begin shell_server.return_shell_result(:1,:2); end; EOS
    1; # dummy statement
# now start the request loop.

while (1) {

    # get the next request.
    $csr = &ora_open($lda,$sql_request_stmt) ||

        die "Oracle error: $ora_errstr\n";
    &ora_bind($csr,$request_stmt); # ORACLE ERROR OCCURS HERE!     &ora_close($csr) || die "Oracle error: $ora_errstr\n";     print "Request statement is $request_stmt\n";

    # send back the "result".

    $no_error = "";
    $ret_result = "OK";
    $csr = &ora_open($lda,$sql_sendback_stmt) ||
	die "Oracle erros: $ora_errstr\n";

    &ora_bind($csr,$no_error,$ret_result);     &ora_close($csr) || die "Oracle error: $ora_errstr\n"; }

exit;
--- end of perl script (fragment) --- Received on Fri May 12 1995 - 00:00:00 CEST