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 Go to next message
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 #292126 is a reply to message #292096] Mon, 07 January 2008 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your Oracle version (at least 3 decimals)? Both on client and server sides?
What is your DBI/DBD version?
What is your Perf version?

By the way, maybe your procedure is just an example but:
1/ you don't dynamic statement to make an insert, just use a static one
2/ you don't need a PL/SQL procedure to execute an insert just use it directly

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 #292184 is a reply to message #292183] Tue, 08 January 2008 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I executed the same thing with a client 8.1.7, server 9.2.0.6 and perl v5.8.4, and it worked too.

Regards
Michel
Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #292213 is a reply to message #292183] Tue, 08 January 2008 03:56 Go to previous messageGo to next message
andrewkl
Messages: 9
Registered: November 2007
Junior Member
Thanks for checking. What was the length of your "$string" ?
As I mentioned in my post, I only had problems with strings >= 32513 chars.

I suspect my problem has to do with Unicode/encoding/Character sets.... not sure how to change/configure this setting.

--Andrew
Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #292219 is a reply to message #292213] Tue, 08 January 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok I will retest with a longer string and come back.

Regards
Michel
Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #292233 is a reply to message #292219] Tue, 08 January 2008 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I get the same behaviour than you using or not the LOB options.

I think you have to do it with LOB locators in several passes depending on what you want to do in the end but basically:
1/ Getting a LOB locator
2/ Working on the LOB locator with the ora_lob* functions

Regards
Michel


Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #292238 is a reply to message #292233] Tue, 08 January 2008 04:41 Go to previous messageGo to next message
andrewkl
Messages: 9
Registered: November 2007
Junior Member
where can i find example code to accomplish what you are suggesting ?

Thanks
--Andrew
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 #292392 is a reply to message #292255] Tue, 08 January 2008 20:23 Go to previous messageGo to next message
andrewkl
Messages: 9
Registered: November 2007
Junior Member
Thank you for all your help. Your demo has helped.

--Andrew
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 Go to previous messageGo to next message
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
Re: help with Perl code to insert a long string (> 32512 chars) via stored procedure [message #295754 is a reply to message #295749] Wed, 23 January 2008 05:20 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you commit in your procedure (something you should not do Oracle is not SQL server or Sybase, commit must be done ONLY in the caller) then you have to return the id and select again to get the lob locator.

Regards
Michel
Previous Topic: PERL - bind_param
Next Topic: ODBC Failed - Urgent
Goto Forum:
  


Current Time: Thu Mar 28 18:03:59 CDT 2024