help with Perl code to insert a long string via stored procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: help with Perl code to insert a long string via stored procedure

  1. #1
    Join Date
    Jan 2008
    Posts
    1

    help with Perl code to insert a long string via stored procedure

    hi,

    I have the following Perl 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 = "";
    2 $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();
    ...

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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You have to use dbms_lob pkg if you want to load more than 32K data onto CLOB column.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The 32K limit = 32767 (2^15-1).

    What is interesting is that 32767 - 32512 = 255 (2^8-1).

    So it looks like java is reserving some space for the call itself.

    Other than that, looks like the standard 32K limit at play.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width