Booshman Booshman - 1 month ago 7
Perl Question

Pass an XML file as a parameter to an Oracle stored procedure



I am trying to pass an XML file as a parameter as an XMLType to an Oracle stored procedure using a Perl script.

I am using Perl

DBI
and
DBD::Oracle
modules.

I did my research and found how to do this in bits and pieces, but I'm not sure how to put everything together to achieve what I want.

Do I pass the XML data contained in the file to
$xml
as a String or CLOB?

Most of the XML parsing and table updates is in the stored procedure.

This is how far I have got. I have not tried to execute the script as I know it is incomplete.

#!/usr/local/bin/perl

###############################################################################
# File Name: load_data.pl
# Description: Reads an input XML file and populates data into tables


use strict;

use Getopt::Std;
use DBI;
use DBD::Oracle qw(:ora_types);

# Properties file parser
#
use util::File;
use use util::DotConfigProperties;

# Generic Utilities
#
use util::GenericUtils qw(getEnv isPath isFile trim );

# Flush output buffers
#
$| = 1;

#############################################################################
# Local Variables

my %args = (
AutoCommit => 0,
RaiseError => 1,
PrintError => 1
);


my $aConfig = &isFile( &getEnv('aConfig') );

# Get the DB properties and open the database connection
#

my $propObj = new util::DotConfigProperties;
my $a_prop = new util::File($aConfig);
$propObj->load($a_prop);

my $dbSid = trim($propObj->getProperty('Database'));
my $user = trim($propObj->getProperty('Username'));
my $password = trim($propObj->getProperty('Password'));

my $dbh = DBI->connect("dbi:Oracle:$dbSid", $user, $password, \%args) or croak("Couldn't connect to database: $dbSid" . DBI->errstr);

#############################################################################
# Input file
open(RFILE,"@ARGV[0]") or die("Unable to open read file");

#
# Read in the data from input file
#
while (<RFILE>) {

$sth = $dbh->prepare( "BEGIN My_Procedure (p_xml in xmltype,
p_out_xml out xmltype,
p_message out varchar2,
p_result out integer); END;" );

$sth->bind_param("p_xml", **$xml**, { ora_type => ORA_XMLTYPE });
$sth->bind_param_inout( ?, \$p_out_xml, ? ); --**I am not sure what to put in place of the question marks?**
$sth->bind_param_inout( ?, \$p_message, ? );
$sth->bind_param_inout( ?, \$p_result, ? );
$sth->execute( );
}

$dbh->commit();

###############################################################################
# Close anything open and exit

$dbh->disconnect();

exit;








Update



I made the changes that @MichaelPiankov mentioned and made some modifications. This is the result

my $xml;
while (<RFILE>) {
$xml.= $_;
}

my $sth = $dbh->prepare( "BEGIN My_Procedure(:p_xml,
:p_out_xml,
:p_message,
:p_result); END;" );
$sth->bind_param(":p_xml", $xml, { ora_type => ORA_XMLTYPE });

my $p_out_xml;
my $p_message;
my $p_result;

$sth->bind_param_inout(":p_out_xml", \$p_out_xml, { ora_type => ORA_XMLTYPE } );
$sth->bind_param_inout(":p_message", \$p_message, { ora_type => ORA_VARCHAR2 } );
$sth->bind_param_inout(":p_result", \$p_result, { ora_type => ORA_NUMBER } );

$sth->execute( );

print "Out_XML: $p_out_xml\n";
print "Message: $p_message\n";
print "Result: $p_result\n";

$dbh->commit();
###############################################################################
# Close anything open and exit
$dbh->disconnect();
exit;


error



DBD::Oracle::st execute failed: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'My_Procedure'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char 6 in 'BEGIN <*>My_Procedure(:p_xml,
:p_out_xml,
:p_message,
:p_result); END;') [for Statement "BEGIN My_Procedure(:p_xml,
:p_out_xml,
:p_message,
:p_result); END;" with ParamValues: :p_xml=OCIXMLTypePtr=SCALAR(0x9085170), :p_message=undef, :p_out_xml=undef, :p_result=undef] at load_tables.pl line 45, <RFILE> line 46947.
Out_XML:
Message:
Result:
commit ineffective with AutoCommit enabled at load_tables.pl line 51, <RFILE> line 46947.


2nd Update



I ended up removing the out parameters and was successful in loading the XML file which was my goal as this was part of testing some stored procedure code. I do want to give credit to @MichaelPiankov as his code helped me load the file.

my $xml;
while (<RFILE>) {
$xml.= $_;
}

my $sth = $dbh->prepare( "BEGIN My_Procedure(:p_xml); END;" );
$sth->bind_param(":p_xml", $xml, { ora_type => ORA_XMLTYPE });

$sth-> execute();

Answer

I think first of all you should write a file to variable if it not so big. Next you should specify binds I called them as procedure params :p_xml, :p_out_xml, :p_message, :p_result, but you may use any names. And you should create a variable for output values \$p_out_xml, \$p_message\$p_result

...
my $xml; 
while (<RFILE>) {
  $xml.= $_;
}

$sth = $dbh->prepare( "BEGIN My_Procedure (:p_xml,
                                           :p_out_xml,
                                           :p_message,
                                           :p_result); END;" );
$sth->bind_param("p_xml", $xml, { ora_type => ORA_XMLTYPE });

my $p_out_xml; 
my $p_message; 
my $p_result; 

$sth->bind_param_inout("p_out_xml", \$p_out_xml, { ora_type => ORA_XMLTYPE } ); 
$sth->bind_param_inout("p_message", \$p_message );
$sth->bind_param_inout("p_result", \$p_result);

$sth->execute(  );
... 
Comments