OSP:
PROCEDURE Add_GFS_cmpdocs (
FIN_C IN VARCHAR2,
ACCT_OU_C IN VARCHAR2,
COUNTRY_ISO3_C IN VARCHAR2,
DOCNAME_N IN VARCHAR2,
DOCFILE_X IN BLOB,
UPDATED_BY_C IN VARCHAR2,
Error_Num OUT NUMBER,
Error_Msg OUT VARCHAR2)
IS
DOCID_R NUMBER(4);
BEGIN
SELECT MAX(GFSA08_DOCID_R) + 1
INTO DOCID_R
FROM MGFSA08_cmpdocs;
IF DOCID_R IS NULL THEN
DOCID_R := 1;
END IF;
INSERT INTO lpdevl.MGFSA08_cmpdocs
(GFSA08_DOCID_R,
ACCT_FIN_C,
ACCT_OU_C,
COUNTRY_ISO3_C,
GFSA08_DOCNAME_N,
GFSA08_DOCFILE_X,
GFSA08_UPDATED_BY_C,
GFSA08_UPDATE_S)
VALUES (
DOCID_R,
FIN_C,
ACCT_OU_C,
COUNTRY_ISO3_C,
DOCNAME_N,
-- '',
DOCFILE_X,
UPDATED_BY_C,
SysDate);
Error_Num := 0;
Error_Msg := 'Success';
return;
EXCEPTION
WHEN OTHERS THEN
Error_Num := SQLCODE;
Error_Msg := SUBSTR(SQLERRM, 1, 100);
END Add_GFS_cmpdocs;
Perl Code:
sub addGlobalDocs
{
my( $acct_fin_c, $acct_ou_c, $country_iso3_c, $gfsa08_docname_n,
$in, $u
serid) = @_;
my @dbHandle;
my $sql, $csr;
my $err_num = "";
my $err_msg = "";
####################Insert Blob from Server################################
my $ua = new LWP::UserAgent;
$ua->timeout(1);
$url = "http://webdev.globalfleetsolutions.ford.com/images/test.pdf";;
my $curl = url("http");
my $forms = new CGI;
my $req = new HTTP::Request 'GET', $url;
if ( $response->is_success)
if ( $response->is_success)
{
$PDF_BLOB = $response->content;
$dbHandle[1]->{LongReadLen} = length($PDF_BLOB) + 1024;
}
##################### END ##################################################
$sql = "BEGIN
gfs.gfs_sp_package_x.add_gfs_cmpdocs(:1,:2,:3,:4,:5,:6,:,
:8); END;";
@dbHandle = &openGFSDb();
return "Error" if (! $#dbHandle );
$dbHandle[1]->{LongReadLen} = length($PDF_BLOB) + 2048;
$dbHandle[1]->{LongTruncOk}=1;
$csr = $dbHandle[1]->prepare ($sql) || die $csr->errstr;
my %attrib = ();
$attrib{'ora_type'} = 24;
$csr->bind_param(1,$acct_fin_c);
$csr->bind_param(2,$acct_ou_c);
$csr->bind_param(3,$country_iso3_c);
$csr->bind_param(4,$gfsa08_docname_n);
$csr->bind_param(5, $PDF_BLOB, {ora_type => ORA_BLOB} );
$csr->bind_param(6,$userid);
$csr->bind_param_inout(7,\$err_num,100);
$csr->bind_param_inout(8,\$err_msg,1000);
$err_num = " ";
$err_msg = " ";
$cnt = $csr->execute () || "Execute failed : $DBI::errstr";
print "Count1: $cnt<br>";
$cnt = $csr->finish () || "Finish failed : $DBI::errstr";
print "Count2: $cnt<br>";
# $cnt = $dbHandle[1]->commit || "Commit failed : $DBI::errstr";
print "Count3: $cnt<br>";
if ($dbHandle[0] == 1) { $dbHandle[1]->disconnect;}
}
sub openGFSDb
{
my ($user,$password,$database,$dbh,$drv);
my (@ret_results);
$drv = 'Oracle';
#if ("apollo" eq $ENV{"SERVER_NAME"}) {
# $user = 'fleet@orcl';
# $password = 'fleet';
# $database = '';
#}
$user = 'XXX';
$password = 'XXX';
$database = 'eccdb39_mssp7.world';
# $database = '19.5.38.97';
$database = 'eccdb8_mssdevl.world';
if ( $ENV{"SERVER_NAME"} eq "webdev.globalfleetsolutions.ford.com" )
{
$database = 'eccdb8_mssdevl.world';
}
else
{
$database = 'eccdb39_mssp7.world';
}
if ($dbh = DBI->connect($database,$user,$password,$drv))
{
#$dbh->{AutoCommit} = 0;
push(@ret_results, 1);
push(@ret_results, $dbh);
}
else
{
$error = $module_name . " " . $DBI::errstr;
print "Unable to open the database, $database; user, $user
(ORACLE_HOME
$ENV{'ORACLE_HOME'} ) in module $module_name due to the following error: " .
$DB
I::errs push(@ret_results, 0);
}
return(@ret_results);
}
>From: "Sterin, Ilya" <[EMAIL PROTECTED]>
>To: 'Mathew Varghese ' <[EMAIL PROTECTED]>, "'[EMAIL PROTECTED] '"
><[EMAIL PROTECTED]>
>Subject: RE: (DBD ERROR: LOB refetch attempted for unsupported statement t
>ype)
>Date: Thu, 11 Apr 2002 10:22:23 -0600
>
>Code, code, let us see the code please...
>
>Show me your flowcharts and conceal your tables, and I shall continue to be
>mystified. Show me your tables, and I won't usually need your flowcharts;
>they'll be obvious. Fred Brooks...
>
>Ilya
>
>-----Original Message-----
>From: Mathew Varghese
>To: [EMAIL PROTECTED]
>Sent: 4/11/02 10:04 AM
>Subject: (DBD ERROR: LOB refetch attempted for unsupported statement type)
>
>(DBD ERROR: LOB refetch attempted for unsupported statement type)
>
>This is the error i am getting while i try to insert BLOB into Oracle 8
>from
>Perl5V6 through OSP.
>Even though i am getting this error, OSP is inserting a row into Oracle
>except for the BLOB field.
>
>I also tried inserting it directly (without using OSP) it works perfect.
>But for some reason i need to make the OSP work, any information
>regarding
>this is appreciated.
>
>Thanks
>Mathew
>
>_________________________________________________________________
>Join the world's largest e-mail service with MSN Hotmail.
>http://www.hotmail.com
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
