pl/sql stored procedure with Table input 
Author Message
 pl/sql stored procedure with Table input

    I have an Oracle PL/SQL Stored Procedure that I need to call from a
perl environment using DBI.  I've already got several working examples
in my scripts using non-Table data types, but this one eludes me.

Here's the header file for the package in question:

CREATE OR REPLACE PACKAGE coe AS
        TYPE TableMessageID
                IS TABLE OF coe_history.message_identifier%TYPE
                INDEX BY BINARY_INTEGER;

        PROCEDURE deleteUidlList(
                        t       IN      TableMessageID,
                        user    IN      coe_history.username%TYPE,
                        coe_id  IN
coe_history.coe_configuration_id%TYPE
                );
END coe;

In my perl code I have the following currently:

$sth->prepare( q{
    BEGIN
        deleteUidlList( ?, ?, ? );
    END; } );
my( $t ) = [ 'foo', 'bar' ];
my( $user ) = 'joe';
my( $coe_id ) = 500;

$sth->execute( $t, $user, $coe_id );

    This returns the following, indicating that a mere array reference
is not adequate to bind to a PL/SQL table type:

Can't bind a reference (ARRAY(0x6065d0))

    Anyone have any ideas as to what I can do differently or if PL/SQL
table types are even supported as an bind parameter under DBD::Oracle?
I'm told that they can be used in the ProC environment, but I don't have
the luxury of being to change the implementation language I've been
assigned just to satisfy this requirement.  If this is not supported by
DBD::Oracle we'll have to re-write some PL/SQL code, which could be
done, but would be rather time consuming.

                                 Desperate for assistance,

                                       John Heinnickel

Sent via Deja.com
http://www.*-*-*.com/



Sat, 28 Jun 2003 17:30:43 GMT  
 pl/sql stored procedure with Table input
    I have an Oracle PL/SQL Stored Procedure that I need to call from a
perl environment using DBI.  I've already got several working examples
in my scripts using non-Table data types, but this one eludes me.

Here's the header file for the package in question:

CREATE OR REPLACE PACKAGE coe AS
        TYPE TableMessageID
                IS TABLE OF coe_history.message_identifier%TYPE
                INDEX BY BINARY_INTEGER;

        PROCEDURE deleteUidlList(
                        t       IN      TableMessageID,
                        user    IN      coe_history.username%TYPE,
                        coe_id  IN
coe_history.coe_configuration_id%TYPE
                );
END coe;

In my perl code I have the following currently:

$sth->prepare( q{
    BEGIN
        deleteUidlList( ?, ?, ? );
    END; } );
my( $t ) = [ 'foo', 'bar' ];
my( $user ) = 'joe';
my( $coe_id ) = 500;

$sth->execute( $t, $user, $coe_id );

    This returns the following, indicating that a mere array reference
is not adequate to bind to a PL/SQL table type:

Can't bind a reference (ARRAY(0x6065d0))

    Anyone have any ideas as to what I can do differently or if PL/SQL
table types are even supported as an bind parameter under DBD::Oracle?
I'm told that they can be used in the ProC environment, but I don't have
the luxury of being to change the implementation language I've been
assigned just to satisfy this requirement.  If this is not supported by
DBD::Oracle we'll have to re-write some PL/SQL code, which could be
done, but would be rather time consuming.

                                 Desperate for assistance,

                                       John Heinnickel

Sent via Deja.com
http://www.deja.com/



Sat, 28 Jun 2003 17:30:25 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Executing DEL and UPDT PL/SQL stored procedures from PERL

2. Executing DEL and UPDT PL/SQL stored procedures from PERL

3. Binding Cursors returned from PL/SQL stored procedure

4. pl/sql stored procedure

5. OraPerl and PL/SQL Stored Procedure

6. DBI and Oracle: PL/SQL table as an OUT param from procedure

7. how to handle a plsql table returned by a stored procedure

8. LONG: Req: Help in retrieving values from SQL Server stored procedure using Win32::ODBC

9. ODBC, SQL Server, Stored Procedures

10. Perl Library to execute stored procedures in MS-SQL Server

11. SQL 2000 Stored procedure called from PERL not responding

12. Using Embedded SQL vs Stored procedures in Perl DBI?DBD for Oracle

 

 
Powered by phpBB® Forum Software