How to pass parameters to java when calling java from Oracle PLSQL

Question

I am trying to call a java function from plsql to return a csv item at a certain index in a csv string. The csv string can also contain separators within quotes (based on this Stack Overflow question).

The code

set serverout on size 100000

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "XxpayCsv" AS
import java.io.ByteArrayInputStream;
public class XxpayCsv
{
    public static String csv(String line, Integer idx)
    {
        String otherThanQuote = " [^\"] ";
        String quotedString = String.format(" \" %s* \" ", otherThanQuote);
        String regex = String.format("(?x) "+ // enable comments, ignore white spaces
                ",                         "+ // match a comma
                "(?=                       "+ // start positive look ahead
                "  (?:                     "+ //   start non-capturing group 1
                "    %s*                   "+ //     match 'otherThanQuote' zero or more times
                "    %s                    "+ //     match 'quotedString'
                "  )*                      "+ //   end group 1 and repeat it zero or more times
                "  %s*                     "+ //   match 'otherThanQuote'
                "  $                       "+ // match the end of the string
                ")                         ", // stop positive look ahead
                otherThanQuote, quotedString, otherThanQuote);

        String[] tokens = line.split(regex, -1);
        //for(String t : tokens) {
        //    System.out.println("> "+t);
        //}
        return tokens[idx];
    }
};
/

CREATE OR REPLACE
FUNCTION xxpay_csv_at(s varchar2, i number) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'XxpayCsv.csv (s, i) return java.lang.String';
/

DECLARE
   my_string clob;   -- VARCHAR2(400 CHAR);
BEGIN
   my_string := xxpay_csv_at('a,"b,c",d', 1);
   dbms_output.put_line('The value of the string is: ' || my_string);
END;
/

gives me the error

DECLARE
*
ERROR at line 1:
ORA-29531: no method csv in class XxpayCsv
ORA-06512: at "APPS.XXPAY_CSV_AT", line 1
ORA-06512: at line 4

because I am not sure how to pass in the string and integer as parameters (I'm not a Java programmer). What am I doing wrong?

Answer 1

You need to give the data types in the function specification (not the parameter names) with the full java path:

CREATE OR REPLACE FUNCTION xxpay_csv_at(s varchar2, i number) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'XxpayCsv.csv ( java.lang.String, java.lang.Integer ) return java.lang.String';
/

(Note: java.lang.Integer rather than int to match your specification in the Java code)

but what you probably want is to just implement it all in Oracle:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE OR REPLACE FUNCTION xxpay_csv_at(
  s varchar2,
  i number
) RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
  RETURN REGEXP_SUBSTR(
    s,
    '(^|,)(([^,"]*".*?")*[^,"]*)',
    1,
    i,
    NULL,
    2
  );
END;
/

Query 1:

WITH table_name ( csv ) AS (
  SELECT 'foo,bar,c;qual="baz,b""lurb",d;junk="quux,syzygy"' FROM DUAL
)
SELECT csv,
       xxpay_csv_at( csv, 1 ) AS value1,
       xxpay_csv_at( csv, 2 ) AS value2,
       xxpay_csv_at( csv, 3 ) AS value3,
       xxpay_csv_at( csv, 4 ) AS value4
FROM   table_name

Results:

|                                               CSV | VALUE1 | VALUE2 |               VALUE3 |               VALUE4 |
|---------------------------------------------------|--------|--------|----------------------|----------------------|
| foo,bar,c;qual="baz,b""lurb",d;junk="quux,syzygy" |    foo |    bar | c;qual="baz,b""lurb" | d;junk="quux,syzygy" |

Update:

Is there any way of getting the position of the matched value from regexp_substr so that I can not just return the csv item at a certain index but also the character position of the start and end in the original csv string?

Write another function using REGEXP_INSTR instead of REGEXP_SUBSTR.

Or use the function below and return CURR and DPOS for the start and end respectively.

(Note: a function used in SQL can only return a single value so you would either need to return an object type with the value, start and end attributes or have three separate functions for substring, start and end.)

Would also like to be able to add separator and quoted_by as parameters and support for separators like ', ' (where there is a space after the comma).

Yes, but not easily with regular expressions. Something like this (partly tested but not gone through all the edge cases):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE OR REPLACE FUNCTION xxpay_csv_at(
  s varchar2,
  i number,
  delim VARCHAR2 DEFAULT ','
) RETURN VARCHAR2 DETERMINISTIC
IS
  j    PLS_INTEGER := 1;
  curr PLS_INTEGER := 1;
  dpos PLS_INTEGER;
  qpos PLS_INTEGER;
BEGIN
  WHILE TRUE LOOP
    dpos := INSTR( s, delim, curr );
    qpos := INSTR( s, '"', curr ); -- Start quote
    WHILE qpos BETWEEN curr AND dpos LOOP
      qpos := INSTR( s, '"', qpos + 1 ); -- End quote
      IF qpos = 0 THEN
        RAISE_APPLICATION_ERROR( -20000, 'Invalid String - No matching end-quote' );
      END IF;
      dpos := INSTR( s, delim, qpos + 1 );
      qpos := INSTR( s, '"', qpos + 1 );
    END LOOP;

    IF dpos = 0 THEN
      IF i = j THEN
        RETURN SUBSTR( s, curr );
      ELSE
        RETURN NULL;
      END IF;
    ELSE
      IF i = j THEN
        RETURN SUBSTR( s, curr, dpos - curr );
      ELSE
        j := j + 1;
        curr := dpos + LENGTH( delim );
      END IF;
    END IF;
  END LOOP;
END;
/

Query 2:

WITH table_name ( csv ) AS (
  SELECT ', foo,bar, c;qual="baz, b""lurb", d;junk="quux, syzygy", , ' FROM DUAL
)
SELECT csv,
       xxpay_csv_at( csv, 1, ', ' ) AS value1,
       xxpay_csv_at( csv, 2, ', ' ) AS value2,
       xxpay_csv_at( csv, 3, ', ' ) AS value3,
       xxpay_csv_at( csv, 4, ', ' ) AS value4,
       xxpay_csv_at( csv, 5, ', ' ) AS value5,
       xxpay_csv_at( csv, 6, ', ' ) AS value6
FROM   table_name

Results:

|                                                         CSV | VALUE1 |  VALUE2 |                VALUE3 |                VALUE4 | VALUE5 | VALUE6 |
|-------------------------------------------------------------|--------|---------|-----------------------|-----------------------|--------|--------|
| , foo,bar, c;qual="baz, b""lurb", d;junk="quux, syzygy", ,  | (null) | foo,bar | c;qual="baz, b""lurb" | d;junk="quux, syzygy" | (null) | (null) |

Answer 2

Turns out the answer is:

CREATE OR REPLACE
FUNCTION xxpay_csv_at(s varchar2, i number) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'XxpayCsv.csv (java.lang.String, int) return java.lang.String';
/