Passing oracletable of number to a stored procedure from perl

Question

I have stored procedure like below:

x(list IN NUMLIST)

I am calling from perl like below:

$lAeging->execute(new numlist(134))

I am getting an error like:

Can't locate object method "new" via package "numlist" (perhaps you forgot to load "numlist"?)

The actual api has 4 args and second being the problemetic one.:

$lAeging = $gDBH->prepare(q{
        BEGIN
            X(?,?,?,?);
        END;
    });

$lAeging->execute('XXXXX',
                      new numlist(134),
                      $gStartDatepassed,
                      $gEndDatePassed);

Can anybody please point out the mistake here.

Probably a duplicate of https://stackoverflow.com/q/42649731/1509264 — MT0, May 16 '18 at 09:59

score 1 · Answer 1 · answered May 16 '18 at 10:09

If you only want to pass an array with a single element then, adapting your code:

$lAeging = $gDBH->prepare(q{
        BEGIN
            X(?,NUMLIST(?),?,?);
        END;
    });

$lAeging->execute(
    'XXXXX',
    134,
    $gStartDatepassed,
    $gEndDatePassed
  );

If you want to pass a variable length collection then this is possible in Java but I have never seen a documented solution for Perl.

You can, however, pass the list as a delimited string and then there are many, many ways to split it back to an collection in the database. One way is to use this pure PL/SQL function:

CREATE OR REPLACE FUNCTION split_Number_List(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN NUMLIST DETERMINISTIC
AS
  p_result       NUMLIST := NUMLIST();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      BEGIN
        p_result( p_result.COUNT ) := TO_NUMBER( SUBSTR( i_str, p_start, p_end - p_start ) );
      EXCEPTION
        WHEN OTHERS THEN
          p_result( p_result.COUNT ) := NULL;
      END;
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      BEGIN
        p_result( p_result.COUNT ) := TO_NUMBER( SUBSTR( i_str, p_start, c_len - p_start + 1 ) );
      EXCEPTION
        WHEN OTHERS THEN
          p_result( p_result.COUNT ) := NULL;
      END;
    END IF;
  END IF;
  RETURN p_result;
END;
/

Passing oracletable of number to a stored procedure from perl

1 Answers1