How to pass parameters to java when calling java from Oracle PLSQL
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?
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) |
原来的答案是：
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';
/