Forum Stats

  • 3,815,828 Users
  • 2,259,096 Discussions
  • 7,893,264 Comments

Discussions

Pass CLOB from Perl to PL/SQL function

Igoroshka
Igoroshka Member Posts: 1,585 Gold Trophy
edited Feb 4, 2020 1:47AM in SQL & PL/SQL

Hi gurus.

Kindly ask you advice on the following problem.

I'm developing function that processes input string. Client that calls function -- Perl client. Client sends a string up to 2 MB in size. Because of this CLOB is used for input variable in PL/SQL function:

create function aaa (Str in clob) return ...

The issue we faced is that nothing is actually passed from Perl to Oracle. Tracing of the data exchange shown that empty parameter is transferred to function.

On the same time we are able to insert that CLOB value from Perl into the table without any problems.

We investigated this but unfortunately found nothing relevant on this.

I will greatly appreciate help or advice on this issue.

Thank you in advance.

Tagged:
Igoroshka
«1

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Feb 2, 2020 7:18PM

    What is your perl version?

    What is your Oracle database version?

    What is your Oracle:::DBD version?

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

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

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

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

    Igoroshka
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,803 Red Diamond
    edited Feb 3, 2020 12:52AM

    Likely caused by not explicitly treating the bind variable explicitly as a CLOB, in Perl.

    Keep in mind that a LOB variable is actually a locator (pointer) variable.

    Igoroshka
  • Igoroshka
    Igoroshka Member Posts: 1,585 Gold Trophy
    edited Feb 3, 2020 5:25AM

    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.

  • Igoroshka
    Igoroshka Member Posts: 1,585 Gold Trophy
    edited Feb 3, 2020 3:01AM

    Hi Billy~Verreynne.

    Thanks for replying.

    Billy~Verreynne wrote:Likely caused by not explicitly treating the bind variable explicitly as a CLOB, in Perl.Keep in mind that a LOB variable is actually a locator (pointer) variable.

    Perl developers used https://metacpan.org/pod/DBD::Oracle#Binding-for-Updates-and-Inserts-for-CLOBs-and-BLOBs to code Perl part. The disappointing thing for us is that we were able to pass clob value on insert but same technique used to pass value to the function failed.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Feb 3, 2020 5:57AM

    Not your current problem, but you'll hit it when you fix your current problem:

    Autonomous transactions need to end with either commit or rollback. If they don't oracle will throw an error.

    Your function has neither.

    Igoroshka
  • Igoroshka
    Igoroshka Member Posts: 1,585 Gold Trophy
    edited Feb 3, 2020 10:02AM

    You are absolutely correct Cookiemonster76.

    Original code does have commit at the end. I cut too much preparing code snippet. Sorry for confusing.

  • Igoroshka
    Igoroshka Member Posts: 1,585 Gold Trophy
    edited Feb 3, 2020 10:58AM

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

    }

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Feb 3, 2020 11:15AM

    The problem is probably in the perl side, and quite probably in the code you omitted.

    Simplify things and build up.

    Can you get a function with a single varchar2 parameter to work?

    If you can try a single clob parameter (so no other parameters)

    then add the other parameters.

  • Igoroshka
    Igoroshka Member Posts: 1,585 Gold Trophy
    edited Feb 3, 2020 11:33AM

    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.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Feb 3, 2020 11:48AM

    So post a full set of perl code that works, a full set of perl code that doesn't work and the error message.

    You're giving us lots of code snippets that don't add up to a complete picture.