RE: inserting XML Document into Oracle database using a stored procedure
- From: Ron.Reidy@xxxxxxxxxxxxxxxxxx (Ron Reidy)
- Date: Thu, 2 Mar 2006 13:29:42 -0700
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.
.
- Follow-Ups:
- Re: inserting XML Document into Oracle database using a stored procedure
- From: Hariharan Venkata-Chinnaswamy
- Re: inserting XML Document into Oracle database using a stored procedure
- Prev by Date: Flaw reported in DBI::ProxyServer - is it something we knew about?
- Next by Date: Re: Flaw reported in DBI::ProxyServer - is it something we knew about?
- 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
|
