Re: inserting XML Document into Oracle database using a stored procedure
- From: hariharan.venkata-chinnaswamy@xxxxxxxxxx (Hariharan Venkata-Chinnaswamy)
- Date: Thu, 02 Mar 2006 14:51:09 -0800
Here is the version information
Perl Verion is 5.8.5
Oracle client version is 9.0
DBI version is 1.45
DBD::Oracle version is 1.15
The problem is that i am not getting any errors. The database is not being called it errors out before that so there is no error message.
What i am trying to do is i want to insert a xml file into the Oracle database using a stored procedure which takes a XmlType type parameter.
How do i create a DOM object to be binded using the bind_param method?
Hari
Reidy, Ron wrote:
What is happening? What Oracle version, Perl version DBI/DBD::Oracle
versions?
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: Hariharan Venkata-Chinnaswamy
[mailto:hariharan.venkata-chinnaswamy@xxxxxxxxxx] Sent: Wednesday, March 01, 2006 9:43 PM
To: dbi-users@xxxxxxxx
Subject: inserting XML Document into Oracle database using a stored
procedure
I am trying to insert a XMLType Xmldom.DOMDocument into the Oracle
database and I am stuck. Thanks in advance for any pointers.
Here is my stored procedure. The first parameter in the checkin_design
stored procedure is a DOM object type parameter. The rest of the
parameters are regular strings.
PROCEDURE checkin_design( in_designinfo_doc IN Xmldom.DOMDocument,
in_design_tag IN DESIGN_TAG.TAG%TYPE,
in_username IN
DESIGN.created_by%TYPE,
out_message OUT VARCHAR2) IS
v_result BOOLEAN := FALSE;
v_proc VARCHAR2(500) := 'CHECKIN_DESIGN-1';
BEGIN
v_result := checkin_design (in_designinfo_doc
,in_design_tag
,in_username
,out_message);
EXCEPTION
WHEN OTHERS THEN
v_error_stack := DBMS_UTILITY.format_error_stack;
v_error_call := DBMS_UTILITY.format_call_stack;
v_ora_errno := SQLCODE;
v_ora_errmsg := SUBSTR ( SQLERRM, 0, 255 );
v_db_object := v_db_object||'.'||v_proc;
v_additional_info := '[PARAMETERS] in_designinfo_doc=[DOM
Document], in_design_tag='||in_design_tag||',
in_username='||in_username;
Error_Monitor.log_error ( v_db_schema
,v_application
,v_db_object
,565
,v_ora_errno
,v_ora_errmsg
,'Unhandled exception in
'||v_db_object||'.'||v_proc
,v_username
,SYSDATE
,1
,v_error_stack
,v_error_call
,v_additional_info
,'Y' );
out_message := v_ora_errmsg;
END checkin_design;
Here is my perl script
#! /tools/xgs/perl/5.8.5/bin/perl
use strict;
use lib "/tools/xgs/perl/5.8.5/lib/dbd/oracle/8.1.7/lib";
use DBI;
use XML::DOM;
use DBD::Oracle qw(:ora_types);
$ENV{'ORACLE_HOME'} = '/mis/TREE/oracle/5prod/8.1.7';
my $dbh = DBI->connect('dbi:Oracle:ubmdev.ir.xilinx',
'xxx',
'xxxxxxx',
{RaiseError => 1, AutoCommit => 1}) || die
"cannot connect:$DBI::errstr";
print "connected to DB\n";
my $infile = "designInfo.xml";
my $dom_parser = new XML::DOM::Parser;
my $doc = $dom_parser->parsefile($infile);
my $nodes = $doc->getElementsByTagName ("designInfo");
my $err1;
my $func1;
eval {
$func1 = $dbh->prepare(q{
BEGIN
DESIGN_PKG.checkin_design(:param1, :param2, :param3, :error);
END;
});
$func1->bind_param_inout(":error",\$err1,500);
$func1->bind_param(":param1",$doc->getDocumentElement(), { ora_type =>
ORA_CLOB });
$func1->bind_param(":param2",'head-00134');
$func1->bind_param(":param3",'hvenkata');
$func1->execute;
print "DIAGNOSIS: $DBI::lasth : $DBI::lasth->{Type} :
$DBI::lasth->{Statement}\n";
};
if ($@) {
warn "message: $DBI::errstr\n";
warn "message2: " . $func1->errstr() . "\n";
warn "message3: " . $func1->err() . "\n";
}
print "store procedure returns $err1\n";
$dbh->disconnect;
What am I doing wrong?
Hari
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
.
- References:
- Prev by Date: Re: Flaw reported in DBI::ProxyServer - is it something we knew about?
- Next by Date: RE: inserting XML Document into Oracle database using a stored procedure
- Previous by thread: RE: inserting XML Document into Oracle database using a stored procedure
- Next by thread: RE: inserting XML Document into Oracle database using a stored procedure
- Index(es):
Relevant Pages
|
