Home » Open Source » Programming Interfaces » help with Perl code to insert a long string (> 32512 chars) via stored procedure
| help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #292096] |
Mon, 07 January 2008 16:32  |
andrewkl
Messages: 9
Registered: November 2007
|
Junior Member |
|
|
hi,
I have the following Perl code that inserts a string to an Oracle DB via a stored procedure:
#!/usr/local/bin/perl ## Perl v5.8.6 built for sun4-solaris
use strict;
BEGIN {
$ENV{'TNS_ADMIN'} = '/auto/engweb/oracle/sqlnet';
$ENV{'ORACLE_HOME'} = '/usr/packages/dbdoracle/9.2.0';
}
use lib qw( /usr/packages/dbdoracle/9.2.0 );
use DBI;
use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_types);
...
1 $pk = "<some_unique_value>";
2 $string = "<a very long string....>";
3 $stmt = 'BEGIN my_package.my_proc(:primary_key, :value); END;';
# see near bottom for details of "my_proc"
4 $sth = $dbh->prepare ($stmt);
5 $sth->bind_param(":primary_key", $pk);
6 $sth->bind_param(":value", "$string");
7 $sth->execute();
...
for strings < 32512 chars, the code works fine.
For strings >= 32513 chars, I see the following error messages. How do I resolve these errors?
-------------------------------
DBD::Oracle::st execute failed: ORA-01460: unimplemented or unreasonable conversion requested (DBD ERROR: OCIStmtExecute)
-------------------------------
I changed line 6 to:
$sth->bind_param(":value", "$string", SQL_LONGVARCHAR );
but saw the same error.
-------------------------------
I then tried:
$sth->bind_param(":value", "$string", SQL_LONGVARBINARY);
which produced:
DBD::Oracle::st execute failed: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments
-------------------------------
Next, I tried:
$sth->bind_param(":value", "$string", { ora_type => ORA_CLOB } );
but got:
DBD::Oracle::st execute failed: ORA-01403: no data found (DBD ERROR: LOB refetch
attempted for unsupported statement type (see also ora_auto_lob attribute))
Is there anything else I can try? Any pointer is appreciated.
--Andrew
==================================================================================
my stored proc is very simple:
PROCEDURE my_proc
(
pk IN VARCHAR2,
col_val IN CLOB
)
IS
v_stmt VARCHAR2(100);
BEGIN
v_stmt := ' INSERT INTO my_table ( pk, clob_col ) ' ||
' VALUES (:1, :2)';
EXECUTE IMMEDIATE v_stmt
USING pk, col_val;
COMMIT;
END;
--------------------------------------------
the table is:
CREATE TABLE my_table
(
pk VARCHAR2 (100) NOT NULL,
clob_col CLOB
);
[Updated on: Mon, 07 January 2008 17:05] Report message to a moderator |
|
|
|
|
|
| Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #292172 is a reply to message #292126] |
Tue, 08 January 2008 02:23  |
andrewkl
Messages: 9
Registered: November 2007
|
Junior Member |
|
|
hi,
a) I'm using Oracle 9.2.0 (server & client)
b) DBI::VERSION = 1.46
c) DBD::Oracle::VERSION = 1.16
d) Perl version v5.8.6
I tried changing my stored procedure to do nothing and still got the ORA-01460: unimplemented or unreasonable conversion requested:
PROCEDURE my_proc ( pk IN VARCHAR2, col_val IN CLOB)
IS
v_stmt VARCHAR2(100);
BEGIN v_stmt := ''; END;
the ORA-01460 error occurred with this original Perl statement:
$sth->bind_param(":value", "$string");
## ORA-01460 error happens when length of "$string" >= 32513 chars
|
|
|
|
| Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #292183 is a reply to message #292172] |
Tue, 08 January 2008 02:58 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I used the following program:
my $srvname = 'MIKA';
my $ora_user = 'MICHEL';
my $ora_psw = 'michel';
my $stringconnect = "dbi:Oracle:$srvname";
my $dbh = DBI->connect ( $stringconnect,
$ora_user,
$ora_psw,
{ PrintError => 1,
RaiseError => 1,
AutoCommit => 0 }
);
$pk = "<some_unique_value>";
$string = "<a very long string....>";
$stmt = 'BEGIN my_proc(:primary_key, :value); END;';
$sth = $dbh->prepare ($stmt);
$sth->bind_param(":primary_key", $pk);
$sth->bind_param(":value", "$string");
$sth->execute();
print "End $DBI::errstr\n";
With your procedure and get:
P:\Documents\Temp\y>perl t.pl
End
So it worked.
Oracle version 10.2.0.3
Perl v5.8.8
DBI 1.58
DBD-Oracle 1.17
Regards
Michel
[Updated on: Tue, 08 January 2008 02:58] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #292255 is a reply to message #292238] |
Tue, 08 January 2008 05:17 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It depends on what you want to do.
For instance (just an example), to insert a CLOB:
drop table t purge;
create table t (id integer, val clob);
create or replace procedure my_proc (p_id number, p_val out clob)
is
begin
insert into t values (p_id, empty_clob()) returning val into p_val;
end;
/
Perl code:
my $srvname = 'MIKA';
my $ora_user = 'MICHEL';
my $ora_psw = 'michel';
my $stringconnect = "dbi:Oracle:$srvname";
my $dbh = DBI->connect ( $stringconnect,
$ora_user,
$ora_psw,
{ PrintError => 1,
RaiseError => 1,
AutoCommit => 0 }
);
$pk = 1;
$string = "<a very long string....>";
$string .= 'X' x 32513;
$stmt = 'BEGIN my_proc(:primary_key, :lob); END;';
$sth = $dbh->prepare ($stmt, {ora_auto_lob=>0});
$sth->bind_param(":primary_key", $pk);
$sth->bind_param_inout(":lob", \$lob, 50, { ora_type => ORA_CLOB });
$sth->execute();
$dbh->ora_lob_write($lob,1,$string);
$dbh->commit;
print "End $DBI::errstr\n";
Execution and query the table:
SQL> select id, length(val) from t;
ID LENGTH(VAL)
---------- -----------
1 32537
1 row selected.
Regards
Michel
|
|
|
|
|
|
| Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #295749 is a reply to message #292255] |
Wed, 23 January 2008 05:08 |
andrewkl
Messages: 9
Registered: November 2007
|
Junior Member |
|
|
hi,
i ran into:
DBD::Oracle::db ora_lob_write failed: ORA-22990: LOB locators cannot span transactions (DBD ERROR: OCILobWrite)
because my stored procedure uses dynamic SQL:
-----------------------------------------
create or replace procedure my_proc (p_id number, p_val out clob)
is
my_stmt VARCHAR2(300);
begin
my_stmt := 'insert into t (id, val) values (:pk, :valu) returning val into :lob';
EXECUTE IMMEDIATE my_stmt
USING p_id, empty_clob(), OUT p_val;
commit;
end;
----------------------
create table t (id integer, val clob);
-----------------------------
I need to use dynamic SQL because in my real code, i don't know which table to write to until execution time.
how can i resolve the above error?
Thanks
--Andrew
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 18:23:38 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
