Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    6

    Unanswered: help with inserting a long string via stored procedure

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2007
    Posts
    6
    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

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Nov 2007
    Posts
    6

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

    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();
    ...
    Last edited by andrewkl; 01-07-08 at 20:27.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >6 $sth->bind_param(":value", "$string");
    does below work any better?
    $sth->bind_param(":value", "$string", {ora_type => ORA_CLOB});
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2007
    Posts
    6
    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

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    $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 :
    Code:
    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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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