RE: inserting XML Document into Oracle database using a stored procedure
- From: Ron.Reidy@xxxxxxxxxxxxxxxxxx (Ron Reidy)
- Date: Thu, 2 Mar 2006 16:02:29 -0700
So, I have a couple of questions.
Why are you parsing the doc? Are you trying to validate it? Have you
tried to eliminate this step and just insert the doc, even as a test?
You say it the database is not being called. So where does the error
occur? I assumed you were having a DB issue, since this is a DBI
mailing list. If your issue is with the XML::DOM::Parser, that is where
you should concentrate your efforts.
I have a couple of Perl programs that insert XML documents into a table
with a column type of SYS.XMLType and have never had issues getting them
into the DB.
I would suggest checking that the XML::DOM::Parser methods (if you
really must parse the document) are returning without errors, and
possibly the use of the Perl debugger to resolve the issue.
rr
-----Original Message-----
From: Hariharan Venkata-Chinnaswamy
[mailto:hariharan.venkata-chinnaswamy@xxxxxxxxxx]
Sent: Thursday, March 02, 2006 3:51 PM
To: Reidy, Ron
Cc: Hariharan Venkata-Chinnaswamy; dbi-users@xxxxxxxx
Subject: Re: inserting XML Document into Oracle database using a stored
procedure
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,
DESIGN_TAG.TAG%TYPE,
in_design_tag IN
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.
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.
.
- Prev by Date: Re: inserting XML Document into Oracle database using a stored procedure
- 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
|
