Pass CLOB from Perl to PL/SQL function

Hi Gaz in Oz.

Thanks for replying. Much apologize for incomplete definition.

1. What is your perl version?

Will update as soon as will get information from Perl developers.

2. What is your Oracle database version?

11.2.0.4 EE.

3. What is your Oracle:::DBD version?

Will update as soon as will get information from Perl developers.

4. What tool did you use to come to the conclusion that "empty parameter is passed to function"?

As was_captured column in v$sql_bind_capture shown 'NO' for query passed from Perl we captured traffic with tcpdump and calculated size of the parameter in test function.

In case of passing value to function (through binding in Perl) it was 0 and 0 respectively.

In case of inserting value into the table (through binding in Perl) value presented in tcpdump output and correct length of the variable in test function.

5. How are you declaring the 2MB "string" that you pass to function?

In PL/SQL function it was declared as clob. In Perl variable was declared as described in https://metacpan.org/pod/DBD::Oracle#Binding-for-Updates-and-Inserts-for-CLOBs-and-BLOBs (adapted to our case)

my $in_blob =<span style="color: #0000ff;"> <was passed from simple Web interface></span>;

$SQL='insert into [email protected] (id,clob1,clob2, blob1,blob2) values(?,?,?,?,?)';

$sth=$dbh->prepare($SQL );

$sth->bind_param(1,3);

$sth->bind_param(2,$in_clob,{ora_type=>SQLT_CHR});

$sth->bind_param(3,$in_clob,{ora_type=>SQLT_CHR});

$sth->bind_param(4,$in_blob,{ora_type=>SQLT_BIN});

$sth->bind_param(5,$in_blob,{ora_type=>SQLT_BIN});

$sth->execute();

6. Does your function have an EXCEPTION block that swallows Oracle errors?

No at a moment. But an error was in the tcpdump. If I correctly recall it was ora-01403 no data found.

Please create a simple, small code snippet that reproduces the problem and can be run by others to help analyse the issue...

PL/SQL part:

create table tmp_tmp (

  str clob

);

create or replace function fn_HDedup_Start (

  s_Id in varchar2,

  s_Time in varchar2,

  c_Str in clob

) return integer

  authid definer

is

  pragma autonomous_transaction;

begin

  insert

    into tmp_tmp

    values (c_Str);

  return 0;

end fn_HDedup_Start;

I will upload Perl code when get it from Perl developers.

1. v5.16.3,

RHEL 7.4 (Maipo), 2.6.9-89.0.0.0.1.ELxenU

2. Instant client : 11.2.0.3.0

3.

$DBD::Oracle::VERSION = '1.74';

DBI version  $VERSION = "1.637"

5. Perl code:

foreach my $parameter (@parameter_names) {

  if ($parameter_type =~ /clob/i) {

    eval {

      $sth->bind_param(':' . $parameter, $parameters{$parameter}, {ora_type=>ORA_CLOB})

    };

  }

  elsif ($parameter_type =~ /blob/i) {

    eval {

      $sth->bind_param(':' . $parameter, $parameters{$parameter}, {ora_type=>ORA_BLOB})

    };

  }

  else {

    eval {

      $sth->bind_param(':' . $parameter, $parameters{$parameter})

    };

  }

}

If works for insert but does not work for function.

Code from the reference did not work for us for both insert and pass.

Working code:

insert into test_table values(:str_clob);

Code that does not work:

SELECT remaining_id FROM TABLE(

fn_function(:id,:time_stamp,:str_clob)

)

6. Perl snippet:

if ($max_clob_length == 0) {

    $sth = $dbh->prepare($query_configuration->{'query'})

        or return $this->_process_error('DB', $DBI::errstr);

}

else {

    $dbh->{LongReadLen} = $max_clob_length;

    $sth = $dbh->prepare($query_configuration->{'query'}, {ora_pers_lob=>1})

        or return $this->_process_error('DB', $DBI::errstr);

}

foreach my $parameter (@parameter_names) {

  if ($parameters_type{$parameter} =~ /^clob$/i) {

    eval {                                                                      

        $sth->bind_param(':' . $parameter, $parameters{$parameter}, {ora_type=>ORA_CLOB})

    };

  }

  elsif ($parameters_type{$parameter} =~ /^blob$/i) {

    eval {

        $sth->bind_param(':' . $parameter, $parameters{$parameter}, {ora_type=>ORA_BLOB})

    };

  }

  else {

      eval {

          $sth->bind_param(':' . $parameter, $parameters{$parameter})

     };

  }

  return $this->_process_error($@) if [email protected];

}

unless ($sth->execute()) {

    my $errstr = $DBI::errstr;

    $dbh->rollback;

    return $this->_process_error('DB', $errstr);

}

It works as expected when we are passing varchar from Perl side (it was clob in Oracle side). But in this case the limit is 4000 symbols only that is not enough for us.

We simplified code on both sides just to call function on Perl side and print length of the variable passed on the Oracle side. Unfortunately that did not work.