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

    Unanswered: retrieving CLOB from an Oracle stored function within perl

    Hi,

    I'm trying to retrieve data (CLOB, string, integer) from a stored function.
    My example code looks like:

    ###########

    $dbh = DBI->connect(....) or die "can't connect";

    $stmt = 'BEGIN :cursor := foo; END;';
    $sth = $dbh->prepare ($stmt);
    $sth->bind_param_inout(":cursor", \$refCursor, 0, { ora_type => ORA_RSET } );

    $sth->execute();
    while ( my ($clob, $str, $num)= $refCursor->fetchrow_array ) {
    print "$clob, $str, $num\n";
    }
    $sth->finish();

    --------

    When I run my perl script, I get:

    OCILobLocatorPtr=SCALAR(0x4cbdec), hello there, 1
    OCILobLocatorPtr=SCALAR(0x4cbe4c), bye now, 2


    How can I get the value of the CLOB data?


    ################################################## ##############

    The stored function foo() looks like:

    FUNCTION foo
    RETURN ref_cursor_type
    IS
    /* ---- TYPE ref_cursor_type is REF CURSOR; */
    ref_cursor ref_cursor_type;
    BEGIN
    OPEN ref_cursor
    FOR SELECT clob_col, string_col, int_col FROM foo_table ;
    RETURN ref_cursor;
    END;

    ################################################## ##############

    The foo_table looks like:

    CREATE TABLE foo_table
    (
    clob_col CLOB,
    string_col VARCHAR2 (100),
    int_col NUMBER
    );

    Contents of foo_table are:

    CLOB_COL STRING_COL INT_COL
    -----------------------------------------
    testing hello there 1
    another test bye now 2

    ################################################## ##############


    Thanks
    --Andrew

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Normally DBI handles CLOBs as big scalars. It has some attributes that let you truncate them so that really big ones don't take up all your memory, but aside from that, it doesn't return references.

    Here's the relevant manual page on DBD::Oracle. It looks confusing to me, but if you go down a bit there are some helpful looking examples.

Posting Permissions

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