[andrewkl]

hi,

I have the following code that inserts a string to an Oracle DB via a stored procedure:

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(ra_types);
...
1 $pk = "<some_unique_value>";
2 $string = "<a very long string....>";
3 $stmt = 'BEGIN my_package.my_proc(rimary_key, :value); END;';
# the my_proc stored procedure code looks like:
# PROCEDURE my_proc(primary_key IN VARCHAR2, col_val IN CLOB)
4 $sth = $dbh->prepare ($stmt);
5 $sth->bind_param("rimary_key", $pk);
6 $sth->bind_param(":value", "$string");
7 $sth->execute();
...

for strings < 4Kb, the code works fine.

For strings > 4Kb, 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.

[anacedent]

http://aspn.activestate.com/ASPN/Mai...erl-DBI/338255

HTH & YMMV

[andrewkl]

I already tried binding with:

$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))

not sure what else to try...

--Andrew

[RBARAER]

Could you post the code within the stored procedure ?

Your first attempt failed for strings longer than 4000 bytes because implicit conversions from VARCHAR2 to CLOB only works up to the maximum size of a VARCHAR2 field in SQL, that is 4000 bytes

Your second attempt failed because of a misuse of LOBs. LOBs are easy to use as long as you use implicit conversions... else, they are much harder to use and they need that you fully understand how they are to be used.

I suggest that you had a close look at the documentation specific to LOB programming : Application Developer's Guide - Large Objects

If you post the code of the PLSQL procedure, I'll be able to help you on (you have to change it and maybe you don't know exactly how).

However I don't know which language you are using to call the procedure, so I don't know if I will be able to help you completely on this... but maybe someone else will

Regards,

rbaraer

[andrewkl]

hi,

below is all details (stored proc/table definition/Perl code)
Actually, for strings < 32512 chars, the perl code (original line: $sth->bind_param(":value", "$string"); ) works fine:

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,
);

------------------

#!/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(ra_types);
...
1 $pk = "<some_unique_value>";
2 $string = "<a very long string....>";
3 $stmt = 'BEGIN my_package.my_proc(rimary_key, :value); END;';
# see near bottom for details of "my_proc"
4 $sth = $dbh->prepare ($stmt);
5 $sth->bind_param("rimary_key", $pk);
6 $sth->bind_param(":value", "$string");
7 $sth->execute();
...

[anacedent]

>6 $sth->bind_param(":value", "$string");
does below work any better?
$sth->bind_param(":value", "$string", {ora_type => ORA_CLOB});

[andrewkl]

hi,

I already tried:

$sth->bind_param(":value", "$string", { ora_type => ORA_CLOB } );

before I made the post (also mentioned this in the post)

i don't think there's anything wrong with my stored procedure. In fact, i don't think it was even executed.
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


--Andrew

[RBARAER]

$sth->bind_param(":value", "$string", {ora_type => ORA_CLOB});

So this is perl... I think you should not use a string to bind a CLOB parameter. Check the Oracle perl documentation to see which type of data has to be used

Actually, for strings < 32512 chars, the perl code (original line: $sth->bind_param(":value", "$string"); ) works fine

Right, here you are hitting the PLSQL size limit for VARCHAR2 variables, but the insert statement would fail for any size > 4000, which is the SQL size limit for VARCHAR2...

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;

Ouch ! NEVER EVER use dynamic SQL in PLSQL UNLESS you are FORCED to ! If the table name is dynamic for example, then OK, else never ! Dynamic SQL is less performant since it cannot use the PLSQL cache, and much more error prone because errors are only known at execution time .

Instead, use static SQL :

PROCEDURE my_proc
(
    strPK IN VARCHAR2,
    blobColVal IN OUT CLOB -- OUT to return the LOB Locator
)
IS

BEGIN
    
    -- Insert an empty clob returning the LOB Locator
    INSERT INTO my_table ( pk, clob_col ) 
    VALUES (strPK, empty_clob())
    RETURNING clob_col INTO blobColVal;

    -- do not commit here since you have to write within the CLOB before

END my_proc;

There you get the LOB Locator from the PLSQL procedure and you can write within it (a FOR UPDATE on the row is made implicitly when you return the LOB Locator from the INSERT statement like this). Use the perl LOB API (there must be one) to open it, write data and then close it (LOBs are managed like files : open, read/write, close). And finally, don't forget to commit

HTH & Regards,

rbaraer